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
, andMERGE
-
Queries:
SELECT
-
Transaction control:
COMMIT
andROLLBACK
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
, orDELETE
. -
Execute a DDL statement such as
CREATE
orALTER
. For example, you can useALTER 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:
-
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 theEXECUTE IMMEDIATE
statement is executed are visible inside the outer block. This is useful for procedures such asDBMS_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 usingDBMS_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 theEXECUTE IMMEDIATE
statement are visible inside the outer block.
FORALL and BULK COLLECT Operations
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
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.
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:
-
The LOB column contains "XY".
-
Select locator
L1
for update. -
Select locator
L2
for update. -
Write "Z" through
L1
at offset 1. -
Read through locator
L1
. This would return "ZY". -
Read through locator
L2
. This would return "XY", becauseL2
remains read-consistent until it is used for a write. -
Write "W" through
L2
at 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").
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.
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.