PL/SQL General Connection Attributes

Use these attributes to control and specify options for PL/SQL compilation, processing, and the number of session cursors to cache.

You can use the ALTER SESSION statement, described in Oracle TimesTen In-Memory Database SQL Reference, to change the PL/SQL parameters to override the values assigned to the PL/SQL general connection attributes at connection time.

The PL/SQL general connection attributes are described in detail next.

PLSQL_CCFLAGS

This attribute sets 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 are deployed to multiple database environments. Possible uses include activating debugging or tracing features, or basing functionality on the version of the database.

Use this format:

PLSQL_CCFLAGS = 'v1:c1,v2:c2,...,vn:cn'

v1 has the form of an unquoted PL/SQL identifier. It is unrestricted and can be a reserved word or a keyword. The text is insensitive to case. Each one is known as a flag or flag name. Each vi can occur multiple times in the string, each occurrence can have a different flag value, and the flag values can be of different kinds.

c1 is one of the following: a PL/SQL boolean literal, a PLS_INTEGER literal, or the literal NULL. The text is insensitive to case. Each one is known as a flag value and corresponds to a flag name.

You can use the ALTER SESSION SQL statement, described in Oracle TimesTen In-Memory Database SQL Reference, to change this attribute within a session:

ALTER SESSION SET PLSQL_CCFLAGS = 'v1:c1,v2:c2,...,vn:cn';

Required Privilege

No privilege is required to change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_CCFLAGS as follows:

Where to set the attribute How the attribute is represented Setting

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems `odbc.ini` file in TimesTen Classic or in the database definition (`.dbdef`) file in TimesTen Scaleout	`PLSQL_CCFLAGS`	'A string literal with this format: `'v1:c1,v2:c2,...,vn:cn'` Default: null
Windows ODBC Data Source Administrator	Not applicable

C or Java programs or UNIX and Linux systems odbc.ini file in TimesTen Classic or in the database definition (.dbdef) file in TimesTen Scaleout

PLSQL_CCFLAGS

'A string literal with this format:

'v1:c1,v2:c2,...,vn:cn'

Default: null

Windows ODBC Data Source Administrator

Not applicable

PLSQL_CONN_MEM_LIMIT

This attribute specifies the maximum amount of process heap memory in megabytes that PL/SQL can use for the connection in which it is set.

Some things to consider when setting this attribute are:

PL/SQL does not allocate this memory until or unless it is needed. Many PL/SQL programs require only a small amount of memory. How you write your application can determine memory requirements. For example, using large VARRAYs in PL/SQL code can require a lot of memory.
If you attempt to allocate more memory than allowed, TimesTen returns an error.
The value can be modified with the ALTER SESSION statement, described in Oracle TimesTen In-Memory Database SQL Reference. For example:
```
ALTER SESSION SET PLSQL_CONN_MEM_LIMIT = 100;
```

Required Privilege

No privilege is required to change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_CONN_MEM_LIMIT as follows:

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems `odbc.ini` file in TimesTen Classic or in the database definition (`.dbdef`) file in TimesTen Scaleout	`PLSQL_CONN_MEM_LIMIT`	An integer value in MB. Default value is `100`. A setting of `0` means no limit.
Windows ODBC Data Source Administrator	Not applicable

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

PLSQL_OPTIMIZE_LEVEL

This attribute specifies the optimization level to be used to compile PL/SQL library units.

The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units. Possible values are 0, 1, 2, or 3.

The PLSQL_OPTIMIZE_LEVEL connection attribute determines the initial value of this attribute within a session. The value can be modified by an ALTER SESSION statement, described in Oracle TimesTen In-Memory Database SQL Reference. For example:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

Required Privilege

No privilege is required to change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_OPTIMIZE_LEVEL as follows:

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems `odbc.ini` file in TimesTen Classic or in the database definition (`.dbdef`) file in TimesTen Scaleout	`PLSQL_OPTIMIZE_LEVEL`	The default value is `2`.
Windows ODBC Data Source Administrator	Not applicable

PLSQL_SESSION_CACHED_CURSORS

This attribute specifies the number of session cursors to cache. A user may adjust the setting to free up space not currently needed in the cache.

PLSQL_SESSION_CACHED_CURSORS can be modified by an ALTER SESSION SQL statement, described in Oracle TimesTen In-Memory Database SQL Reference.

ALTER SESSION SET PLSQL_SESSION_CACHED_CURSORS=25;

The value of this connection attribute also can be set at a database level by the ttDBConfig built-in procedure.

Note:

This attribute has the same functionality as SESSION_CACHED_CURSORS in Oracle Database.

Required Privilege

No privilege is required to change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_SESSION_CACHED_CURSORS as follows:

Where to set the attribute How the attribute is represented Setting

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems `odbc.ini` file in TimesTen Classic or in the database definition (`.dbdef`) file in TimesTen Scaleout	`PLSQL_SESSION_CACHED_CURSORS`	A positive integer from 1 to 65535 representing the number of cursors to cache. The default value is `50`.
Windows ODBC Data Source Administrator	N/A

C or Java programs or UNIX and Linux systems odbc.ini file in TimesTen Classic or in the database definition (.dbdef) file in TimesTen Scaleout

PLSQL_SESSION_CACHED_CURSORS

A positive integer from 1 to 65535 representing the number of cursors to cache.

The default value is 50.

Windows ODBC Data Source Administrator

N/A

PLSQL_TIMEOUT

This attribute controls how long (in seconds) PL/SQL program units, including PL/SQL procedures, anonymous blocks and functions, are allowed to run before being automatically terminated.

This value can be modified with an ALTER SESSION statement, described in Oracle TimesTen In-Memory Database SQL Reference. If this value is modified through ALTER SESSION, the new value impacts any PL/SQL program units that are currently running. For example:

ALTER SESSION SET PLSQL_TIMEOUT=10;

Note:

See Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide for information about the relationship between TTC_Timeout, SQLQueryTimeout, and PLSQL_TIMEOUT.
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.

Required Privilege

No privilege is required to change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_TIMEOUT as follows:

Where to set the attribute How the attribute is represented Setting

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems `odbc.ini` file in TimesTen Classic or in the database definition (`.dbdef`) file in TimesTen Scaleout	`PLSQL_TIMEOUT`	A positive integer representing the number of seconds for the timeout value. A value of `0` means that there is no timeout limit. The default value is `30`.
Windows ODBC Data Source Administrator	Not applicable

C or Java programs or UNIX and Linux systems odbc.ini file in TimesTen Classic or in the database definition (.dbdef) file in TimesTen Scaleout

PLSQL_TIMEOUT

A positive integer representing the number of seconds for the timeout value.

A value of 0 means that there is no timeout limit.

The default value is 30.

Windows ODBC Data Source Administrator

Not applicable

Also see TTC_TCP_KEEPALIVE_TIME_MS.