Examples Using EXECUTE IMMEDIATE
TimesTen supports the EXECUTE IMMEDIATE
statement.
See Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE Statement). This section provides these additional examples to consider as you develop your PL/SQL applications in TimesTen:
Using EXECUTE IMMEDIATE to Create a Table
Consider a situation where you do not know your table definition at compilation. By using an EXECUTE IMMEDIATE
statement, you can create your table at execution time.
This example shows a procedure that creates a table using the EXECUTE IMMEDIATE
statement. The procedure is executed with the table name and column definitions passed as parameters, then creation of the table is verified.
Command> CREATE OR REPLACE PROCEDURE create_table
(p_table_name VARCHAR2, p_col_specs VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name
|| ' (' || p_col_specs|| ' )';
END;
/
Procedure created.
Execute the procedure and verify the table is created.
Command> BEGIN
create_table ('EMPLOYEES_NAMES', 'id NUMBER (4)
PRIMARY KEY, name VARCHAR2 (40)');
END;
/
PL/SQL procedure successfully completed.
Command> DESCRIBE employees_names;
Table USER.EMPLOYEES_NAMES:
Columns:
*ID NUMBER (4) NOT NULL
NAME VARCHAR2 (40) INLINE
1 table found.
(primary key columns are indicated with *)
Using EXECUTE IMMEDIATE with a Single Row Query
You can use the EXECUTE IMMEDIATE SQL statement within your query.
In this example, the function get_emp
retrieves an employee record. The function is executed and returns the results in v_emprec
.
Command> CREATE OR REPLACE FUNCTION get_emp (p_emp_id NUMBER)
RETURN employees%ROWTYPE IS
v_stmt VARCHAR2 (200);
v_emprec employees%ROWTYPE;
BEGIN
v_stmt:= 'SELECT * FROM EMPLOYEES '||
'WHERE employee_id = :p_emp_id';
EXECUTE IMMEDIATE v_stmt INTO v_emprec USING p_emp_id;
RETURN v_emprec;
END;
/
Function created.
Command> DECLARE
v_emprec employees%ROWTYPE := GET_EMP (100);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Employee: ' || v_emprec.last_name);
END;
/
Employee: King
PL/SQL procedure successfully completed.
Using EXECUTE IMMEDIATE to Alter a Connection Attribute
This example uses an EXECUTE IMMEDIATE
statement with ALTER SESSION
to alter the PLSQL_OPTIMIZE_LEVEL
setting, calling the ttConfiguration
built-in procedure before and after to verify the results.
The next example calls ttConfiguration
from inside an EXECUTE IMMEDIATE
statement. Refer to ttConfiguration in Oracle TimesTen In-Memory Database
Reference.
Command> call ttconfiguration;
...
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 128 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
...
54 rows found.
Command> begin
execute immediate 'alter session set PLSQL_OPTIMIZE_LEVEL=3';
end;
/
PL/SQL procedure successfully completed.
Command> call ttconfiguration;
...
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 128 >
< PLSQL_OPTIMIZE_LEVEL, 3 >
< PLSQL_TIMEOUT, 30 >
...
54 rows found.
Using EXECUTE IMMEDIATE to Call a TimesTen Built-In Procedure
In PL/SQL, you can use an EXECUTE IMMEDIATE
statement with CALL
syntax to call a TimesTen built-in procedure.
For example, to call the built-in procedure ttConfiguration
and return its output result set, create a PL/SQL record type then use EXECUTE IMMEDIATE
with BULK COLLECT
to fetch the result set into an array.
See Built-In Procedures in Oracle TimesTen In-Memory Database Reference.
Command> DECLARE
TYPE ttConfig_record IS RECORD
(name varchar2(255), value varchar2 (255));
TYPE ttConfig_table IS TABLE OF ttConfig_record;
v_ttConfigs ttConfig_table;
BEGIN
EXECUTE IMMEDIATE 'CALL ttConfiguration'
BULK COLLECT into v_ttConfigs;
DBMS_OUTPUT.PUT_LINE ('Name: ' || v_ttConfigs(7).name
|| ' Value: ' || v_ttConfigs(7).value);
END;
/
Name: CommitBufferSizeMax Value: 10
PL/SQL procedure successfully completed.
Using EXECUTE IMMEDIATE with TimesTen-Specific Syntax
This example uses an EXECUTE IMMEDIATE
statement to execute a TimesTen SELECT FIRST
n
statement. This syntax is specific to TimesTen.
Command> DECLARE v_empid NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT FIRST 1 employee_id FROM employees'
INTO v_empid;
DBMS_OUTPUT.PUT_LINE ('Employee id: ' || v_empid);
END;
/
Employee id: 100
PL/SQL procedure successfully completed.