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.