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
.