3 Built-In Procedures

TimesTen built-in procedures extend standard ODBC and JDBC functionality.

You can invoke these procedures using the ODBC or JDBC procedure call interface. The procedure takes the position of the SQL statement, as illustrated in the following examples.

The following ODBC SQLExecDirect call invokes the ttOpsSetFlag built-in procedure to tell the optimizer that it should not generate temporary hash indexes when preparing commands:

SQLExecDirect (hstmt, (SQLCHAR*)
       "{CALL ttOptSetFlag ('TmpHash', 0)}", SQL_NTS);

This is the equivalent JDBC call:

CallableStatement cstmt = con.prepareCall 
          ("{CALL ttOptSetFlag ('TmpHash', 0)}");
cstmt.execute();

TimesTen built-in procedures can also be called from PL/SQL using the EXECUTE IMMEDIATE statement with CALL, as illustrated in the following example. See Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE Statement) in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for more details on this statement.

TimesTen built-in procedures that return result sets are not supported directly through OCI. You can use PL/SQL for this purpose. For an example, see Use of PL/SQL in OCI to Call a TimesTen Built-In Procedure in the Oracle TimesTen In-Memory Database C Developer's Guide.

For example, to call the built-in procedure ttConfiguration, create a PL/SQL record type and then SELECT INTO that record type. Because ttConfiguration returns multiple rows, use BULK COLLECT.

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(1).name
            || ' Value: ' || v_ttConfigs(1).value);
         end;
         /
 
PL/SQL procedure successfully completed.

You can also call built-in procedures from the ttIsql command line:

Command> call ttDBCompactConfig(2000,5,2000);
< 2000, 5, 2000 >
1 row found.

Note:

String parameter values for built-in procedures must be single-quoted as indicated in these examples, unless the value is NULL.