PL/SQL first connection attributes

PL/SQL connection attributes are set by each connection and persist for the duration of the connection. These attributes control the behaviors of the data store. PL/SQL first connection attributes are listed Table 1-5, "PL/SQL first connection attributes" and described in detail in this section.

---

PLSQL

This attribute determines whether PL/SQL is configured for the data store.

Specifying PLSQL=1 enables PL/SQL use in the data store. Specifying PLSQL=0 disables PL/SQL use in the data store.

On platforms where PL/SQL is supported, and in TimesTen installations where PL/SQL support was enabled at installation time, the default is PLSQL=1. In other environments the default is PLSQL=0.

PL/SQL may be enabled when the data store is initially created, or at any first connect afterwards. However, once PL/SQL support is enabled in a data store, you cannot disable it later.

Configuring PL/SQL support in a data store results in the creation of a number of "built-in" PL/SQL packages and procedures that are defined in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

Some things to be aware of when setting this attribute are:

• If an application connects to a data store that has PL/SQL enabled, and the application or odbc.ini file specifies PLSQL=0, TimesTen returns a warning.

• If an application connects to a data store that does not have PL/SQL enabled, and the application or odbc.ini file specifies PLSQL=1, what happens depends on whether this is a first connect or a subsequent one. At first connect, the data store is configured to support PL/SQL. Otherwise, TimesTen returns an error.

• If PLSQL=0, all PL/SQL first and general connection attributes are ignored.

• If PLSQL=0, any attempt to change the value of a PL/SQL general connection attributes returns an error.

• If PLSQL=1, Logging must be set to 1.

• If PLSQL=1, we recommend setting LockLevel=0 for the connection. If database level locking is enabled, some PL/SQL internal functions cannot be performed. You can use the ttLockLevel built-in procedure to selectively change to database-level locking only for those specific transactions that require it.

• If PLSQL support is enabled, the DDLCommitBehavior must be the Oracle transactional commit behavior (value 0).

Required privilege

Only the instance administrator can change the value of this attribute.

Setting

Set PLSQL as follows:

---

PLSQL_MEMORY_ADDRESS

This attribute determines the virtual address at which this shared memory segment is loaded into each process that makes use of the TimesTen "direct" drivers. This memory address must be identical in each process using TimesTen. You must specify the value as a hexadecimal address.

Use of PL/SQL requires a shared memory segment. This shared memory contains recently-executed PL/SQL code, shared package state, and metadata associated with the operation of PL/SQL. This shared memory segment is separate from the one containing the TimesTen data store.

If PL/SQL use is enabled (PLSQL=1) and you have not specified a value for PLSQL_MEMORY_ADDRESS, TimesTen uses a platform-dependent default value.

The default values for each platform are designed to:1) Maximize the amount of virtual space for your TimesTen database and for your applications.2) Minimize the fragmentation of the virtual address space.3) Avoid conflicts with other uses of virtual address apace.

The platform specific default memory addresses are:

Some things to consider when setting this attribute are:

• If applications simultaneously connect to more than one TimesTen data store in direct mode, then each data store must use a different value for value for PLSQL_MEMORY_ADDRESS.

• The value of this attribute is stored persistently by TimesTen. This allows the attribute value to be properly specified in situations when the data store is loaded automatically by TimesTen. For example, the data store is automatically loaded if RamPolicy for the data store is set to 1.

• If the PL/SQL shared memory cannot be mapped at the appropriate address, TimesTen returns an error and the connection to the data store fails.

• The memory segment size is determined by the value of PLSQL_MEMORY_SIZE.

Required privilege

Only the instance administrator can change the value of this attribute.

Setting

Set PLSQL_MEMORY_ADDRESS as follows:

---

PLSQL_MEMORY_SIZE

Use of PL/SQL requires a shared memory segment. This attribute determines the size in megabytes of the shared memory segment used by PL/SQL.

This shared memory contains recently-executed PL/SQL code, the shared package state, and metadata associated with the operation of PL/SQL. This shared memory segment is separate from the one containing the TimesTen database.

Some things to consider when setting this attribute are:

• The value of this attribute is stored persistently by TimesTen. This allows the attribute value to be properly specified in situations when the data store is loaded automatically by TimesTen. For example, the data store is automatically loaded if RamPolicy for the data store is set to 1.

• The default memory size is 32 MB. The minimum size is 2MB, if PLSQL=1. For most PL/SQL users, this should be an adequate amount of memory. For data stores that make extensive use of PL/SQL, specify a larger memory size. If the memory space is exhausted, ORA-4031 errors may occur during PL/SQL execution.

• The address of the memory segment is determined by the value of PLSQL_MEMORY_ADDRESS.

For more information on how to calculate the size for PLSQL_MEMORY_SIZE, see "Calculate shared memory size for PL/SQL runtime" inOracle TimesTen In-Memory Database Operations Guide

Required privilege

Only the instance administrator can change the value of this attribute.

Setting

Set PLSQL_MEMORY_SIZE as follows: