Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide
Release 11.2.1

Part Number E13076-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 PL/SQL Installation and Environment

Oracle TimesTen In-Memory Database seamlessly integrates PL/SQL with the database. You can conveniently manage PL/SQL in TimesTen and set connection attributes specific to your environment. In little time, you can start using PL/SQL in your TimesTen environment.

The chapter shows you how to manage PL/SQL in your TimesTen database, set connection attributes, and display system-provided packages. It also describes the ttSrcScan utility, which you can use to check for PL/SQL features unsupported in TimesTen. The chapter concludes with examples to assist you in your setup procedures.

Topics in this chapter include:

Confirming that PL/SQL is Installed and Enabled in TimesTen

This section covers the following topics:

PL/SQL installation and the ttmodinstall utility

Oracle TimesTen In-Memory Database installs PL/SQL in your instance by default. If you chose not to install PL/SQL during installation, you can use the TimesTen ttmodinstall utility to install it later. For more information, see "ttmodinstall" in Oracle TimesTen In-Memory Database Reference.

Note:

Only the instance administrator can run this utility.

Understanding the PLSQL connection attribute

PL/SQL is enabled in TimesTen through the first connection attribute PLSQL. You can set this attribute when you initially create your database or at any first connection afterward. Note that once PL/SQL is enabled (PLSQL=1), it cannot be disabled (PLSQL=0 would have no effect).

If PL/SQL is supported on your platform and enabled at installation time, TimesTen sets PLSQL=1 by default. You can also set the PLSQL connection attribute in the odbc.ini file or in your application.

For more information on the PLSQL connection attribute, see "PLSQL" in Oracle TimesTen In-Memory Database Reference.

Checking that PL/SQL is enabled in a TimesTen database

There are several ways to check the status of PL/SQL in your database:

  • Use the ttVersion utility to confirm that PL/SQL is installed in your instance. If PL/SQL is enabled, that is indicated as in the following example:

    $ ttVersion
    TimesTen Release 11.2.1.0.0 (32 bit Linux/x86) (user:4738) 2008-07-04T22:01:57Z
      Instance admin: user
      Instance home directory: /scratch/user...
      Daemon home directory: /scratch/user...
      PL/SQL enabled.
    
  • Use the ttStatus utility to determine if PL/SQL is enabled in your database. In the following example, PL/SQL is enabled in database plsql1 and is not enabled in database plsql0.

    $ ttstatus
    TimesTen status report as of Wed Jul 16 14:35:31 2008
     
    Daemon pid 00000 port 0000 instance user
    TimesTen server pid 00000 started on port 0000
    ------------------------------------------------------------------------
    Data store /scratch/user/plsql1
    There are no connections to the data store
    Replication policy  : Manual
    Cache Agent policy  : Manual
    PL/SQL enabled.
    ------------------------------------------------------------------------
    Data store /scratch/user/plsql0
    There are no connections to the data store
    Replication policy  : Manual
    Cache Agent policy  : Manual
    ------------------------------------------------------------------------
    ...
    
  • Using the ttIsql utility, call the ttConfiguration built-in procedure to determine the PLSQL connection attribute setting for your database. Refer to "ttConfiguration" in Oracle TimesTen In-Memory Database Reference for information about this procedure.

    For example:

    Command> call ttConfiguration;
    < CacheGridEnable, 0 >
    < CacheGridMsgWait, 60 >
    ...
    < PLSQL, 1 >
    ...
    

PL/SQL connection attributes

There are several TimesTen connection attributes specific to PL/SQL, as summarized in Table 7-1. 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 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 7-1 PL/SQL Connection Attributes

Attribute Summary

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.


Notes:

There are additional TimesTen connection attributes you should consider with respect to your use of PL/SQL. For more information about them, refer to the indicated sections in Oracle TimesTen In-Memory Database Reference.
  • If PL/SQL is enabled in your database, the value for DDLCommitBehavior must be 0. See "DDLCommitBehavior".

  • If LockLevel 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 MemoryLock. See "MemoryLock".

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

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

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

Sample odbc.ini:

[pldef]
Driver=path/libtten.so
Datastore=/scratch/user/pldef
DatabaseCharacterSet=US7ASCII

Connect to database pldef:

$ ttIsql pldef
 
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=pldef";
Connection successful: DSN=pldef;UID=user;DataStore=/scratch/user/pldef;Database
CharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=path/libtten.so;
TypeMode=0;
(Default setting AutoCommit=1)

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

Command> call ttConfiguration;
...
< ConnectionCharacterSet, US7ASCII >
< ConnectionName, pldef >
...
< DataBaseCharacterSet, US7ASCII >
< DataStore, /scratch/user/pldef >
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 32 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
...

Example 7-2 Enable PL/SQL at first connection

This example establishes a first connection to a database that does not yet have PL/SQL enabled, specifying PLSQL=1. Because the connection is a first connection, TimesTen enables PL/SQL in the database as a result. The sample odbc.ini file also provides settings for PLSQL_MEMORY_SIZE and PLSQL_MEMORY_ADDRESS.

Sample odbc.ini:

[plsql0]
Driver=path/libtten.so
Datastore=/scratch/user/plsql0
DatabaseCharacterSet=US7ASCII
PLSQL=0
PLSQL_MEMORY_SIZE=40
PLSQL_MEMORY_ADDRESS=20000000

Connect to the plsql0 database with PLSQL=1:

$ ttisql
 
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
Command> connect "DSN=plsql0;PLSQL=1";
Connection successful: DSN=plsql0;UID=user;DataStore=/scratch/user/plsql0;
DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=path/libtten.so;TypeMode=0;PLSQL_MEMORY_SIZE=40;PLSQL_MEMORY_ADDRESS=20000000;
(Default setting AutoCommit=1)

Call the ttConfiguration built-in procedure to verify PLSQL=1 as well as PL/SQL settings from odbc.ini:

Command> call ttConfiguration;
...
< ConnectionCharacterSet, US7ASCII >
< ConnectionName, plsql0 >
...
< DataBaseCharacterSet, US7ASCII >
< DataStore, /scratch/user/plsql0 >
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x20000000 >
< PLSQL_MEMORY_SIZE, 40 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
...

Example 7-3 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_OPTIMIZE_LEVEL, and PLSQL_CONN_MEM_LIMIT. 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_OPTIMIZE_LEVEL=3;
 
Session altered.
 
Command> ALTER SESSION SET PLSQL_CONN_MEM_LIMIT=200;
 
Session altered.
 
Command> call ttconfiguration;
...
< PLSCOPE_SETTINGS, IDENTIFIERS:ALL >
...
< PLSQL_CONN_MEM_LIMIT, 200 >
...
< PLSQL_OPTIMIZE_LEVEL, 3 >
...

Next, the example sets the PLSQL_TIMEOUT connection attribute to 20 seconds. When there is an attempt to execute a program that loops indefinitely, sometime after 20 seconds has passed the execution is terminated and TimesTen returns an error.

Command> ALTER SESSION SET PLSQL_TIMEOUT = 20;
 
Session altered.
 
Command> DECLARE v_timeout NUMBER;
       > BEGIN
       >  LOOP
       >   v_timeout := 0;
       >   EXIT WHEN v_timeout < 0;
       >  END LOOP;
       > END;
       > /
 8509: PL/SQL execution terminated; PLSQL_TIMEOUT exceeded
The command failed.

Example 7-4 View PL/SQL performance statistics

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

Command> call ttplsqlmemorystats;
< Gets, 0.000000e+00 >
< GetHits, 0.000000e+00 >
< GetHitRatio, 1.000000 >
< Pins, 0.000000e+00 >
< PinHits, 0.000000e+00 >
< PinHitRatio, 1.000000 >
< Reloads, 0.000000e+00 >
< Invalidations, 0.000000e+00 >
< CurrentConnectionMemory, 0.000000e+00 >
9 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.

The ttSrcScan 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 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. You can find the ttSrcScan executable in the quickstart/sample_util directory in your TimesTen installation.

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. See the README file in the sample_util directory for information.

Displaying supplied packages

Oracle TimesTen In-Memory Database installs supplied packages when you enable PL/SQL in your database. To display a list of the packages provided with TimesTen, use the system view ALL_PROCEDURES for objects owned by SYS, as in the following example:

Command> select distinct object_name from all_procedures where owner='SYS';
< DBMS_LOCK >
< DBMS_OUTPUT >
< DBMS_PREPROCESSOR >
< DBMS_RANDOM >
< DBMS_SQL >
< DBMS_STANDARD >
< DBMS_SYS_ERROR >
< DBMS_UTILITY >
< PLITBLM >
< STANDARD >
< SYS_STUB_FOR_PURITY_ANALYSIS >
< UTL_FILE >
< UTL_RAW >
< UTL_RECOMP >
< TT_DB_VERSION >
< UTL_IDENT >
16 rows found.

For more information on supplied packages in TimesTen, see Chapter 9, "TimesTen Supplied PL/SQL Packages".

Note:

As with other ALL_* system views, all users have SELECT privilege for the ALL_PROCEDURES system view.