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.
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.
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
salary for the employee with
employee_id=100 from the
employees table of the
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
Execute a DDL statement such as
ALTER. For example, you can use
ALTER SESSIONto 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.
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_SQLpackage 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
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
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
SQL and PL/SQL executed through
EXECUTE IMMEDIATErun in the same transaction as the outer block.
Any exception raised during execution of an
EXECUTE IMMEDIATEstatement is propagated to the outer block. Therefore, any errors on the error stack when the
EXECUTE IMMEDIATEstatement is executed are visible inside the outer block. This is useful for procedures such as
Errors on the error stack before execution of a PL/SQL block in an
EXECUTE IMMEDIATEstatement are visible inside the block, for example by using
The execution environment in which an
EXECUTE IMMEDIATEstatement executes is the same as for the outer block. PL/SQL and TimesTen parameters,
REF CURSORstate, and package state from the
EXECUTE IMMEDIATEstatement are visible inside the outer block.
FORALL and BULK COLLECT Operations
DELETEoperations multiple times. TimesTen supports bulk binding, which can result in significant performance improvement.
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
SAVE EXCEPTIONS allows an
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
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
RETURNING INTOclause, sometimes referred to as DML returning, with an
DELETEstatement to return specified columns or expressions, optionally including rowids, from rows that were affected by the action. This eliminates the need for a subsequent
SELECTstatement and separate round trip, in case, for example, you want to confirm what was affected or want the rowid after an insert or update.
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
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.
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)
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.
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.
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:
The LOB column contains "XY".
Write "Z" through
L1at offset 1.
Read through locator
L1. This would return "ZY".
Read through locator
L2. This would return "XY", because
L2remains read-consistent until it is used for a write.
Write "W" through
L2at offset 2.
Read through locator
L2. This would return "ZW". Before the write in the preceding step, the locator was updated with the latest data ("ZY").
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
FRAGMENTprocedures 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
HexadecimalLiteralin Constants in Oracle TimesTen In-Memory Database SQL Reference.
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
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
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.