2 Programming Features in PL/SQL in TimesTen

One of the advantages of PL/SQL in TimesTen is the ability to integrate PL/SQL procedural constructs with the flexible and powerful TimesTen SQL language.

This chapter surveys the main PL/SQL programming features described in "Overview of PL/SQL" in Oracle Database PL/SQL Language Reference. Working from simple examples, you will learn how to use PL/SQL in TimesTen. Unless otherwise noted, the examples have the same results in TimesTen as in Oracle Database.

See the end of the chapter for TimesTen-specific considerations. See "TimesTen PL/SQL components and operations" for an overview of how applications interact with TimesTen in general and PL/SQL in particular.

The following are the main topics of this chapter:

Note:

Except where stated otherwise, the examples in this guide use the TimesTen ttIsql utility. In order to display output in the examples, the setting SET SERVEROUTPUT ON is used. For more information on ttIsql, see "ttIsql" in Oracle TimesTen In-Memory Database Reference.

PL/SQL blocks

The basic unit of a PL/SQL source program is the block, or anonymous block, which groups related declarations and statements. TimesTen supports PL/SQL blocks.

A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. Example 2-1 shows the basic structure of a PL/SQL block.

Note:

If you use TimesTen Application-Tier Database Cache (TimesTen Cache): A PL/SQL block cannot be passed through to Oracle Database. (Also see "TimesTen PL/SQL with TimesTen Cache".)

Example 2-1 PL/SQL block structure

DECLARE   -- (optional)
          -- Variables, cursors, user-defined exceptions
BEGIN     -- (mandatory)
          -- PL/SQL statements
EXCEPTION -- (optional)
          -- Actions to perform when errors occur
END       -- (mandatory)

You can define either anonymous or named blocks in your PL/SQL programs. This example creates an anonymous block that queries the employees table and returns the data in a PL/SQL variable:

Command> SET SERVEROUTPUT ON;
Command>  DECLARE
       >    v_fname VARCHAR2 (20);
       >  BEGIN
       >    SELECT first_name
       >    INTO v_fname
       >    FROM employees
       >    WHERE employee_id = 100;
       >  DBMS_OUTPUT.PUT_LINE (v_fname);
       >  END;
       >  /
Steven
 
PL/SQL procedure successfully completed.

PL/SQL variables and constants

You can define variables and constants in PL/SQL and then use them in procedural statements and in SQL anywhere an expression can be used.

For example:

Command> DECLARE
       >   v_hiredate DATE;
       >   v_deptno   NUMBER (2) NOT NULL := 10;
       >   v_location VARCHAR2 (13) := 'San Francisco';
       >   c_comm     CONSTANT NUMBER := 1400;

You can use the %TYPE attribute to declare a variable according to either a TimesTen column definition or another declared variable. For example, use %TYPE to create variables emp_lname and min_balance:

Command> DECLARE
       >   emp_lname  employees.last_name%TYPE;
       >   balance    NUMBER (7,2);
       >   min_balance  balance%TYPE:= 1000;
       > BEGIN
       >   SELECT last_name INTO emp_lname FROM employees WHERE employee_id = 100;
       >   DBMS_OUTPUT.PUT_LINE (emp_lname);
       >   DBMS_OUTPUT.PUT_LINE (min_balance);
       > END;
       > /
King
1000
 
PL/SQL procedure successfully completed.

You can assign a value to a variable in the following ways.

  • With the assignment operator (:=), as shown in Example 2-2

  • By selecting or fetching values into it, as shown in Example 2-3

  • By passing the variable as an OUT or IN OUT parameter to a subprogram (procedure or function) and then assigning the value inside the subprogram, as shown in Example 2-4

Note:

The DBMS_OUTPUT package used in these examples is supplied with TimesTen. For information on this and other supplied packages, refer to Chapter 8, "TimesTen Supplied PL/SQL Packages".

Example 2-2 Assigning values to variables with the assignment operator

Command> DECLARE -- Assign values in the declarative section 
       >   wages NUMBER;
       >   hours_worked NUMBER := 40;
       >   hourly_salary NUMBER := 22.50;
       >   bonus NUMBER := 150;
       >   country VARCHAR2(128);
       >   counter NUMBER := 0;
       >   done BOOLEAN;
       >   valid_id BOOLEAN;
       >   emp_rec1 employees%ROWTYPE;
       >   emp_rec2 employees%ROWTYPE;
       >   TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
       >   comm_tab commissions;
       > BEGIN -- Assign values in the executable section
       >   wages := (hours_worked * hourly_salary) + bonus;
       >   country := 'France';
       >   country := UPPER('Canada');
       >   done := (counter > 100);
       >   valid_id := TRUE;
       >   emp_rec1.first_name := 'Amy';
       >   emp_rec1.last_name := 'Feiner';
       >   emp_rec1 := emp_rec2;
       >   comm_tab(5) := 20000 * 0.15;
       > END;
       > /
 
PL/SQL procedure successfully completed.

Note:

This example uses records, which are composite data structures that have fields with different data types. You can use the %ROWTYPE attribute, as shown, to declare a record that represents a row in a table or a row from a query result set. Records are further discussed under "PL/SQL composite data types".

Example 2-3 Using SELECT INTO to assign values to variables

Select 10% of an employee's salary into the bonus variable:

Command>  DECLARE
       >    bonus NUMBER(8,2);
       >    emp_id NUMBER(6) := 100;
       >  BEGIN
       >    SELECT salary * 0.10 INTO bonus FROM employees
       >      WHERE employee_id = emp_id;
       >    DBMS_OUTPUT.PUT_LINE (bonus);
       >  END;
       >  /
2400
 
PL/SQL procedure successfully completed.

Example 2-4 Assigning values to variables as parameters of a subprogram

Declare the variable new_sal and then pass the variable as a parameter (sal) to procedure adjust_salary. Procedure adjust_salary computes the average salary for employees with job_id='ST_CLERK' and then updates sal. After the procedure is executed, the value of the variable is displayed to verify that the variable was correctly updated.

Command> DECLARE
       >   new_sal NUMBER(8,2);
       >   emp_id NUMBER(6) := 126;
       > PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
       >   emp_job VARCHAR2(10);
       >   avg_sal NUMBER(8,2);
       > BEGIN
       >   SELECT job_id INTO emp_job FROM employees
       >     WHERE employee_id = emp_id;
       >   SELECT AVG(salary) INTO avg_sal FROM employees
       >     WHERE job_id = emp_job;
       >   DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job
       >     || ' employees: ' || TO_CHAR(avg_sal));
       >   sal := (sal + avg_sal)/2;
       >   DBMS_OUTPUT.PUT_LINE ('New salary is ' || sal);
       > END;
       > BEGIN
       >   SELECT AVG(salary) INTO new_sal FROM employees;
       >   DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: '
       >     || TO_CHAR(new_sal));
       >   adjust_salary(emp_id, new_sal);
       >   DBMS_OUTPUT.PUT_LINE ('Salary should be same as new salary ' ||
       >     new_sal);
       > END;
       > /
The average salary for all employees: 6461.68
The average salary for ST_CLERK employees: 2785
New salary is 4623.34
Salary should be same as new salary 4623.34
 
PL/SQL procedure successfully completed.

Note:

This example illustrates the ability to nest PL/SQL blocks within blocks. The outer anonymous block contains an enclosed procedure. This PROCEDURE statement is distinct from the CREATE PROCEDURE statement documented in "PL/SQL procedures and functions", which creates a subprogram that remains stored in the user's schema.

SQL function calls from PL/SQL

Most SQL functions are supported for calls directly from PL/SQL. In the first example that follows, the function RTRIM is used as a PL/SQL function in a PL/SQL assignment statement. In the second example, it is used as a SQL function in a static SQL statement.

Example 2-5 Using the RTRIM function from PL/SQL

Use the TimesTen PL/SQL RTRIM built-in function to remove the right-most "x" and "y" characters from the string. Note that RTRIM is used in a PL/SQL assignment statement.

Command> DECLARE p_var VARCHAR2(30);
       > BEGIN
       >   p_var := RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy');
       >   DBMS_OUTPUT.PUT_LINE (p_var);
       > END;
       > /
RTRIM Example
 
PL/SQL procedure successfully completed.

Example 2-6 Using the RTRIM function from SQL

Use the TimesTen SQL function RTRIM to remove the right-most "x" and "y" characters from the string. Note that RTRIM is used in a static SQL statement.

Command>  DECLARE tt_var VARCHAR2 (30);
       >  BEGIN
       >    SELECT RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy')
       >      INTO tt_var FROM DUAL;
       >    DBMS_OUTPUT.PUT_LINE (tt_var);
       > END;
       > /
RTRIM Example
 
PL/SQL procedure successfully completed.

You can refer to information about SQL functions in TimesTen under "Expressions" in Oracle TimesTen In-Memory Database SQL Reference. See "SQL Functions in PL/SQL Expressions" in Oracle Database PL/SQL Language Reference for information about support for SQL functions in PL/SQL.

PL/SQL control structures

Control structures are among the PL/SQL extensions to SQL. TimesTen supports the same control structures as Oracle Database.

The following control structures are discussed in this section.

Conditional control

The IF-THEN-ELSE and CASE constructs are examples of conditional control. In Example 2-7, the IF-THEN-ELSE construct is used to determine the salary raise of an employee based on the current salary. The CASE construct is also used to choose the course of action to take based on the job_id of the employee.

Example 2-7 Using the IF-THEN-ELSE and CASE constructs

Command> DECLARE
       >   jobid employees.job_id%TYPE;
       >   empid employees.employee_id%TYPE := 115;
       >   sal employees.salary%TYPE;
       >   sal_raise NUMBER(3,2);
       > BEGIN
       >   SELECT job_id, salary INTO jobid, sal from employees
       >     WHERE employee_id = empid;
       >   CASE
       >     WHEN jobid = 'PU_CLERK' THEN
       >       IF sal < 3000 THEN sal_raise := .12;
       >       ELSE sal_raise := .09;
       >       END IF;
       >     WHEN jobid = 'SH_CLERK' THEN
       >       IF sal < 4000 THEN sal_raise := .11;
       >       ELSE sal_raise := .08;
       >       END IF;
       >     WHEN jobid = 'ST_CLERK' THEN
       >       IF sal < 3500 THEN sal_raise := .10;
       >       ELSE sal_raise := .07;
       >       END IF;
       >     ELSE
       >       BEGIN
       >         DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
       >       END;
       >   END CASE;
       > DBMS_OUTPUT.PUT_LINE ('Original salary ' || sal);
       > -- Update
       > UPDATE employees SET salary = salary + salary * sal_raise
       > WHERE employee_id = empid;
       > END;
       > /
Original salary 3100
 
PL/SQL procedure successfully completed.

Iterative control

An iterative control construct executes a sequence of statements repeatedly, as long as a specified condition is true. Loop constructs are used to perform iterative operations.

There are three loop types:

  • Basic loop

  • FOR loop

  • WHILE loop

The basic loop performs repetitive actions without overall conditions. The FOR loop performs iterative actions based on a count. The WHILE loops perform iterative actions based on a condition.

Example 2-8 Using a WHILE loop

Command> CREATE TABLE temp (tempid NUMBER(6),
       > tempsal NUMBER(8,2),
       > tempname VARCHAR2(25));
Command> DECLARE
       >   sal employees.salary%TYPE := 0;
       >   mgr_id employees.manager_id%TYPE;
       >   lname employees.last_name%TYPE;
       >   starting_empid employees.employee_id%TYPE := 120;
       > BEGIN
       >   SELECT manager_id INTO mgr_id
       >     FROM employees
       >     WHERE employee_id = starting_empid;
       >   WHILE sal <= 15000 LOOP -- loop until sal > 15000
       >     SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
       >       FROM employees WHERE employee_id = mgr_id;
       >   END LOOP;
       >   INSERT INTO temp VALUES (NULL, sal, lname);  -- insert NULL for tempid
       >   COMMIT;
       > EXCEPTION
       >   WHEN NO_DATA_FOUND THEN
       >     INSERT INTO temp VALUES (NULL, NULL, 'Not found');  -- insert NULLs
       >     COMMIT;
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM temp;
< <NULL>, 24000, King >
1 row found.

CONTINUE statement

The CONTINUE statement enables you to transfer control within a loop back to a new iteration.

Example 2-9 Using the CONTINUE statement

In this example, the first v_total assignment is executed for each of the 10 iterations of the loop. The second v_total assignment is executed for the first five iterations of the loop. The CONTINUE statement transfers control within a loop back to a new iteration, so for the last five iterations of the loop, the second v_total assignment is not executed. The end v_total value is 70.

Command> DECLARE
       >   v_total  SIMPLE_INTEGER := 0;
       > BEGIN
       >   FOR i IN 1..10 LOOP
       >     v_total := v_total + i;
       >     DBMS_OUTPUT.PUT_LINE ('Total is : ' || v_total);
       >     CONTINUE WHEN i > 5;
       >     v_total := v_total + i;
       >     DBMS_OUTPUT.PUT_LINE ('Out of loop  Total is: ' || v_total);
       >   END LOOP;
       > END;
       > /
Total is : 1
Out of loop  Total is: 2
Total is : 4
Out of loop  Total is: 6
Total is : 9
Out of loop  Total is: 12
Total is : 16
Out of loop  Total is: 20
Total is : 25
Out of loop  Total is: 30
Total is : 36
Total is : 43
Total is : 51
Total is : 60
Total is : 70

PL/SQL procedure successfully completed.

How to execute PL/SQL procedures and functions

TimesTen supports execution of PL/SQL from client applications using ODBC, OCI, Pro*C/C++, ODP.NET, JDBC, or TimesTen TTClasses (for C++).

As noted earlier, a block is the basic unit of a PL/SQL source program. Anonymous blocks were also discussed earlier. By contrast, procedures and functions are PL/SQL blocks that have been defined with a specified name. See "PL/SQL procedures and functions" for how to define and create them.

In TimesTen, a PL/SQL procedure or function that is standalone (created with CREATE PROCEDURE or CREATE FUNCTION) or part of a package can be executed using an anonymous block or a CALL statement. (See "CALL" in Oracle TimesTen In-Memory Database SQL Reference for details about CALL syntax.)

Consider the following function:

create or replace function mytest return number is
begin
  return 1;
end;

In TimesTen, you can execute mytest in either of the following ways.

  • In an anonymous block:

    Command> variable n number;
    Command> begin
           > :n := mytest();
           > end;
           > /
     
    PL/SQL procedure successfully completed.
     
    Command> print n;
    N                    : 1
    
  • In a CALL statement:

    Command> variable n number;
    Command> call mytest() into :n;
    Command> print n;
    N                    : 1
    

In Oracle Database, you could also execute mytest through a SQL statement, as follows. This execution mechanism is not supported in TimesTen.

  • In a SELECT statement:

    SQL> select mytest from dual;
     
        MYTEST
    ----------
             1
    

    Note:

    A user's own procedure takes precedence over a TimesTen built-in procedure with the same name, but it is best to avoid such naming conflicts.

How to pass data between an application and PL/SQL

This section covers the following topics for passing data between an application and PL/SQL:

Refer to "Bind Variables" in Oracle Database PL/SQL Language Reference for additional information.

Using bind variables from an application

You can use ":var" notation for bind variables to be passed between your application (such as a C or Java application) and PL/SQL. The term bind variable (or sometimes host variable) is used equivalently to how the term parameter has historically been used in TimesTen, and bind variables from an application would correspond to the parameters declared in a PL/SQL procedure or function specification.

Here is a simple example using ttIsql to call a PL/SQL procedure that retrieves the name and salary of the employee corresponding to a specified employee ID. In this example, ttIsql essentially acts as the calling application, and the name and salary are output from PL/SQL:

Command> VARIABLE b_name VARCHAR2 (25)
Command> VARIABLE b_sal  NUMBER

Command> BEGIN
       >  query_emp (171, :b_name, :b_sal);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_name
B_NAME               : Smith
Command> PRINT b_sal
B_SAL                : 7400

See "Examples using input and output parameters and bind variables" for the complete example.

See "PL/SQL procedures and functions" for how to create and define procedures and functions.

See "Binding parameters and executing statements" in Oracle TimesTen In-Memory Database C Developer's Guide and "Preparing SQL statements and setting input parameters" in Oracle TimesTen In-Memory Database Java Developer's Guide for additional information and examples for those languages.

Notes:

  • The TimesTen binding mechanism (early binding) differs from that of Oracle Database (late binding). TimesTen requires the data types before preparing queries. As a result, there will be an error if the data type of each bind parameter is not specified or cannot be inferred from the SQL statement. This would apply, for example, to the following statement:

    SELECT 'x' FROM DUAL WHERE :a = :b;
    

    You could address the issue as follows, for example:

    SELECT 'x' from DUAL WHERE CAST(:a as VARCHAR2(10)) = 
                               CAST(:b as VARCHAR2(10));
    
  • For duplicate parameters, the implementation in PL/SQL in TimesTen is no different than the implementation in PL/SQL in Oracle Database.

  • The term "bind parameter" as used in TimesTen developer guides (in keeping with ODBC terminology) is equivalent to the term "bind variable" as used in TimesTen PL/SQL documents (in keeping with Oracle Database PL/SQL terminology).

IN, OUT, and IN OUT parameter modes

Parameter modes define whether parameters declared in a PL/SQL subprogram (procedure or function) specification are used for input, output, or both. The three parameter modes are IN (the default), OUT, and IN OUT.

An IN parameter lets you pass a value to the subprogram being invoked. Inside the subprogram, an IN parameter acts like a constant and cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter.

An OUT parameter returns a value to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it.

An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. Typically, an IN OUT parameter is a string buffer or numeric accumulator that is read inside the subprogram and then updated. The actual parameter that corresponds to an IN OUT formal parameter must be a variable, not a constant or an expression.

Note:

TimesTen supports the binding of associative arrays (but not varrays or nested tables) as IN, OUT, or IN OUT parameters. See "Using associative arrays from applications".

See "Examples using input and output parameters and bind variables".

Use of SQL in PL/SQL programs

PL/SQL is tightly integrated with the TimesTen database through the SQL language. This section covers use of the following SQL features in PL/SQL.

Static SQL in PL/SQL for queries and DML statements

From within PL/SQL, you can execute the following as static SQL:

  • DML statements: INSERT, UPDATE, DELETE, and MERGE

  • Queries: SELECT

  • Transaction control: COMMIT and ROLLBACK

Notes:

For information on these SQL statements, refer to "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Example 2-10 shows how to execute a query. For additional examples using TimesTen SQL in PL/SQL, see Chapter 5, "Examples Using TimesTen SQL in PL/SQL".

Example 2-10 Retrieving data with SELECT...INTO

Use the SELECT... INTO statement to retrieve exactly one row of data. TimesTen returns an error for any query that returns no rows or multiple rows.

This example retrieves hire_date and salary for the employee with employee_id=100 from the employees table of the HR schema.

Command> run selectinto.sql
 
DECLARE
   v_emp_hiredate employees.hire_date%TYPE;
   v_emp_salary   employees.salary%TYPE;

BEGIN
   SELECT hire_date, salary
   INTO   v_emp_hiredate, v_emp_salary
   FROM   employees
   WHERE  employee_id = 100;
   DBMS_OUTPUT.PUT_LINE(v_emp_hiredate || ' ' || v_emp_salary);
END;
/
 
1987-06-17 24000
 
PL/SQL procedure successfully completed.

Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)

You can use native dynamic SQL, through the EXECUTE IMMEDIATE statement, to accomplish any of the following:

  • Execute a DML statement such as INSERT, UPDATE, or DELETE.

  • Execute a DDL statement such as CREATE or ALTER. For example, you can use ALTER SESSION to change a PL/SQL first connection attribute.

  • Execute a PL/SQL anonymous block.

  • Call a PL/SQL stored procedure or function.

  • Call a TimesTen built-in procedure. (See "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.)

One use case is if you do not know the full text of your SQL statement until execution time. For example, during compilation you may not know the name of the column to use in the WHERE clause of your SELECT statement. In such a situation, you can use the EXECUTE IMMEDIATE statement.

Another use case is for DDL, which cannot be executed in static SQL from within PL/SQL.

To call a TimesTen built-in procedure that returns a result set, create a record type and use EXECUTE IMMEDIATE with BULK COLLECT to fetch the results into an array.

Example 2-11 provides a set of brief examples of EXECUTE IMMEDIATE. For additional examples, see "Examples using EXECUTE IMMEDIATE".

For more information, see "EXECUTE IMMEDIATE Statement" in Oracle Database PL/SQL Language Reference.

Notes:

  • See "Differences in TimesTen: transaction behavior" for important information.

  • As a DDL statement is being parsed to drop a procedure or a package, a timeout occurs if the procedure, or a procedure in the package, is still in use. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock times out after a short time.

  • You can also use the DBMS_SQL package for dynamic SQL. See "DBMS_SQL".

Example 2-11 Using EXECUTE IMMEDIATE to execute PL/SQL

This provides a set of examples using PL/SQL in EXECUTE IMMEDIATE statements. (The examples are independent of each other.)

Create a table and execute a DML statement on it within a PL/SQL block, specifying the input parameter through a USING clause. Then select the table to see the result.

Command> create table t(i int);
Command> declare
       >    i number := 1;
       > begin
       >    execute immediate 'begin insert into t values(:j);end;' using i;
       > end;
       > /
 
PL/SQL procedure successfully completed.
 
Command> select * from t;
< 1 >
1 row found.

Create a PL/SQL procedure foo then execute it in a PL/SQL block, specifying the input parameter through a USING clause:

Command> create or replace procedure foo(message varchar2) is
       > begin
       >    dbms_output.put_line(message);
       > end;
       > /
 
Procedure created.
 
Command> begin
       >    execute immediate 'begin foo(:b);end;' using 'hello';
       > end;
       > /
hello
 
PL/SQL procedure successfully completed.

Create a PL/SQL procedure myprint then execute it through a CALL statement, specifying the input parameter through a USING clause:

Command> declare
       >    a number := 1;
       > begin
       >    execute immediate 'call myprint(:b)' using a;
       > end;
       > /
myprint procedure got number 1
 
PL/SQL procedure successfully completed.

Usage notes for dynamic SQL

Code that is executed through EXECUTE IMMEDIATE generally shares the same environment as the outer PL/SQL block, as in Oracle Database. In particular, be aware of the following. (These points apply to using DBMS_SQL as well as EXECUTE IMMEDIATE.)

  • SQL and PL/SQL executed through EXECUTE IMMEDIATE run in the same transaction as the outer block.

  • Any exception raised during execution of an EXECUTE IMMEDIATE statement is propagated to the outer block. Therefore, any errors on the error stack when the EXECUTE IMMEDIATE statement is executed are visible inside the outer block. This is useful for procedures such as DBMS_UTILITY.FORMAT_ERROR_STACK.

  • Errors on the error stack prior to execution of a PL/SQL block in an EXECUTE IMMEDIATE statement are visible inside the block, for example by using DBMS_UTILITY.FORMAT_ERROR_STACK.

  • The execution environment in which an EXECUTE IMMEDIATE statement executes is the same as for the outer block. PL/SQL and TimesTen parameters, REF CURSOR state, and package state from the EXECUTE IMMEDIATE statement are visible inside the outer block.

FORALL and BULK COLLECT operations

Bulk binding is a powerful feature used in the execution of SQL statements from PL/SQL to move large amounts of data between SQL and PL/SQL. (This is different from binding parameters from an application program to PL/SQL.) With bulk binding, you bind arrays of values in a single operation rather than using a loop to perform FETCH, INSERT, UPDATE, and DELETE operations multiple times. TimesTen supports bulk binding, which can result in significant performance improvement.

Use the FORALL statement to bulk-bind input collections before sending them to the SQL engine. Use BULK COLLECT to bring back batches of results from SQL. You can bulk-collect into any type of PL/SQL collection, such as a varray, nested table, or associative array (index-by table). For additional information on collections, refer to "Using collections in PL/SQL".

You can use the %BULK_EXCEPTIONS cursor attribute and the SAVE EXCEPTIONS clause with FORALL statements. SAVE EXCEPTIONS allows an UPDATE, INSERT, or DELETE statement to continue executing after it issues an exception (for example, a constraint error). Exceptions are collected into an array that you can examine using %BULK_EXCEPTIONS after the statement has executed. When you use SAVE EXCEPTIONS, if exceptions are encountered during the execution of the FORALL statement, then all rows in the collection are processed. When the statement finishes, an error is issued to indicate that at least one exception occurred. If you do not use SAVE EXCEPTIONS, then when an exception is issued during a FORALL statement, the statement returns the exception immediately and no other rows are processed.

Refer to "Using FORALL Statement and BULK COLLECT Clause Together" in Oracle Database PL/SQL Language Reference for more information on these features.

Example 2-12 shows basic use of bulk binding and the FORALL statement. For more information and examples on bulk binding, see "Examples using FORALL and BULK COLLECT".

Example 2-12 Using the FORALL statement

In the following example, the PL/SQL program increases the salary for employees with IDs 100, 102, 104, or 110. The FORALL statement bulk-binds the collection.

Command> CREATE OR REPLACE PROCEDURE raise_salary (p_percent NUMBER) IS
       >   TYPE numlist_type IS TABLE OF NUMBER
       >    INDEX BY BINARY_INTEGER;
       >   v_id  numlist_type;  -- collection
       > BEGIN
       >   v_id(1) := 100; v_id(2) := 102; v_id (3) := 104; v_id (4) := 110;
       >   -- bulk-bind the associative array
       >   FORALL i IN v_id.FIRST .. v_id.LAST
       >    UPDATE employees
       >     SET salary = (1 + p_percent/100) * salary
       >     WHERE employee_id = v_id (i);
       > END;
       > /
 
Procedure created.
 

Find out salaries before executing the raise_salary procedure:

Command> SELECT salary FROM employees WHERE employee_id = 100 OR employee_id = 
102 OR employee_id = 104 OR employee_id = 100;
< 24000 >
< 17000 >
< 6000 >
3 rows found.

Execute the procedure and verify results as follows.

Command> EXECUTE raise_salary (10);
 
PL/SQL procedure successfully completed.
 
Command> SELECT salary FROM employees WHERE employee_id = 100 or employee_id = 
102 OR employee_id = 104 OR employee_id = 100;
< 26400 >
< 18700 >
< 6600 >
3 rows found.

RETURNING INTO clause

You can use a RETURNING INTO clause, sometimes referred to as DML returning, with an INSERT, UPDATE, or DELETE statement to return specified columns or expressions, optionally including rowids, from rows that were affected by the action. This eliminates the need for a subsequent SELECT statement and separate round trip, in case, for example, you want to confirm what was affected or want the rowid after an insert or update.

A RETURNING INTO clause can be used with dynamic SQL (with EXECUTE IMMEDIATE) or static SQL.

Through the PL/SQL BULK COLLECT feature, the clause can return items from a single row into either a set of parameters or a record, or can return columns from multiple rows into a PL/SQL collection such as a varray, nested table, or associative array (index-by table). Parameters in the INTO part of the clause must be output only, not input/output. For information on collections, refer to "Using collections in PL/SQL". For BULK COLLECT, see "FORALL and BULK COLLECT operations" and "Examples using FORALL and BULK COLLECT".

SQL syntax and restrictions for the RETURNING INTO clause in TimesTen are documented as part of the "INSERT", "UPDATE", and "DELETE" documentation in Oracle TimesTen In-Memory Database SQL Reference.

Also see "Examples using RETURNING INTO".

Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for additional information about DML returning.

TimesTen PL/SQL with TimesTen Cache

When PL/SQL programs execute SQL statements, the SQL statements are processed by TimesTen in the same manner as when SQL is executed from applications written in other programming languages. All standard behaviors of TimesTen SQL apply. In an TimesTen Cache environment, this includes the ability to use all cache features from PL/SQL. When PL/SQL accesses tables in cache groups, the normal rules for those tables apply. For example, issuing a SELECT statement against a cache instance in a dynamic cache group may cause the instance to be automatically loaded into TimesTen from Oracle Database.

In particular, be aware of the following points about this functionality.

  • When you use static SQL in PL/SQL, any tables accessed must exist in TimesTen or the PL/SQL will not compile successfully. In the following example, ABC must exist in TimesTen.

    begin
      insert into abc values(1, 'Y');
    end;
    
  • In an TimesTen Cache environment, there is the capability to use the TimesTen passthrough facility to automatically route SQL statements from TimesTen to Oracle Database. (See "Setting a passthrough level" in Oracle TimesTen Application-Tier Database Cache User's Guide for details of the passthrough facility.)

    With passthrough=1, a statement can be passed through to Oracle Database if any accessed table does not exist in TimesTen. In PL/SQL, however, the statement would have to be executed using dynamic SQL.

    Updating the preceding example, the following TimesTen PL/SQL block could be used to access ABC in Oracle Database with passthrough=1:

    begin
      execute immediate 'insert into abc values(1, 'Y')';
    end;
    

    In this case, TimesTen PL/SQL can compile the block because the SQL statement is not examined at compile time.

  • While PL/SQL can be executed in TimesTen, in the current release the TimesTen passthrough facility cannot be used to route PL/SQL blocks from TimesTen to Oracle Database. For example, when using TimesTen Cache with passthrough=3, statements executed on a TimesTen connection are routed to Oracle Database in most circumstances. In this scenario, you may not execute PL/SQL blocks from your application program, because TimesTen would attempt to forward them to Oracle Database, which is not supported. (In the passthrough=1 example, it is just the SQL statement being routed to Oracle Database, not the block as a whole.)

Important:

PL/SQL procedures and functions can use any of the following cache operations with either definer's rights or invoker's rights:
  • Loading or refreshing a cache group with commit every n rows

  • DML on AWT cache groups

  • DML on non-propagated cache groups (user managed cache groups without PROPAGATE enabled)

  • SELECT on cache group tables that do not invoke passthrough or dynamic load

  • UNLOAD CACHE GROUP

PL/SQL procedures or functions that use any of the following cache operations must use invoker's rights (AUTHID CURRENT_USER): passthrough, dynamic loading of a cache group, loading or refreshing a cache group using WITH ID, DDL on cache groups, DML on SWT cache groups, or FLUSH CACHE GROUP.

See "Definer's rights and invoker's rights".

Large objects (LOBs)

TimesTen supports LOBs (large objects). This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).

PL/SQL language features support LOBs in TimesTen as they do in Oracle Database, unless noted otherwise.

This section provides a brief overview of LOBs and discusses their use in PL/SQL, covering the following topics.

Note:

TimesTen does not support CLOBs if the database character set is TIMESTEN8.

You can also refer to the following:

About LOBs

A LOB is a large binary object (BLOB) or character object (CLOB or NCLOB). In TimesTen, a BLOB can be up to 16 MB in size and a CLOB or NCLOB up to 4 MB. LOBs in TimesTen have essentially the same functionality as in Oracle Database, except as noted otherwise. (See "Differences between TimesTen LOBs and Oracle Database LOBs".)

LOBs may be either persistent or temporary. A persistent LOB exists in a LOB column in the database. A temporary LOB exists only within an application.

LOB locators

In PL/SQL, a LOB consists of a LOB locator and a LOB value. The locator is an opaque structure that acts as a handle to the value. When an application uses a LOB in an operation such as passing a LOB as a parameter, it is passing the locator, not the actual value.

Important:

LOB manipulations through APIs that use LOB locators result in usage of TimesTen temporary space. Any significant number of such manipulations may necessitate a size increase for the TimesTen temporary data region. See "TempSize" in Oracle TimesTen In-Memory Database Reference.

To update a LOB, your transaction must have an exclusive lock on the row containing the LOB. You can accomplish this by selecting the LOB with a SELECT ... FOR UPDATE statement. This results in a writable locator. With a simple SELECT statement, the locator is read-only. Read-only and writable locators behave as follows.

  • A read-only locator is read consistent, meaning that throughout its lifetime, it sees only the contents of the LOB as of the time it was selected. Note that this would include any uncommitted updates made to the LOB within the same transaction prior to when the LOB was selected.

  • A writable locator is updated with the latest data from the database each time a write is made through the locator. So each write is made to the most current data of the LOB, including updates that have been made through other locators.

The following example details behavior for two writable locators for the same LOB:

  1. The LOB column contains "XY".

  2. Select locator L1 for update.

  3. Select locator L2 for update.

  4. Write "Z" through L1 at offset 1.

  5. Read through locator L1. This would return "ZY".

  6. Read through locator L2. This would return "XY", because L2 remains read-consistent until it is used for a write.

  7. Write "W" through L2 at offset 2.

  8. Read through locator L2. This would return "ZW". Prior to the write in the preceding step, the locator was updated with the latest data ("ZY").

Temporary LOBs

A PL/SQL block can create a temporary LOB explicitly, for it own use. In TimesTen, the lifetime of such a LOB does not extend past the end of the transaction in which it is created (as is the case with the lifetime of any LOB locator in TimesTen).

A temporary LOB may also be created implicitly by TimesTen. For example, if a SELECT statement selects a LOB concatenated with an additional string of characters, TimesTen implicitly creates a temporary LOB to contain the concatenated data. Note that a temporary LOB is a server-side object. TimesTen has no concept of client-side LOBs.

Temporary LOBs are stored in the TimesTen temporary data region.

See "CREATETEMPORARY procedures" in Oracle TimesTen In-Memory Database PL/SQL Packages Reference for how to create temporary LOBs.

Differences between TimesTen LOBs and Oracle Database LOBs

Be aware of the following:

  • A key difference between the TimesTen LOB implementation and the Oracle Database implementation is that in TimesTen, LOB locators do not remain valid past the end of the transaction. All LOB locators are invalidated after a commit or rollback, whether explicit or implicit. This includes after any DDL statement if TimesTen DDLCommitBehavior is set to 0 (the default), for Oracle Database behavior, which is always the case for PL/SQL users.

  • TimesTen does not support BFILEs, SecureFiles, array reads and writes for LOBs, or callback functions for LOBs.

  • In TimesTen, the DBMS_LOB FRAGMENT procedures are not supported, so you can write data into the middle of a LOB only by overwriting previous data. There is no functionality to insert data into the middle of a LOB and move previous data, beginning at that point, higher in the LOB correspondingly. Similarly, in TimesTen you can delete data from the middle of a LOB only by overwriting previous data with zeros or null data. There is no functionality to remove data from the middle of a LOB and move previous data, beginning at that point, lower in the LOB correspondingly. In either case in TimesTen, the size of the LOB does not change, except in the circumstance where from the specified offset there is less space available in the LOB than there is data to write. (In Oracle Database there is functionality for either mode, either overwriting and not changing the size of the LOB, or inserting or deleting and changing the size of the LOB.)

  • TimesTen does not support binding arrays of LOBs.

  • TimesTen does not support batch processing of LOBs.

  • Relevant to BLOBs, there are differences in the usage of hexadecimal literals in TimesTen. See the description of HexadecimalLiteral in "Constants" in Oracle TimesTen In-Memory Database SQL Reference.

Using LOBs

The following shows basic use of a CLOB. Assume a table defined and populated as follows, with a BLOB column (not used here) and a CLOB column:

Command> create table t1 (a int, b blob, c clob);
Command> insert into t1(a,b,c) values(1, 0x123451234554321, 'abcde');
1 row inserted.
Command> commit;

Select a CLOB from the table and display it:

Command> declare
       >    myclob clob;
       > begin
       >    select c into myclob from t1 where a=1;
       >    dbms_output.put_line('CLOB selected from table t1 is: ' || myclob);
       > end;
       > /
CLOB selected from table t1 is: abcde
 
PL/SQL procedure successfully completed.

The following tries to display the temporary CLOB again after a commit statement has ended the transaction, showing that the LOB locator becomes invalid from that point:

Command> declare
       >    myclob clob;
       > begin
       >    select c into myclob from t1 where a=1;
       >    dbms_output.put_line('CLOB selected from table t1 is: ' || myclob);
       >    commit;
       >    dbms_output.put_line('CLOB after end of transaction is: ' || myclob);
       > end;
       > /
 1806: invalid LOB locator specified
 8507: ORA-06512: at line 8
CLOB selected from table t1 is: abcde
The command failed.

PL/SQL package support for LOBs

TimesTen supports subprograms of the DBMS_LOB package for manipulation of LOB data.

See "DBMS_LOB" in this document for a list and descriptions of these subprograms. See "DBMS_LOB" in Oracle TimesTen In-Memory Database PL/SQL Packages Reference for further information.

Passthrough LOBs

Passthrough LOBs, which are LOBs in Oracle Database accessed through TimesTen, are exposed as TimesTen LOBs and are supported by TimesTen in much the same way that any TimesTen LOB is supported, but note the following:

  • TimesTen LOB size limitations do not apply to storage of passthrough LOBs, but do apply to binding. Also, if a passthrough LOB is copied to a TimesTen LOB, such as through DBMS_LOB.COPY, the size limit applies to the copy.

    An attempt to copy a passthrough LOB to a TimesTen LOB when the passthrough LOB is larger than the TimesTen LOB size limit results in an error.

  • As with TimesTen local LOBs, a locator for a passthrough LOB does not remain valid past the end of the transaction.

See "DBMS_LOB" in this document for information about DBMS_LOB support for passthrough LOBs.

Use of cursors in PL/SQL programs

A cursor, either explicit or implicit, is used to handle the result set of a SELECT statement. As a programmer, you can declare an explicit cursor to manage queries that return multiple rows of data. PL/SQL declares and opens an implicit cursor for any SELECT statement that is not associated with an explicit cursor.

Important:

Be aware that in TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes any COMMIT or ROLLBACK statement. This also includes any DDL statement executed when PL/SQL is enabled, because the DDLCommitBehavior connection must be set to 0 (Oracle Database behavior) if PL/SQL is enabled. This results in autocommits of DDL statements. See "Differences in TimesTen: transaction behavior" for additional information.

Example 2-13 shows basic use of a cursor. See "Examples using cursors" for additional information and examples. Also see "PL/SQL REF CURSORs".

Example 2-13 Using a cursor to retrieve information about an employee

Declare a cursor c1 to retrieve the last name, salary, hire date, and job class for the employee whose employee ID is 120.

Command> DECLARE
       >   CURSOR c1 IS
       >     SELECT last_name, salary, hire_date, job_id FROM employees
       >     WHERE employee_id = 120;
       > --declare record variable that represents a row
       > --fetched from the employees table
       >   employee_rec c1%ROWTYPE;
       > BEGIN
       > -- open the explicit cursor
       > -- and use it to fetch data into employee_rec
       >   OPEN c1;
       >   FETCH c1 INTO employee_rec;
       >   DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
       >   CLOSE c1;
       > END;
       > /
Employee name: Weiss
 
PL/SQL procedure successfully completed.

PL/SQL procedures and functions

Procedures and functions are PL/SQL blocks that have been defined with a specified name.

Creating and using procedures and functions

Standalone subprograms (stored procedures or functions) are created at the database level with the CREATE PROCEDURE or CREATE FUNCTION statement.

Optionally use CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION if you want the subprogram to be replaced if it already exists.

Use ALTER PROCEDURE or ALTER FUNCTION to explicitly compile a procedure or function or modify the compilation options. (To recompile a procedure or function that is part of a package, recompile the package using the ALTER PACKAGE statement.)

In TimesTen, syntax for CREATE PROCEDURE and CREATE FUNCTION is a subset of what is supported in Oracle Database. For information on these statements and the ALTER PROCEDURE and ALTER FUNCTION statements in TimesTen, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Also see "How to execute PL/SQL procedures and functions".

Notes:

  • If you use replication: PL/SQL DDL statements, such as CREATE statements for PL/SQL functions, procedures, and packages, are not replicated. See "Creating a new PL/SQL object in an existing active standby pair" and "Adding a PL/SQL object to an existing replication scheme" in Oracle TimesTen In-Memory Database Replication Guide for steps to address this.

  • If you use TimesTen Cache: A PL/SQL procedure or function resident in Oracle Database cannot be called in TimesTen by passthrough. Procedures and functions must be defined in TimesTen to be executable in TimesTen. (Also see "TimesTen PL/SQL with TimesTen Cache".)

  • PL/SQL and database object names: TimesTen does not support non-ASCII or quoted non-uppercase names of PL/SQL objects (procedures, functions, and packages). Also, trailing spaces in the quoted names of PL/SQL objects are not supported. In addition, trailing spaces in the quoted names of objects such as tables and views that are passed to PL/SQL are silently removed.

  • Definer's rights or invoker's rights determines access to SQL objects used by a PL/SQL procedure or function. For information, refer to "Definer's rights and invoker's rights".

  • See "Showing errors in ttIsql" for how to get information when you encounter errors in compiling a procedure or function.

Example 2-14 Create and execute a procedure with OUT parameters

This example creates a procedure that uses OUT parameters, executes the procedure in an anonymous block, then displays the OUT values. The procedure takes an employee ID as input then outputs the salary and job ID for the employee.

Command>  CREATE OR REPLACE PROCEDURE get_employee
       >    (p_empid in employees.employee_id%TYPE,
       >     p_sal OUT employees.salary%TYPE,
       >     p_job OUT employees.job_id%TYPE) IS
       > BEGIN
       >   SELECT salary,job_id
       >   INTO p_sal, p_job
       >   FROM employees
       >   WHERE employee_id = p_empid;
       > END;
       > /
 
Procedure created.
 
Command> VARIABLE v_salary NUMBER;
Command> VARIABLE v_job VARCHAR2(15);
Command> BEGIN
       >   GET_EMPLOYEE (120, :v_salary, :v_job);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT
V_SALARY             : 8000
V_JOB                : ST_MAN

Command> SELECT salary, job_id FROM employees WHERE employee_id = 120;
< 8000, ST_MAN >
1 row found.

Note:

Instead of using the anonymous block shown in the preceding example, you could use a CALL statement:
Command> CALL GET_EMPLOYEE(120, :v_salary, :v_job);

Example 2-15 Create and call a function

This example creates a function that returns the salary of the employee whose employee ID is specified as input, then calls the function and displays the result that was returned.

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >    v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /
 
Function created.
 
Command> variable n number;
Command> call get_sal(100) into :n;
Command> print n;
N                    : 24000

Note:

Instead of using the CALL statement shown in the preceding example, you could use an anonymous block:
Command> begin
       >   :n := get_sal(100);
       > end;
       > /

Using synonyms for procedures and functions

TimesTen supports private and public synonyms (aliases) for database objects, including PL/SQL procedures, functions, and packages. Synonyms are often used to mask object names and object owners or to simplify SQL statements.

Create a private synonym for procedure foo in your schema as follows:

CREATE SYNONYM synfoo FOR foo;

Create a public synonym as follows:

CREATE PUBLIC SYNONYM pubfoo FOR foo;

A private synonym exists in the schema of a specific user and shares the same namespace as database objects such as tables, views, and sequences. A private synonym cannot have the same name as a table or other object in the same schema.

A public synonym does not belong to any particular schema, is accessible to all users, and can have the same name as any private object.

To use a synonym you must have appropriate privileges to access the underlying object. For required privileges to create or drop a synonym, see "Required privileges for PL/SQL statements and operations".

For general information about synonyms, see "Understanding synonyms" in Oracle TimesTen In-Memory Database Operations Guide. For information about the CREATE SYNONYM and DROP SYNONYM statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Example 2-16 Use a synonym for a procedure

In the following example, USER1 creates a procedure in his schema and creates a public synonym for it. Then USER2 executes the procedure through the public synonym. Assume the following:

  • USER1 has been granted CREATE SESSION, CREATE PROCEDURE, and CREATE PUBLIC SYNONYM privileges.

  • USER2 has been granted CREATE SESSION and EXECUTE ANY PROCEDURE privileges.

  • Both users have connected to the database.

  • USER2 employs the SET SERVEROUTPUT ON setting.

USER1:

Command> create or replace procedure test is
       > begin
       > dbms_output.put_line('Running the test');
       > end;
       > /
 
Procedure created.
 
Command> create public synonym pubtest for test;
 
Synonym created.

USER2:

Command> begin
       > pubtest;
       > end;
       > /
Running the test
 
PL/SQL procedure successfully completed.

PL/SQL packages

This section discusses how to create and use PL/SQL packages.

For information about PL/SQL packages provided with TimesTen, refer to Chapter 8, "TimesTen Supplied PL/SQL Packages."

Package concepts

A package is a database object that groups logically related PL/SQL types, variables, and subprograms. You specify the package and then define its body in separate steps.

The package specification is the interface to the package, declaring the public types, variables, constants, exceptions, cursors, and subprograms that are visible outside the immediate scope of the package. The body defines the objects declared in the specification, as well as queries for the cursors, code for the subprograms, and private objects that are not visible to applications outside the package.

TimesTen stores the package specification separately from the package body in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body.

Note:

The syntax for creating packages and package bodies is the same as in Oracle Database; however, while Oracle Database documentation mentions that you must run a script named DBMSSTDX.SQL, this does not apply to TimesTen.

Creating and using packages

To create packages and store them permanently in the database, use the CREATE PACKAGE and CREATE PACKAGE BODY statements.

To create a new package, do the following:

  1. Create the package specification with the CREATE PACKAGE statement.

    You can declare program objects in the package specification. Such objects are referred to as public objects and can be referenced outside the package, and by other objects in the package.

    Optionally use CREATE OR REPLACE PACKAGE if you want the package specification to be replaced if it already exists.

  2. Create the package body with the CREATE PACKAGE BODY (or CREATE OR REPLACE PACKAGE BODY) statement.

    You can declare and define program objects in the package body.

    • You must define public objects declared in the package specification.

    • You can declare and define additional package objects, referred to as private objects. Private objects are declared in the package body rather than in the package specification, so they can be referenced only by other objects in the package. They cannot be referenced outside the package.

Use ALTER PACKAGE to explicitly compile the member procedures and functions of a package or modify the compilation options.

For more information on the CREATE PACKAGE, CREATE PACKAGE BODY, and ALTER PACKAGE statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Notes:

Example 2-17 Create and use a package

Consider the case where you want to add a row to the employees tables when you hire a new employee and delete a row from the employees table when an employee leaves your company. The following example creates two procedures to accomplish these tasks and bundles the procedures in a package. The package also contains a function to return the count of employees with a salary greater than that of a specific employee. The example then executes the function and procedures and verifies the results.

Command> CREATE OR REPLACE PACKAGE emp_actions AS
       >   PROCEDURE hire_employee (employee_id NUMBER,
       >     last_name VARCHAR2,
       >     first_name VARCHAR2,
       >     email VARCHAR2,
       >     phone_number VARCHAR2,
       >     hire_date DATE,
       >     job_id VARCHAR2,
       >     salary NUMBER,
       >     commission_pct NUMBER,
       >     manager_id NUMBER,
       >     department_id NUMBER);
       >   PROCEDURE remove_employee (emp_id NUMBER);
       >   FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;
       > END emp_actions;
       > /
 
Package created.
 
Command> -- Package body:
       > CREATE OR REPLACE PACKAGE BODY emp_actions AS
       > -- Code for procedure hire_employee:
       >   PROCEDURE hire_employee (employee_id NUMBER,
       >     last_name VARCHAR2,
       >     first_name VARCHAR2,
       >     email VARCHAR2,
       >     phone_number VARCHAR2,
       >     hire_date DATE,
       >     job_id VARCHAR2,
       >     salary NUMBER,
       >     commission_pct NUMBER,
       >     manager_id NUMBER,
       >     department_id NUMBER) IS
       >   BEGIN
       >     INSERT INTO employees VALUES (employee_id,
       >       last_name,
       >       first_name,
       >       email,
       >       phone_number,
       >       hire_date,
       >       job_id,
       >       salary,
       >       commission_pct,
       >       manager_id,
       >       department_id);
       >   END hire_employee;
       > -- Code for procedure remove_employee:
       >   PROCEDURE remove_employee (emp_id NUMBER) IS
       >   BEGIN
       >     DELETE FROM employees WHERE employee_id = emp_id;
       >   END remove_employee;
       > -- Code for function num_above_salary:
       >   FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
       >     emp_sal NUMBER(8,2);
       >     num_count NUMBER;
       >   BEGIN
       >     SELECT salary INTO emp_sal FROM employees
       >     WHERE employee_id = emp_id;
       >     SELECT COUNT(*) INTO num_count FROM employees
       >     WHERE salary > emp_sal;
       >     RETURN num_count;
       >   END num_above_salary;
       > END emp_actions;
       > /
 
Package body created.
 
Command> BEGIN
       > /* call function to return count of employees with salary
       >    greater than salary of employee with employee_id = 120
       > */
       >   DBMS_OUTPUT.PUT_LINE
       >     ('Number of employees with higher salary: ' ||
       >       TO_CHAR(emp_actions.num_above_salary(120)));
       > END;
       > /
Number of employees with higher salary: 33
 
PL/SQL procedure successfully completed.

Verify the count of 33.

Command> SELECT salary FROM employees WHERE employee_id = 120;
< 8000 >
1 row found.

Command> SELECT COUNT (*) FROM employees WHERE salary > 8000;
< 33 >
1 row found.

Now add an employee and verify results. Then, remove the employee and verify that the employee was deleted from the employees table.

Command> BEGIN 
       >   emp_actions.hire_employee(300,
       >     'Belden',
       >     'Enrique',
       >     'EBELDEN',
       >     '555.111.2222',
       >     '31-AUG-04',
       >     'AC_MGR',
       >     9000,
       >     .1,
       >     101,
       >     110);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
< 300, Belden, Enrique, EBELDEN, 555.111.2222, 2004-08-31 00:00:00, AC_MGR, 9000, 
.1, 101, 110 >
1 row found.
Command> BEGIN
       >   emp_actions.remove_employee (300);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
0 rows found.

Using synonyms for packages

TimesTen supports private and public synonyms (aliases) for database objects, including PL/SQL procedures, functions, and packages. Synonyms are often used to mask object names and object owners or to simplify SQL statements.

To create a private synonym for package foopkg in your schema:

CREATE SYNONYM synfoopkg FOR foopkg;

To create a public synonym for foopkg:

CREATE PUBLIC SYNONYM pubfoopkg FOR foopkg;

Also see "Using synonyms for procedures and functions" and "Required privileges for PL/SQL statements and operations".

Note:

You cannot create synonyms for individual member subprograms of a package.

This is valid:

create or replace public synonym pubtestpkg for testpkg;

This is not valid:

create or replace public synonym pubtestproc for testpkg.testproc;

Wrapping PL/SQL source code

Wrapping is the process of hiding PL/SQL source code. You can wrap PL/SQL source code with the wrap utility, which processes an input SQL file and wraps only the PL/SQL units in the file, such as a package specifications, package bodies, functions, and procedures.

Consider the following example, which uses a file wrap_test.sql to define a procedure named wraptest. It then uses the wrap utility to process wrap_test.sql. The procedure is created with the source code hidden, and executes successfully. As a final step, the ALL_OBJECTS view is queried to see the wrapped source code.

Here are the contents of wrap_test.sql:

CREATE OR REPLACE PROCEDURE wraptest IS
   TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   all_emps emp_tab;
BEGIN
   SELECT * BULK COLLECT INTO all_emps FROM employees;
   FOR i IN 1..10
      LOOP
         DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id);
      END LOOP;
END;
/

In the example that follows, "$" is the UNIX prompt, "Command>" is the ttIsql prompt, and user input is shown in bold.

$ wrap iname=wrap_test.sql
 
PL/SQL Wrapper: Release 11.2.0.2.0- Production on Wed Sep 14 12:59:27 2011
 
Copyright (c) 1993, 2009, Oracle.  All rights reserved.
 
Processing wrap_test.sql to wrap_test.plb
 
$ cat wrap_test.plb
CREATE OR REPLACE PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
124 12c
YZ6L0v2ntFaqttW8hSJD5IHIYccwg+nwNfZqfHQCv/9kMJyznwdLh8FepNXpWS1fzVBDkTke
LWlhFdFzCMfmmJ5GGrCwrqgngEhfRpq7ck5Dzsf7sDlnQeE3QGmb/yu9Dec1+JO2kOMlx3dq
BuC7fR2f5sjDtBeDXiGCC0kJ5QBVregtoBckZNO9MoiWS4w0jF6T1CPY0Aoi/KUwxC8S8I8n
amF5xGQDCYTDajs77orIGEqtX747k0YAO+r1e9adGUsVgZK1ONcTM/+Wit+LYKi7b03eJxdB
+aaKn/Lh
 
/
 
$ ttisql sampledb_1122
 
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=sampledb_1122";
Connection successful:
DSN=sampledb_1122;UID=myuserid;DataStore=.../install/info/DemoDataStore/
sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER
=.../install/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)
 
Command> @wrap_test.plb
 
CREATE OR REPLACE PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
124 12c
YZ6L0v2ntFaqttW8hSJD5IHIYccwg+nwNfZqfHQCv/9kMJyznwdLh8FepNXpWS1fzVBDkTke
LWlhFdFzCMfmmJ5GGrCwrqgngEhfRpq7ck5Dzsf7sDlnQeE3QGmb/yu9Dec1+JO2kOMlx3dq
BuC7fR2f5sjDtBeDXiGCC0kJ5QBVregtoBckZNO9MoiWS4w0jF6T1CPY0Aoi/KUwxC8S8I8n
amF5xGQDCYTDajs77orIGEqtX747k0YAO+r1e9adGUsVgZK1ONcTM/+Wit+LYKi7b03eJxdB
+aaKn/Lh
 
Procedure created.
 
Command> SET SERVEROUTPUT ON
Command> BEGIN
       >    wraptest();
       > END;
       > /
Emp Id: 100
Emp Id: 101
Emp Id: 102
Emp Id: 103
Emp Id: 104
Emp Id: 105
Emp Id: 106
Emp Id: 107
Emp Id: 108
Emp Id: 109
 
PL/SQL procedure successfully completed.
 
Command> SELECT text FROM all_source WHERE name = 'WRAPTEST';

< PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
124 12c
YZ6L0v2ntFaqttW8hSJD5IHIYccwg+nwNfZqfHQCv/9kMJyznwdLh8FepNXpWS1fzVBDkTke
LWlhFdFzCMfmmJ5GGrCwrqgngEhfRpq7ck5Dzsf7sDlnQeE3QGmb/yu9Dec1+JO2kOMlx3dq
BuC7fR2f5sjDtBeDXiGCC0kJ5QBVregtoBckZNO9MoiWS4w0jF6T1CPY0Aoi/KUwxC8S8I8n
amF5xGQDCYTDajs77orIGEqtX747k0YAO+r1e9adGUsVgZK1ONcTM/+Wit+LYKi7b03eJxdB
+aaKn/Lh
 
 >
1 row found.

Differences in TimesTen: transaction behavior

In TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes the following:

  • Any COMMIT or ROLLBACK statement

  • For PL/SQL users, any DDL statement

    This is because when PL/SQL is enabled (the PLSQL first connection attribute is set to 1), the TimesTen DDLCommitBehavior general connection attribute must be set to 0 for Oracle Database behavior (autocommit DDL).

For example, consider the following scenario, where you want to recompile a set of procedures. This would not work, because the first time ALTER PROCEDURE is executed, the cursor (pnamecurs) would be closed:

declare
   cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
begin
   for rec in pnamecurs loop
     execute immediate 'alter procedure ' || rec.object_name || ' compile';
   end loop;
end;

Instead, you can do something like the following, which fetches all the procedure names into an internal table then executes ALTER PROCEDURE on them with no active cursor.

declare
   cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
   type tbl is table of c%rowtype index by binary_integer;
   myprocs tbl;

begin
   open pnamecurs;
   fetch pnamecurs bulk collect into myprocs;
   close pnamecurs;
   for i in 1..myprocs.count loop
     execute immediate 'alter procedure ' || myprocs(i).object_name || ' compile';
   end loop;
end;