Run PL/SQL Sample Code

PL/SQL support in Oracle TimesTen is seamlessly integrated within the TimesTen database and is available from all supported TimesTen programming interfaces, ODBC, JDBC, OCI, Pro*C, and TTClasses (C++). TimesTen PL/SQL uses the same language syntax and semantics as supported in the Oracle database. In this release, a subset of the PL/SQL packages targeted for performance critical OLTP applications are available. For details of supported PL/SQL functionality, refer to the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

IMPORTANT PRE-REQUISITES

1. Set up sample database and user accounts

The following build_sampledb script should be run once to set up the sample database and user accounts

Windows:   > cd quickstart/sample_scripts/createdb
> build_sampledb.bat
Unix/Linux:   $ cd quickstart/sample_scripts/createdb
$ ./build_sampledb.sh

2. Set up environment to compile and run the sample programs

The following script must be run for each of the terminal session:

Windows:   > quickstart/ttquickstartenv.bat
Unix/Linux:   $ . quickstart/ttquickstartenv.sh OR
$ source quickstart/ttquickstartenv.csh

How to run the sample PL/SQL code

To run the PL/SQL sample code using ttIsql, do

$ttIsql "dsn=<name>;uid=<username>"
> @<plsql_filename>;

OR

ttIsql -f <plsql_filename> "dsn=<name>;uid=<username>"

Examples:

$ttIsql "dsn=sampledb_1122;uid=appuser"
> enter the password for appuser
> @basics.sql;

$ttIsql -f basics.sql "dsn=sampledb_1122;uid=appuser"

  Procedures Description
   basics.sql This PL/SQL script gives an overview of the functionality of TimesTen PL/SQL via ttIsql.

Features shown include:

  • SET SERVEROUTPUT ON
  • Creating stored procedures
  • Executing stored procedures from a block
  • Error output with SHOW ERRORs
  • Examples of INPUT and OUTPUT bind variables
  • PL/SQL querying the TimesTen SYSTEM tables
  • Shows the stored procedures in the database using the PROCEDURES command
  • Describes a PL/SQL object
  • Displays the USER_OBJECTS view

This PL/SQL script should be executed from ttIsql.
  case_procedures.sql This PL /SQL block demonstrates the usage of the CASE statement and CASE expression. The PL/SQL CASE statement includes the simple CASE statement and the searched CASE statement. The CASE expression includes the simple CASE expression, the searched CASE expression, plus two syntactic shorthands, COALESCE and NULLIF.

For comparison, each stored procedure is also implemented using traditional language elements, such as a sequence of IF statements or the SQL DECODE function.

This PL/SQL block should be executed from ttIsql and output is displayed using DBMS_OUTPUT to the console.

   cursor_loop.sql

 This PL /SQL block uses a CURSOR and a LOOP to find and conditionally insert all employees whose monthly wages (salary plus commission) are higher than $2000. A variable of type %ROWTYPE is used to FETCH a row into. EXIT is used to quit the LOOP.

This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program.
  cursor_loop_types.sql This PL /SQL block show the use of the %TYPE datatype to declare variables of the same type as database columns. These types are then used in the CURSOR and some simple math is performed on the fetched data. WHEN %NOTFOUND is used to exit the LOOP.

This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program.
  cursor_loop_types2.sql This PL/SQL block show the use of a FOR-LOOP based on a CURSOR. The CURSOR resultset is used to exit the LOOP.

This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program.
  cursor_update_logic.sql This program modifies the ACCOUNT table based on instructions stored in the ACTION table. Each row of the ACTION table contains an account number to act upon, an action to be taken (insert, update, or delete), an amount by which to update the account, and a time tag.

On an insert, if the account already exists, an update is performed instead. On an update, if the account does not exist, it is created by an insert. On a delete, if the row does not exist, no action is taken.

This program can be executed from ttIsql [or from a TimesTen API] and output goes to the ACCOUNT and ACTION tables. Check the values in the ACTION and ACCOUNT tables before and after running this program.
  inner_loop_block.sql This PL/SQL block illustrates block structure and scope rules. An outer block declares two variables named X and COUNTER, and loops four times. Inside the loop is a sub-block that also declares a variable named X. The values inserted into the TEMP table show that the two Xs are indeed different.

This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program.
  loop_insert.sql This sample PL/SQL block uses a simple FOR loop to insert 10 rows into a table. The values of a loop index, counter variable, and either of two character strings are inserted. The inserted string depends on the value of the loop index.

This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the TEMP table. Check the values in the TEMP table before and after running this program.
  select_exception.sql This sample program shows examples of PL/SQL Exception handling.

The first PL/SQL block calculates the ratio between the X and Y columns of the RESULT_TABLE table. If the ratio is greater than 0.72, the block inserts the ratio into RATIO. Otherwise, it inserts -1. If the denominator is zero, ZERO_DIVIDE is raised, and a zero is inserted into RATIO.

The second PL/SQL block uses a cursor to fetch and display the employees in a given department. If no employee is found, the PL/SQL exception NO_DATA_FOUND is raised.

This program can be executed from ttIsql [or from a TimesTen API] and output is inserted into the RATIO table. Check the values in the RATIO table before and after running this program.
  update_inventory.sql This PL/SQL block processes orders for tennis rackets. It decrements the quantity of rackets on hand only if there is at least one racket left in stock.

This program can be executed from ttIsql [or from a TimesTen API] and output goes to the INVENTORY2 and PURCHASE_RECORD tables. Check the values in the INVENTORY2 and PURCHASE_RECORD tables before and after running this program.

For more information on PL/SQL support in TimesTen, refer to the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.