PL/SQL Connection Attributes

PL/SQL Connection Attributes Reference

There are several TimesTen connection attributes specific to PL/SQL. See Table 6-1.

The table also notes any required privileges and whether each connection attribute is a first connection attribute or a general connection attribute. First connection attributes are set when the database is first loaded, and persist for all connections. Only the instance administrator can load a database with changes to first connection attribute settings. A general connection attribute setting applies to one connection only, and requires no special privilege.

Table 6-1 PL/SQL Connection Attributes

Attribute	Summary
`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. Also see PLSQL_CCFLAGS in Oracle TimesTen In-Memory Database Reference.
`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 `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. Also see PLSQL_CONN_MEM_LIMIT in Oracle TimesTen In-Memory Database Reference. Note: In `ttPLSQLMemoryStats` output, the related value `CurrentConnectionMemory` indicates how much process heap memory PL/SQL has actually acquired through `malloc()`. (Also see PL/SQL Performance Statistics.)
`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. 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. 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.
`PLSQL_OPEN_CURSORS`	First connection attribute Required privilege: Instance administrator Specifies the maximum number of PL/SQL cursors that can be open in a session at one time. Note that this attribute has the same functionality as `OPEN_CURSORS` in Oracle Database. Also see PLSQL_OPEN_CURSORS in Oracle TimesTen In-Memory Database Reference. At the database level, you can use the `ttDBConfig` built-in procedure to set this parameter. See PL/SQL Database Configuration Parameters.
`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. Also see PLSQL_OPTIMIZE_LEVEL in Oracle TimesTen In-Memory Database Reference.
`PLSQL_SESSION_CACHED_CURSORS`	General connection attribute Required privilege: None Specifies the number of session cursors to cache. This attribute has the same functionality as `SESSION_CACHED_CURSORS` in Oracle Database. You can use the `ALTER SESSION` statement to change this value within your session. Also see PLSQL_SESSION_CACHED_CURSORS in Oracle TimesTen In-Memory Database Reference. At the database level, you can use the `ttDBConfig` built-in procedure to set this parameter. See PL/SQL Database Configuration Parameters.
`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 see PLSQL_TIMEOUT in Oracle TimesTen In-Memory Database Reference. 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 (relevant for client/server), discussed in TTC_Timeout in Oracle TimesTen In-Memory Database Reference. If you use 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. For additional information about the relationship between timeout values, see Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide. 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.

Note:

There are additional TimesTen connection attributes you should consider for PL/SQL. For more information about them, refer to the indicated sections in Oracle TimesTen In-Memory Database Reference.

If the LockLevel general connection attribute is set to 1 (database-level locking), certain PL/SQL internal functions cannot be performed. Therefore, set LockLevel to 0 for your connection. You can then use the ttLockLevel built-in procedure to selectively switch to database-level locking for those transactions that require it. See LockLevel and ttLockLevel.
The PL/SQL shared memory segment is not subject to the MemoryLock first connection attribute. See MemoryLock.

Creating a Database with PL/SQL Default Connection Attributes

This section provides an example that defines a database pldef without specifying PL/SQL connection attributes. (Be aware that only an instance administrator can create a database.)

The example applies only to TimesTen Classic. In TimesTen Scaleout, you set connection attributes when you define a connectable and you are not allowed to manually edit configuration files such as odbc.ini. See Connecting to a Database in Oracle TimesTen In-Memory Database Scaleout User's Guide.

Sample odbc.ini entry:

[pldef]
Driver=/mypath/install/lib/libtten.so
DataStore=/mypath/install/info/DemoDataStore/pldef
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

Connect to database pldef:

% ttisql pldef
 
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=pldef";
Connection successful: DSN=pldef;UID=myuser;DataStore=/mypath/install/info/DemoDataStore/pldef;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
DRIVER=/mypath/install/lib/libtten.so;
(Default setting AutoCommit=1)

Call the ttConfiguration built-in procedure to display settings, which shows you the default PL/SQL settings:

Command> call ttconfiguration;
...
< PLSQL_CCFLAGS, <NULL> >
...
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x0000005000000000 >
< PLSQL_MEMORY_SIZE, 128 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
...
< PLSQL_SESSION_CACHED_CURSORS, 50 >
...
88 rows found.

Using ALTER SESSION to Change Connection Attribute Settings

This section provides an example using ALTER SESSION to change general connection attribute values. Also see ALTER SESSION in Oracle TimesTen In-Memory Database SQL Reference.

This example alters PL/SQL connection attributes, changing the settings of PLSQL_CONN_MEM_LIMIT and PLSQL_OPTIMIZE_LEVEL. It then calls the ttConfiguration built-in procedure to display the new values.

Command> ALTER SESSION SET PLSQL_CONN_MEM_LIMIT=200;
 
Session altered.
 
Command> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;
 
Session altered.
 
Command> call ttconfiguration;
...
< DataStore, /mypath/install/info/DemoDataStore/pldef >
...
< PLSQL_CONN_MEM_LIMIT, 200 >
...
< PLSQL_OPTIMIZE_LEVEL, 3 >
...
< UID, MYUSER >
61 rows found.