6 PL/SQL Environment

The chapter shows you how to manage PL/SQL in your TimesTen database. It also describes the ttSrcScan utility, which you can use to check for PL/SQL features unsupported in TimesTen.

Topics in this chapter include:

PL/SQL connection attributes
PL/SQL database configuration parameters
The ttSrcScan utility

PL/SQ L connection attributes

There are several TimesTen connection attributes specific to PL/SQL, as summarized in Table 6-1 that follows. For additional information on these connection attributes, see "PL/SQL first connection attributes" and "PL/SQL general connection attributes" in Oracle TimesTen In-Memory Database Reference.

The table also notes any required access control 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.

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

Table 6-1 PL/SQL Connection Attributes

Attribute	Summary
`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. Also see "PLSCOPE_SETTINGS" in Oracle TimesTen In-Memory Database Reference
`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. 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 Example 6-3.)
`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. Note: Alternatively, you can use the `ttDBConfig` parameter `PLSQL_OPEN_CURSORS`. 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.
`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.

Notes:

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".

The rest of this section provides some examples for setting and altering PL/SQL connection attributes.

Note:

The following example applies 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.

Example 6-1 Create a database with PL/SQL default connection attributes

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

Sample odbc.ini entry:

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

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=US7ASCII;ConnectionCharacterSet=US7ASCII;
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;
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
...
< 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.

Example 6-2 Use ALTER SESSION to change attribute settings

This example uses ALTER SESSION statements to alter PL/SQL connection attributes, changing the settings of PLSCOPE_SETTINGS, 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 PLSCOPE_SETTINGS = "IDENTIFIERS:ALL";
 
Session altered.
 
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 >
...
< PLSCOPE_SETTINGS, IDENTIFIERS:ALL >
...
< PLSQL_CONN_MEM_LIMIT, 200 >
...
< PLSQL_OPTIMIZE_LEVEL, 3 >
...
< UID, MYUSER >
61 rows found.

Example 6-3 View PL/SQL performance statistics

The ttPLSQLMemoryStats built-in procedure returns statistics about PL/SQL library cache performance and activity. This example shows some sample output. Refer to "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference for information about this procedure.

Command> call ttplsqlmemorystats;
< Gets, 5.000000 >
< GetHits, 0.000000e+00 >
< GetHitRatio, 0.000000e+00 >
< Pins, 4.000000 >
< PinHits, 0.000000e+00 >
< PinHitRatio, 0.000000e+00 >
< Reloads, 0.000000e+00 >
< Invalidations, 0.000000e+00 >
< CurrentConnectionMemory, 0.000000e+00 >
< DeferredCleanups, 0.000000e+00 >
10 rows found.

Note:

CurrentConnectionMemory is related to the PLSQL_CONN_MEM_LIMIT connection attribute documented in "PL/SQL connection attributes", indicating the amount of heap memory that has actually been acquired by PL/SQL.

PL/SQL database configuration parameters

You can use the ttDBConfig built-in procedure to display or set the value of the database configuration parameter PLSQL_OPEN_CURSORS.

This parameter specifies the maximum number of PL/SQL cursors that can be open in a session at one time. Use it to prevent a session from opening an excessive number of cursors.

To retrieve the current value:

call ttdbconfig('PLSQL_OPEN_CURSORS');
< PLSQL_OPEN_CURSORS, 50 >
1 row found.

To set the value to 75:

call ttdbconfig('PLSQL_OPEN_CURSORS', '75');
< PLSQL_OPEN_CURSORS, 75 >
1 row found.

Refer to "ttDBConfig" in Oracle TimesTen In-Memory Database Reference for information about this built-in procedure and additional information about PLSQL_OPEN_CURSORS.

Note:

Alternatively, you can use the PLSQL_OPEN_CURSORS connection attribute. See "PL/SQL connection attributes".

The t tSrcScan utility

If you have an existing PL/SQL program and want to see whether it uses PL/SQL features that TimesTen does not support, you can use the ttSrcScan command line utility to scan your program for unsupported functions, packages, types, type codes, attributes, modes, and constants. This is a standalone utility that can be run without TimesTen or Oracle Database being installed and runs on any platform supported by TimesTen. It reads source code files as input and creates HTML and text files as output. If the utility finds unsupported items, they are logged and alternatives are suggested. Specify an input file or directory for the program to be scanned and an output directory for the ttSrcScan reports. Other options are available as well.

The ttSrcScan utility is available on the Oracle Technology Network site. See the README file for information.

6 PL/SQL Environment

PL/SQL connection attributes

PL/SQL database configuration parameters

The ttSrcScan utility

PL/SQ L connection attributes

The t tSrcScan utility