7 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 connects to more than one TimesTen database in direct mode, 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. This is memory used to hold recently executed PL/SQL code and metadata about PL/SQL objects, as opposed to storing runtime data such as database output.

There is also related tuning information for this setting in "PLSQL_MEMORY_SIZE" in Oracle TimesTen In-Memory Database Operations Guide.

Default: 32 megabytes

Important: Use a value of at least 8 megabytes.

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

This can be used to set inquiry directives to control conditional compilation of PL/SQL units, which allows 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. Possible uses include activating debugging or tracing features, or basing functionality on the version of the database. The following is an example:

PLSQL_CCFLAGS='DEBUG:TRUE, PRODUCTION:YES'

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

% cc -DEBUG=TRUE -DPRODUCTION=YES ...

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. There is also an example of conditional compilation (though not involving PLSQL_CCFLAGS) in "UTL_IDENT".

Default: null

PLSQL_CONN_MEM_LIMIT

General connection attribute

Required privilege: None

Specifies the maximum amount of 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 7-4, "View PL/SQL performance statistics".)

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 procedures are allowed to run, in seconds, before being terminated. A new value impacts PL/SQL program units 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.

Default: 30 seconds

Note: 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.