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

• Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE Statement)

• FORALL and BULK COLLECT Operations

• RETURNING INTO Clause

• Large Objects (LOBs)

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

Note:

• You must use dynamic SQL to execute DDL statements in PL/SQL. See the next section, Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE Statement).

• See Differences in TimesTen: Transaction Behavior for details about how TimesTen transaction behavior differs from Oracle Database behavior.

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

The example that follows shows how to execute a query. For additional examples using TimesTen SQL in PL/SQL, see Examples Using TimesTen SQL in PL/SQL.

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. The 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 several operations.

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

See EXECUTE IMMEDIATE Statement in Oracle Database PL/SQL Language Reference.

Note:

• See Differences in TimesTen: Transaction Behavior.

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

Following is a set of brief independent examples of EXECUTE IMMEDIATE. For additional examples, see Examples Using EXECUTE IMMEDIATE.

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.

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

The following example shows basic use of bulk binding and the FORALL statement, increasing the salary for employees with IDs 100, 102, 104, or 110. The FORALL statement bulk-binds the collection. For more information and examples on bulk binding, see Examples Using FORALL and BULK COLLECT.

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:

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.

Large Objects (LOBs)

TimesTen Classic 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.

• About LOBs

• LOB Locators

• Temporary LOBs

• Differences Between TimesTen LOBs and Oracle Database LOBs

• Using LOBs

• PL/SQL Package Support for LOBs

• Passthrough LOBs

Note:

TimesTen Scaleout supports LOBs as PL/SQL variables but not as database columns.

You can also refer to the following:

• LOB Data Types in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen

• Oracle Database SecureFiles and Large Objects Developer's Guide for general information about programming with LOBs (but not specific to TimesTen functionality)

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

Tip:

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

There are key differences between the TimesTen LOB implementation and the Oracle Database implementation.

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.

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

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.

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.