6 PL/SQL Installation and Environment

PLSQL

First connection attribute

Required privilege: Instance administrator

Enables PL/SQL in the database.

If PLSQL=1, PL/SQL is enabled.

If PLSQL=0, PL/SQL is not enabled.

You can enable PL/SQL when your database is initially created or at any first connection. Once PL/SQL is enabled, it cannot be disabled.

Default: 1 (for platforms where PL/SQL is supported)

PLSQL_MEMORY_ADDRESS

First connection attribute

Required privilege: Instance administrator

Specifies the virtual address, as a hexadecimal value, at which the PL/SQL shared memory segment is loaded into each process that uses the TimesTen direct drivers. This memory address must be identical in all connections to a given database and in all processes that connect to that database.

If a single application simultaneously makes direct connections to multiple databases, then you must set different values for each of the databases.

Default: Platform-specific value

Refer to "PLSQL_MEMORY_ADDRESS" in Oracle TimesTen In-Memory Database Reference for platform-specific information.

PLSQL_MEMORY_SIZE

First connection attribute

Required privilege: Instance administrator

Determines the size, in megabytes, of memory allocated for the PL/SQL shared memory segment, which is shared by all connections. This memory is used to hold recently executed PL/SQL code and metadata about PL/SQL objects.

Default: Platform-specific value

Refer to "PLSQL_MEMORY_SIZE" in Oracle TimesTen In-Memory Database Reference for information about calculating the PL/SQL memory size and for platform-specific values and tuning information.

PLSCOPE_SETTINGS

General connection attribute

Required privilege: None

Controls whether the PL/SQL compiler generates cross-reference information. Possible values are IDENTIFIERS:NONE or IDENTIFIERS:ALL.

You can use the ALTER SESSION statement to change this value within your session.

Default: IDENTIFIERS:NONE

PLSQL_CCFLAGS

General connection attribute

Required privilege: None

Use this to set inquiry directives to control conditional compilation of PL/SQL units, which enables you to customize the functionality of a PL/SQL program depending on conditions that are checked. This is especially useful when applications may be deployed to multiple database environments. For example, to activate debugging features:

PLSQL_CCFLAGS='DEBUG:TRUE'

PL/SQL conditional compilation flags are similar in concept to flags on a C compiler command line, such as the following:

% cc -DEBUG=TRUE ...

You can use the ALTER SESSION statement to change PLSQL_CCFLAGS within your session.

See "Conditional Compilation" in Oracle Database PL/SQL Language Reference for information about this feature.

Default: NULL

PLSQL_CONN_MEM_LIMIT

General connection attribute

Required privilege: None

Specifies the maximum amount of PL/SQL shared memory (process heap memory) that PL/SQL can allocate for the current connection. (Note that this memory is not actually allocated until needed.) This is memory used for runtime data, such as large PL/SQL collections, as opposed to cached executable code. This limit setting protects other parts of your application, such as C or Java components, when PL/SQL might otherwise take all available runtime memory.

The amount of space consumed by PL/SQL variables is roughly what you might expect comparable variables to consume in other programming languages. As an example, consider a large array of strings:

type chararr is table of varchar2(32767) 
             index by binary_integer;
big_array chararr;

If 100,000 strings of 100 bytes each are placed into such an array, approximately 12 megabytes of memory is consumed.

Memory consumed by variables in PL/SQL blocks is used while the block executes, then is released. Memory consumed by variables in PL/SQL package specifications or bodies (not within a procedure or function) is used for the lifetime of the package. Memory consumed by variables in a PL/SQL procedure or function, including one defined within a package, is used for the lifetime of the procedure or function. However, in all cases, memory freed by PL/SQL is not returned to the operating system. Instead, it is kept by PL/SQL and reused by future PL/SQL invocations. The memory is freed when the application disconnects from TimesTen.

The PLSQL_CONN_MEM_LIMIT value is a number specified in megabytes. A setting of 0 means no limit.

You can use the ALTER SESSION statement to change this value within your session.

Default: 100 megabytes

Note: In ttPLSQLMemoryStats output, the related value CurrentConnectionMemory indicates how much process heap memory PL/SQL has actually acquired through malloc(). (Also see Example 6-3.)

PLSQL_OPTIMIZE_LEVEL

General connection attribute

Required privilege: None

Specifies the optimization level used to compile PL/SQL library units. The higher the setting, the more effort the compiler makes to optimize PL/SQL library units. Possible values are 0, 1, 2, or 3.

You can use the ALTER SESSION statement to change this value within your session.

Default: 2

PLSQL_TIMEOUT

General connection attribute

Required privilege: None

Controls how long PL/SQL program units are allowed to run, in seconds, before being terminated. A new value impacts PL/SQL programs currently running. Possible values are 0 (meaning no time limit) or any positive integer.

You can use the ALTER SESSION statement to change this value within your session.

(Also be aware of TimesTen SQL query timeout settings, as discussed in "Setting a timeout duration for SQL statements" in Oracle TimesTen In-Memory Database C Developer's Guide, and the TimesTen TTC_Timeout setting, discussed in "TTC_Timeout" in Oracle TimesTen In-Memory Database Reference.)

Default: 30 seconds

Notes:

• The frequency with which PL/SQL programs check execution time against this timeout value is variable. It is possible for programs to run significantly longer than the timeout value before being terminated.

• If you are using TimesTen Client/Server, PLSQL_TIMEOUT should be significantly less than TTC_Timeout, and cannot be 0 (for no timeout) if TTC_Timeout is greater than 0. For details, see the TTC_Timeout documentation referenced above.