How to open the sys refcursor in oracle

6 Using the ttIsql utility

The TimesTen utility is a general tool for working with a TimesTen data source. The command line interface is used to execute SQL statements and built-in commands to perform various operations. Some common tasks that are typically accomplished using include:

  • Database setup and maintenance. Creating tables and indexes, altering existing tables and updating table statistics can be performed quickly and easily using.

  • Retrieval of information on database structures. The definitions for tables, indexes and cache groups can be retrieved using built-in commands. In addition, the current size and state of the database can be displayed.

  • Optimizing database operations. The utility can be used to alter and display query optimizer plans for the purpose of tuning SQL operations. The time required to execute various ODBC function calls can also be displayed.

The following sections describe how the utility is used to perform these types of tasks:

For more information on commands, see the "ttIsql" section in the Oracle Times Ten In-Memory Database Reference.

Batch mode vs. interactive mode

The utility can be used in two distinctly different ways: batch mode or interactive mode. When is used in interactive mode, users type commands directly into from the console. When is used in batch mode, a prepared script of commands is executed by specifying the name of the file containing the commands.

Batch mode is commonly used for the following types of tasks:

  • Performing periodic maintenance operations including the updating of table statistics, compacting the database and purging log files.

  • Initializing a database by creating tables, indexes and cache groups and then populating the tables with data.

  • Generating simple reports by executing common queries.

Interactive mode is suited for the following types of tasks:

  • Experimenting with Times Ten features, testing design alternatives and improving query performance.

  • Solving database problems by examining database statistics.

  • Any other database tasks that are not performed routinely.

By default, when starting from the shell, is in interactive mode. The utility prompts you to type in a valid built-in command or SQL statement by printing the prompt. The following example starts ttIsql in interactive mode and then connects to a TimesTen database by executing the command with the DSN.

C: \> ttIsql Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. Command> connect MY_DSN; Connection successful: DSN = MY_DSN; DataStore = E: \ ds \ MY_DSN; DRIVER = E: \ WINNT \ System32 \ TTdv1122.dll; (Default setting AutoCommit = 1) Command>

When connecting to the database using, you can also specify the DSN or connection string on the command line. The command is implicitly executed.

C: \> ttIsql -connstr "DSN = MY_DSN" Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. connect "DSN = MY_DSN"; Connection successful: DSN = MY_DSN; DataStore = E: \ ds \ MY_DSN; DRIVER = E: \ WINNT \ System32 \ TTdv1122.dll; (Default setting AutoCommit = 1) Command>

Batch mode can be accessed in two different ways. The most common way is to specify the option on the command line followed by the name of the file to run.

For example, executing a file containing a statement looks like the following:

C: \> ttIsql -f create.sql -connstr "DSN = MY_DSN" Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. connect "DSN = MY_DSN" Connection successful: DSN = MY_DSN; DataStore = E: \ ds \ MY_DSN; DRIVER = E: \ WINNT \ System32 \ TTdv1122.dll; (Default setting AutoCommit = 1) run "create.sql" CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)) exit; Disconnecting ... Done. C: \>

The other way to use batch mode is to enter the command directly from the interactive command prompt. The command is followed by the name of the file containing built-in commands and SQL statements to execute:

Command> run "create.sql"; CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)) Command>

Defining default settings with the TTISQL environment variable

The utility can be customized to automatically execute a set of command line options every time a session is started from the command prompt. This is accomplished by setting an environment variable called to the value of the command line that you prefer. A summary of command line options is shown below. For a complete description of the command line options, see the "ttIsql" section in the Oracle Times Ten In-Memory Database Reference.

Usage: ttIsql [-h | -help | -helpcmds | -helpfull | -V] ttIsql [-f ] [-v ] [-e ] [-interactive] [-N ] [-wait] [{ | -connstr }]

The environment variable has the same syntax requirements as the command line. When starts up it reads the value of the environment variable and applies all options specified by the variable to the current session. If a particular command line option is specified in both the environment variable and the command line, then the command line version always takes precedence.

The procedure for setting the value of an environment variable differs based on the platform and shell that is started from. As an example, setting the environment variable on Windows could look like this:

C: \> set TTISQL = -connStr "DSN = MY_DSN" -e "autocommit 0; dssize;"

In this example, automatically connects to a DSN called, turns off autocommit, and displays the size of the database, as shown below:

C: \> ttIsql Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. Command> connect "DSN = MY_DSN"; Connection successful: DSN = MY_DSN; DataStore = E: \ ds \ MY_DSN; DRIVER = E: \ WINNT \ System32 \ TTdv1122.dll; (Default setting AutoCommit = 1) Command> autocommit 0; Command> dssize; The following values ​​are in KB: PERM_ALLOCATED_SIZE: 40960 PERM_IN_USE_SIZE: 9453 PERM_IN_USE_HIGH_WATER: 9453 TEMP_ALLOCATED_SIZE: 32768 TEMP_IN_USE_SIZE: 9442 TEMP_IN_USE_HIGH_WATER: 9885 Command>

Customizing the ttIsql command prompt

You can customize the command prompt by using the command with the attribute:

Command> set prompt MY_DSN; MY_DSN

You can specify a string format () that returns the name of the current connection:

Command> set prompt% c; con1

If you want to embed spaces, you must quote the string:

Command> set prompt "MY_DSN% c>"; MY_DSN con1>

Using the ttIsql online help

The utility has an online version of command syntax definitions and descriptions for all built-in commands. To access this online help from within use the command. To view a detailed description of any built-in commands type the command followed by one or more commands to display help for. The example below displays the online description for the and commands.

Command> help connect disconnect Arguments in <> are required. Arguments in [] are optional. Command Usage: connect [DSN | connection_string] [as ] Command Aliases: (none) Description: Connects to the data source specified by the optional DSN or connection string argument. If an argument is not given, then the DSN or connection string from the last successful connection is used. A connection ID may optionally be specified, for use in referring to the connection when multiple connections are enabled. The DSN is used as the default connection ID. If that ID is already in use, the connection will be assigned the ID "conN", where N is some number larger than 0. Requires an active connection: NO Requires autocommit turned off: NO Reports elapsed execution time: YES Works only with a TimesTen data source: NO Example: connect; -or- connect RunData; -or- connect "DSN = RunData"; -or- connect RunData as rundata1; Command Usage: disconnect [all] Command Aliases: (none) Description: Disconnects from the currently connected data source or all connections when the "all" argument is included. If a transaction is active when disconnecting then the transaction will be rolled back automatically. If a connection exists when executing the "bye", "quit" or "exit" commands then the "disconnect" command will be executed automatically. Requires an active connection: NO Requires autocommit turned off: NO Reports elapsed execution time: YES Works only with a TimesTen data source: NO Example: disconnect;

To view a short description of all built-in commands type the command without an argument. To view a detailed description of all built-in commands type the command followed by the argument.

To view the list of attributes that can be set or shown by using, enter:

Command> help attributes

Using the ttIsql 'editline' feature for UNIX only

On UNIX systems, you can use the 'editline' library to set up emacs (default) or vi bindings that enable you to scroll through previous commands, as well as edit and resubmit them. This feature is not available or needed on Windows.

To disable the 'editline' feature in, use the command.

The set up and keystroke information is described for each type of editor:

Emacs binding

To use the emacs binding, create a file and put "" on the last line of the file, run. The editline lib prints the current bindings.

The keystrokes when using with the emacs binding are:

KeystrokeAction
<Left-Arrow>Move the insertion point left. Back up.
<Right-Arrow>Move the insertion point right. Move forward.
<Up-Arrow>Scroll to the command prior to the one being displayed. Places the cursor at the end of the line.
<Down-Arrow>Scroll to a more recent command history item and put the cursor at the end of the line.
<Ctrl-A>Move the insertion point to the beginning of the line.
<Ctrl-E>Move the insertion point to the end of the line.
<Ctrl-K>"Kill" (Save and erase) the characters on the command line from the current position to the end of the line.
<Ctrl-Y>"Yank" (Restore) the characters previously saved and insert them at the current insertion point.
<Ctrl-F>Forward char - move forward 1 (see Right Arrow).
<Ctrl-B>Backward char - move back 1 (see Left Arrow).
<Ctrl-P>Previous History (see Up Arrow).
<Ctrl-N>Next History (see up Down Arrow).

vi binding

To use the vi bindings, create a file and put "" in the file, run. To get the current settings, create a file and put "" on the last line of the file. When you execute, the editline lib prints the current bindings.

The keystrokes when using with the vi binding are:

KeystrokeAction
, hMove the insertion point left (back up).
, lMove the insertion point right (forward).
, kScroll to the prior command in the history and put the cursor at the end of the line.
, jScroll to the next command in the history and put the cursor at the end of the line.
ESCVi Command mode.
0, $Move the insertion point to the beginning of the line, move to the end of the line.
i, iInsert mode, insert mode at the beginning of the line.
a, AAdd ("Insert after") mode, append at the end of the line
R.Replace mode.
C.Change to end of line.
B.Move to previous word.
eMove to end of word.
<Ctrl-P>Previous History (see Up Arrow).
<Ctrl-N>Next History (see up Down Arrow).

Using the ttIsql command history

The utility stores a list of the last 100 commands executed within the current session. The commands in this list can be viewed or executed again without having to type the entire command over. Both SQL statements and built-in commands are stored in the history list. Use the command ("") to view the list of previously executed commands. For example:

Command> h; 8 INSERT INTO T3 VALUES (3) 9 INSERT INTO T1 VALUES (4) 10 INSERT INTO T2 VALUES (5) 11 INSERT INTO T3 VALUES (6) 12 autocommit 0 13 showplan 14 SELECT * FROM T1, t2, t3 WHERE A = B AND B = C AND A = B 15 trytbllocks 0 16 tryserial 0 17 SELECT * FROM T1, t2, t3 WHERE A = B AND B = C AND A = B Command>

The command displays the last 10 SQL statements or built-in commands executed. To display more than that last 10 commands specify the maximum number to display as an argument to the command.

Each entry in the history list is identified by a unique number. The character followed by the number of the command can be used to execute the command again. For example:

Command> Command>! 12; autocommit 0 Command>

To execute the last command again simply type a sequence of two characters:

Command> !!; autocommit 0 Command>

To execute the last command that begins with a given string type the character followed by the first few letters of the command. For example:

Command>! automobile; autocommit 0 Command>

Saving and clearing the ttIsql command history

You can save the list of commands that stores by using the command:

Command> savehistory history.txt;

If the output file already exists, use the option to append the new command history to the file or the option to overwrite the file. The next example shows how to append new command history to an existing file.

Command> savehistory -a history.txt;

You can clear the list of commands that stores by using the command:

Command> clearhistory;

Using the ttIsql edit command

You can use the command to edit a file or edit commands in a text editor. The command starts a text editor such as,, or. For more information on changing the default text editor, see "Changing the default text editor for the ttIsql edit command".

The syntax for the command is as follows:

Command> edit [file | ! history_search_command]

You can only use one parameter at a time. The parameter is defined as the character followed by the number of the command or a search string. If you do not specify a character, the command interprets the parameter as. is the name of the file that you want to edit. If you do not specify a parameter or specify, the last command is edited.

When you specify a parameter, the editor edits the specified file. If TimesTen does not find an exact file match for the specified parameter in the current working directory, it searches for. If neither file exists, the editor creates the specified file in the current working directory. You can specify a path in the parameter.

The following example edits the file:

Command> edit new.sql;

The following example edits the file in the directory:

Command> edit /scripts/new.sql;

If you execute the command with a parameter, does not execute the contents of the file after you exit the editor.

You can edit a SQL statement that is stored in the history list of the current session. When calling the command specify the character followed by the number of the command or a search string. The editor opens the command in a temporary file that you can save in a preferred location. For more information on using the command, see "Using the ttIsql command history".

The following example edits command:

Command> edit! 2;

The following example searches for and edits the last command that contains the search string:

Command> edit! Create;

The following example executes a statement and then uses the command to edit the statement in a text editor:

Command> CREATE TABLE t1 (c1 VARCHAR (10) NOT INLINE NOT NULL, c2 VARCHAR (144) INLINE NOT NULL); Command> edit;

The prior example is equivalent to using the command with the parameter:

Command> CREATE TABLE t1 (c1 VARCHAR (10) NOT INLINE NOT NULL, c2 VARCHAR (144) INLINE NOT NULL); Command> edit !!;

If you execute the command with a parameter, executes the contents of the file after you exit the editor. The contents of the file are executed as a single command. If you do not want to execute the contents of the file, delete the contents of the file and save the file before you exit the editor.

Changing the default text editor for the ttIsql edit command

You can specify the default editor by defining the define alias. The following example sets the default editor to:

Command> DEFINE _EDITOR = vi

If you do not define the define alias, use the editor specified by the environment variable. If the define alias and the environment variables are not set, uses the editor specified by the environment variable. When,, and EDITOR are not set, is used for Unix and is used for Windows.

Working with character sets

The utility supports the character sets listed in "Supported character sets" in the Oracle Times Ten In-Memory Database Reference. The ability of to display characters depends on the native operating system locale settings of the terminal on which you are using.

To override the locale-based output format, use the option or the option. The valid values ​​for these options are (the default) and. If you choose and encounters a Unicode character, it displays it in escaped format.

You do not need to have an active connection to change the output method.

Displaying database structure information

There are several commands that display information on database structures. The most useful commands are summarized below:

  • - Displays information on database objects.

  • - Displays the attributes of cache groups.

  • - Reports the current sizes of the permanent and temporary database memory regions.

  • - Displays the size of tables that have been analyzed with the tool.

  • - Displays a summary of the current state of the database.

Using the ttIsql describe command

Use the command to display information on individual database objects. Displays parameters for prepared SQL statements and built-in procedures. The argument to the command can be the name of a table, cache group, view, materialized view, materialized view log, sequence, synonym, a built-in procedure, a SQL statement or a command ID for a previously prepared SQL statement, a PL / SQL function, PL / SQL procedure or PL / SQL package.

The command requires a semicolon character to terminate the command.

Command> CREATE TABLE T1 (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)); Command> describe T1>; Table USER.T1: Columns: * KEY NUMBER NOT NULL VALUE CHAR (64) 1 table found. (primary key columns are indicated with *) Command> describe SELECT * FROM T1 WHERE KEY = ?; Prepared Statement: Parameters: Parameter 1 NUMBER Columns: KEY NUMBER NOT NULL VALUE CHAR (64) Command> describe ttOptUseIndex; Procedure TTOPTUSEINDEX: Parameters: Parameter INDOPTION VARCHAR (1024) Columns: (none) 1 procedure found. Command>

Using the ttIsql cachegroups command

The command is used to provide detailed information on cache groups defined in the current database. The attributes of the root and child tables defined in the cache group are displayed in addition to the clauses associated with the cache group. The argument to the command is the name of the cache group that you want to display information for.

Command> cachegroups; Cache Group CACHEUSER.READCACHE: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: Paused Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: ORATT.READTAB Table Type: Read Only Cache Group CACHEUSER.WRITECACHE: Cache Group Type: Asynchronous Writethrough global (Dynamic) Autorefresh: No Aging: LRU on Root Table: ORATT.WRITETAB Table Type: Propagate 2 cache groups found.

Using the ttIsql dssize command

The command is used to report the current memory status of the permanent and temporary memory regions as well as the maximum, allocated and in-use sizes for the database.

The following example uses the option to print the database size information in KB:

Command> dssize k; The following values ​​are in KB: PERM_ALLOCATED_SIZE: 40960 PERM_IN_USE_SIZE: 9742 PERM_IN_USE_HIGH_WATER: 9742 TEMP_ALLOCATED_SIZE: 32768 TEMP_IN_USE_SIZE: 9442 TEMP_IN_USE_HIGH_WATER: 9505

For more information on the command, see "ttIsql" in the Oracle Times Ten In-Memory Database Reference.

Using the ttIsql tablesize command

The command displays the detailed analysis of the amount of space used by a table. Once you call the built-in procedure, which analyzes the table size of the indicated tables, the command displays the total size data for all analyzed tables.

Grade:

For more details, see "ttComputeTabSizes" in the Oracle TimesTen In-Memory Database Reference.

Executing the command with no arguments displays available sizing information for all tables that have had the computation run. When you provide a table as an argument, displays available sizing only for the indicated table.

The syntax for is as follows:

tablesize [[owner_name_pattern.] table_name_pattern]

The following example invokes the built-in procedure to calculate the table size of the table. Then, the command displays the sizing information gathered for the table.

Command> call ttComputeTabSizes ('employees'); Command> tablesize employees; Sizes of USER1.EMPLOYEES: INLINE_ALLOC_BYTES: 60432 NUM_USED_ROWS: 107 NUM_FREE_ROWS: 149 AVG_ROW_LEN: 236 OUT_OF_LINE_BYTES: 0 METADATA_BYTES: 1304 TOTAL_BYTES: 556-2936 TOTAL_BYTES: 1 found 2011_BYTES: 286-2936

These values ​​provide insights into overhead and how the total space is used for the table.

For example:

  • The value describes the number of rows allocated for the table, but not currently in use. Space occupied by free rows cannot be used by the system for storing other system objects or structures.

  • Use the value to calculate how much permanent space your table occupies.

  • is the time of the last size computation. If you want a more recent computation, re-execute and display the new output.

You can find a description for each calculated value in the "SYS.ALL_TAB_SIZES" section in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Using the ttIsql monitor command

The command displays all of the information provided by the command plus additional statistics on the number of connections, checkpoints, lock timeouts, commits, rollbacks and other information collected since the last time the database was loaded into memory.

Listing database objects by object type

You can use to list tables, indexes, views, sequences, synonyms, PL / SQL functions, procedures and packages in a database. Commands prefixed by display all of this type of object. For example, the command lists PL / SQL functions that are owned by the user, whereas lists all PL / SQL functions.

You can optionally specify patterns for object owners and object names.

Use these commands to list database objects:

  • and - lists tables.

  • and - Lists indexes.

  • and - Lists views.

  • and - Lists sequences.

  • and - Lists synonyms.

  • and - Lists PL / SQL functions.

  • and - Lists PL / SQL procedures.

  • and - Lists PL / SQL packages.

Grade:

For details on each of these commands, see the "ttIsql" section in the Oracle Times Ten In-Memory Database Reference.

The following example demonstrates the and commands. User creates a procedure called while connected to. Note that a slash character (/) is entered on a new line following the PL / SQL statements.

The command and the command show that it is the only PL / SQL procedure in the database.

$ ttisql myDSN Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. connect "DSN = myDSN"; Connection successful: DSN = myDSN; UID = terry; DataStore = / scratch / terry / myDSN; DatabaseCharacter Set = AL32UTF8; ConnectionCharacterSet = US7ASCII; PermSize = 32; TypeMode = 0; (Default setting AutoCommit = 1) Command> create or replace procedure proc1 as begin null; end; > / Procedure created. Command> procedures; TERRY.PROC1 1 procedure found. Command> allprocedures; TERRY.PROC1 1 procedure found.

Now connect to the same DSN as Pat and create a procedure called. The command shows the PL / SQL procedures created by Terry and.

$ ttisql "dsn = myDSN; uid = PAT" Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. connect "dsn = myDSN; uid = PAT"; Connection successful: DSN = myDSN; UID = PAT; DataStore = / scratch / terry / myDSN; DatabaseCharacterSet = AL32UTF8; ConnectionCharacterSet = US7ASCII; PermSize = 32; TypeMode = 0; (Default setting AutoCommit = 1) Command> create or replace procedure q as begin null; end; > / Procedure created. Command> procedures; PAT.Q 1 procedure found. Command> allprocedures; TERRY.PROC1 PAT.Q 2 procedures found.

Viewing and setting connection attributes

You can view and set connection attributes with the and commands. For a list of the attributes that you can view and set with, see "Connection Attributes" in Oracle TimesTen In-Memory Database Reference.

To view the setting for the attribute, enter:

Command> show passthrough; PassThrough = 0

To change the setting, enter:

Command> set passthrough 1;

Working with transactions

The utility has several built-in commands for managing transactions. These commands are summarized below:

  • - Turns on or off the autocommit feature. This can also be set as an attribute of the command.

  • - Commits the current transaction.

  • - Commits the current transaction and ensures that the committed work is recovered in case of database failure.

  • - Rolls back the current transaction.

  • - Changes to the transaction isolation level. This can also be set as an attribute of the command.

  • - Specifies the number of seconds to wait for a SQL statement to execute before returning to the application. This can also be set as an attribute of the command.

When starting, the autocommit feature is turned on by default, even within a SQL script. In this mode, every SQL operation against the database is committed automatically. When autocommit is turned off, then automatic commit depends on the setting for the connection attribute and the user executing DDL. For more information, see "Relationship between autocommit and DDLCommitBehavior".

To turn the autocommit feature off, execute the command with an argument of 0. When autocommit is turned off, transactions must be committed or rolled back manually by executing the, or commands. The command ensures that the transaction's effect is preserved in a case of database failure. If autocommit is off when exits, any uncommitted statements are rolled back and reported by.

The command can be used to change the current connection's transaction isolation properties. The isolation can be changed only at the beginning of a transaction. The command accepts one of the following constants: and. If the command is modified without an argument then the current isolation level is reported.

The command sets the timeout period for SQL statements. If the execution time of a SQL statement exceeds the number of seconds set by the command, the SQL statement is not executed and an 6111 error is generated. For details, see "Setting a timeout duration for SQL statements" in the Oracle TimesTen In-Memory Database Java Developer's Guide and "Setting a timeout duration for SQL statements" in the Oracle TimesTen In-Memory Database C Developer's Guide.

Grade:

TimesTen rollback and query timeout features do not stop TimesTen Cache operations that are being processed on the Oracle database. This includes passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating and dynamic loading.

The following example demonstrates the common use of the built-in transaction management commands.

E: \> ttIsql Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. Command> connect "DSN = MY_DSN"; Connection successful: DSN = MY_DSN; DataStore = E: \ ds \ MY_DSN; DRIVER = E: \ WINNT \ System32 \ TTdv1122.dll; (Default setting AutoCommit = 1) Command> autocommit 0; Command> CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)); Command> commit; Command> INSERT INTO LOOKUP VALUES (1, 'ABC'); 1 row inserted. Command> SELECT * FROM LOOKUP; <1, ABC> 1 row found. Command> rollback; Command> SELECT * FROM LOOKUP; 0 rows found. Command> isolation; isolation = READ_COMMITTED Command> commitdurable; Command> sqlquerytimeout 10; Command> sqlquerytimeout; Query timeout = 10 seconds Command> disconnect; Disconnecting ... Command> exit; Done.

Working with prepared and parameterized SQL statements

Preparing a SQL statement just once and then executing it multiple times is much more efficient for Times Ten applications than re-preparing the statement each time it is to be executed. has a set of built-in commands to work with prepared SQL statements. These commands are summarized below:

  • - Prepares a SQL statement. Corresponds to an ODBC call.

  • - Executes a previously prepared statement. Corresponds to an ODBC call.

  • - Executes a previously prepared statement and fetches all result rows. Corresponds to a call followed by one or more calls to.

  • - Fetches all result rows for a previously executed statement. Corresponds to one or more calls.

  • - Fetches only one row for a previously executed statement. Corresponds to exactly one call.

  • - Closes the result set cursor on a previously executed statement that generated a result set. Corresponds to a call with the option.

  • - Closes a previously prepared statement. Corresponds to a call with the option.

  • - Describes the prepared statement including the input parameters and the result columns.

The utility prepared statement commands also handle SQL statement parameter markers. When parameter markers are included in a prepared SQL statement, automatically prompts for the value of each parameter in the statement at execution time.

The example below uses the prepared statement commands of the utility to prepare an statement into a table containing a and a column. The statement is prepared and then executed twice with different values ​​for each of the statement's two parameters. The utility command is used to display the elapsed time required to executed the primary ODBC function call associated with each command.

Command> connect "DSN = MY_DSN"; Connection successful: DSN = MY_DSN; DataStore = E: \ ds \ MY_DSN; DRIVER = E: \ WINNT \ System32 \ TTdv1122.dll; (Default setting AutoCommit = 1) Command> timing 1; Command> create table t1 (key number not null primary key, value char (20)); Execution time (SQLExecute) = 0.007247 seconds. Command> prepare insert into t1 values ​​(: f,: g); Execution time (SQLPrepare) = 0.000603 seconds. Command> exec; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/' to leave the remaining parameters unbound and execute the command. Enter parameter 1 'F' (NUMBER)> 1; Enter parameter 2 'G' (CHAR)> 'abc'; 1 row inserted. Execution time (SQLExecute) = 0.000454 seconds. Command> exec; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/' to leave the remaining parameters unbound and execute the help command. Enter parameter 1 'F' (NUMBER)> 2; Enter parameter 2 'G' (CHAR)> 'def'; 1 row inserted. Execution time (SQLExecute) = 0.000300 seconds. Command> free; Command> select * from t1; <1, abc> <2, def> 2 rows found. Execution time (SQLExecute + Fetch Loop) = 0.000226 seconds. Command> disconnect; Disconnecting ... Execution time (SQLDisconnect) = 2.911396 seconds. Command>

In the example above, the command is immediately followed by the SQL statement to prepare. Whenever a SQL statement is prepared in, a unique command ID is assigned to the prepared statement. The utility uses this ID to keep track of multiple prepared statements. A maximum of 256 prepared statements can exist in a session simultaneously. When the command is executed, the command ID is automatically disassociated from the prepared SQL statement.

To see the command IDs generated by when using the prepared statement commands, set the verbosity level to 4 using the command before preparing the statement, or use the command to list all prepared statements with their IDs.

Command IDs can be referenced explicitly when using 's prepared statement commands. For a complete description of the syntax of 's prepared statement commands see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference or type at the command prompt.

The example below prepares and executes a statement with a predicate containing one parameter. The command is used to fetch the result row generated by the statement. The command is used to display the execution plan used by the Times Ten query optimizer when the statement is executed. In addition, the verbosity level is set to 4 so that the command ID used by to keep track of the prepared statement is displayed.

Command> connect "DSN = MY_DSN"; Connection successful: DSN = MY_DSN; DataStore = E: \ ds \ MY_DSN; DRIVER = E: \ WINNT \ Sys tem32 \ TTdv1122.dll; (Default setting AutoCommit = 1) The command succeeded. Command> CREATE TABLE T1 (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)); The command succeeded. Command> INSERT INTO T1 VALUES (1, 'abc'); 1 row inserted. The command succeeded. Command> autocommit 0; Command> showplan 1; Command> verbosity 4; The command succeeded. Command> prepare SELECT * FROM T1 WHERE KEY = ?; Assigning new prepared command id = 0. Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkRangeScan TBLNAME: T1 IXNAME: T1 INDEXED CONDITION: T1.KEY = _QMARK_1 NOT INDEXED: The command succeeded. Command> exec; Executing prepared command id = 0. Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/;' to leave the remaining parameters unbound and execute the command. Enter parameter 1 '_QMARK_1' (NUMBER)> 1 The command succeeded. Command> fetchone; Fetching prepared command id = 0. <1, abc> 1 row found. The command succeeded. Command> close; Closing prepared command id = 0. The command succeeded. Command> free; Freeing prepared command id = 0. The command succeeded. Command> commit; The command succeeded. Command> disconnect; Disconnecting ... The command succeeded. Command>

Using, declaring, and setting variables

The following sections describe how to declare, set and use bind variables in ttIsql:

Declaring and setting bind variables

You can declare and set variables and arrays in ttIsql that can be referenced in a SQL statement, SQL script, or PL / SQL block. The variables declared using the and command must be one of the following data types:,,,,,,,, or. However, when binding arrays, Timesten supports only binding arrays of the,,,, or data types.

Grade:

All variables that are declared exist for the life of the ttIsql session. However, if you declare a new variable with the same name, the new variable replaces the old variable.

The following examples declare bind variables with the or command for a number, character string, and an array. Each is assigned to a value either when declared or by using the or command.

Grade:

For details on the syntax for these commands, see "ttIsql" in the Oracle Times Ten In-Memory Database Reference.
Command> VARIABLE house_number NUMBER: = 268; Command> PRINT house_number; HOUSE_NUMBER: 268 Command> VARIABLE street_name VARCHAR2 (15); Command> SETVARIABLE street_name: = 'Oracle Parkway'; Command> VARIABLE occupants [5] VARCHAR2 (15); Command> SETVARIABLE occupants [1]: = 'Pat'; Command> SETVARIABLE occupants [2]: = 'Terry'; Command> PRINT occupants; OCCUPANTS: ARRAY [5] (Current Size 2) OCCUPANTS [1]: Pat OCCUPANTS [2]: Terry

The following is an example of binding multiple values ​​in an array using square brackets to delineate the values ​​and commas to separate each value for the array:

Command> VARIABLE occupants [5] VARCHAR2 (15): = ['Pat', 'Terry']; Command> PRINT occupants; OCCUPANTS: ARRAY [5] (Current Size 2) OCCUPANTS [1]: Pat OCCUPANTS [2]: Terry

When using array binds, PL / SQL enables you to bind each variable to a PL / SQL variable with the following declaration, where is any unique identifier for the PL / SQL data type and can be specified as,,, or.

TYPE TypeName IS TABLE OF DataType () INDEX BY BINARY_INTEGER;

If the variable is declared as array of, you can bind it to a PL / SQL variable of the following data types:,,, or. To do so, use the appropriate declaration:

TYPE TypeName IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE TypeName IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; TYPE TypeName IS TABLE OF FLOAT INDEX BY BINARY_INTEGER; TYPE TypeName IS TABLE OF DOUBLE PRECISION INDEX BY BINARY_INTEGER;

The following example declares the array, which is then declared and used within a PL / SQL block:

Command> VARIABLE occupants [5] VARCHAR2 (15); Command> SETVARIABLE occupants [1]: = 'Pat'; Command> SETVARIABLE occupants [2]: = 'Terry'; Command> DECLARE> TYPE occuname IS TABLE OF VARCHAR2 (15) INDEX BY BINARY_INTEGER; > x occuname; > BEGIN> x: =: occupants; > FOR LROW IN x.FIRST..x.LAST LOOP> x (LROW): = x (LROW) || 'Doe'; > END LOOP; >: occupants: = x; > END; > / PL / SQL procedure successfully completed. Command> PRINT occupants; OCCUPANTS: ARRAY [5] (Current Size 2) OCCUPANTS [1]: Pat Doe OCCUPANTS [2]: Terry Doe

Automatically creating bind variables for retrieved columns

When you set on in ttIsql, TimesTen creates an automatic bind variable named after each column in the last fetched row. An automatic bind variable can be used in the same manner of any bind variable.

The following example selects all rows from the table. Since all columns are retrieved, automatic variables are created and named for each column. The bind variable contains the last value retrieved for each column.

Command> SET AUTOVARIABLES ON; Command> SELECT * FROM employees; ... <204, Hermann, Baer, ​​HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000, , 101, 70> <205, Shelley, Higgins, SHIGGINS, 515.123. 8080, 1994-06-07 00:00:00, AC_MGR, 12000, , 101, 110> <206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT , 8300, , 205, 110> Command> PRINT; EMPLOYEE_ID: 206 FIRST_NAME: William LAST_NAME: Gietz EMAIL: WGIETZ PHONE_NUMBER: 515.123.8181 HIRE_DATE: 1994-06-07 00:00:00 JOB_ID: AC_ACCOUNT SALARY: 8300 COMMISSION_PCT: MANAGER_ID: 205 DEPARTM

If you provide an alias for a column name, the automatic bind variable name uses the alias, rather than the column name.

Command> SET AUTOVARIABLES ON; Command> SELECT employee_id ID, First_name SURNAME, last_name LASTNAME FROM employees; ID, SURNAME, LASTNAME ... <204, Hermann, Baer> <205, Shelley, Higgins> <206, William, Gietz> 107 rows found. Command> PRINT; ID: 206 SURNAME: William LASTNAME: Gietz

For any query that fetches data without a known named column, set to show the column names. The following example shows that the columns returns from built-in procedure are and.

Command> SET AUTOVARIABLES ON; Command> SET COLUMNLABELS ON; Command> call TTCONFIGURATION ('PLSQL'); PARAMNAME, PARAMVALUE 1 row found. Command> IF: paramvalue = 1 THEN "e: PLSQL is enabled"; PLSQL is enabled Command> IF NOT: paramvalue = 1 THEN "e: PLSQL is not enabled";

You can also use the command to show the column names. The following example uses the describe command to display the column names for the built-in procedure.

Command> DESCRIBE TTCONFIGURATION; Procedure TTCONFIGURATION: Parameters: PARAMNAME TT_VARCHAR (30) Columns: PARAMNAME TT_VARCHAR (30) NOT NULL PARAMVALUE TT_VARCHAR (1024) 1 procedure found.

Creating and executing PL / SQL blocks

You can create and execute PL / SQL blocks from the command line.

Set on to display results generated from the PL / SQL block:

Command> set serveroutput on

Create an anonymous block that puts a text line in the output buffer. Note that the block must be terminated with a slash (/).

Command> BEGIN> DBMS_OUTPUT.put_line (> 'Welcome!'); > END; > / Welcome! PL / SQL procedure successfully completed. Command>

See the Oracle TimesTen In-Memory Database PL / SQL Developer's Guide for more examples. For information on error handling in for PL / SQL objects, see "Showing errors in ttIsql" in the Oracle Times Ten In-Memory Database PL / SQL Developer's Guide.

Passing data from PL / SQL using OUT parameters

You can pass data back to applications from PL / SQL by using parameters. This example returns information about how full a TimesTen database is.

Create the PL / SQL procedure and use SQL to provide values ​​for the,,, and parameters.

Command> CREATE OR REPLACE PROCEDURE tt_space_info> (permpct OUT PLS_INTEGER,> permmaxpct OUT PLS_INTEGER,> temppct OUT PLS_INTEGER,> tempmaxpct OUT PLS_INTEGER) AS> monitor sys.monitor% ROWTYPE; > BEGIN> SELECT * INTO monitor FROM sys.monitor; > permpct: = monitor.perm_in_use_size * 100 / monitor.perm_allocated_size; > permmaxpct: = monitor.perm_in_use_high_water * 100 / monitor.perm_allocated_size; > temppct: = monitor.temp_in_use_size * 100 / monitor.temp_allocated_size; > tempmaxpct: = monitor.temp_in_use_high_water * 100 / monitor.temp_allocated_size; > END; > / Procedure created.

Declare the variables and call. The parameter values ​​are passed back to so they can be printed:

Command> VARIABLE permpct NUMBER Command> VARIABLE permpctmax NUMBER Command> VARIABLE temppct NUMBER Command> VARIABLE temppctmax NUMBER Command> BEGIN> tt_space_info (: permpct,: permpctmax,: temppct,: temppctmax); > END; > / PL / SQL procedure successfully completed. Command> PRINT permpct; PERMPCT: 4 Command> PRINT permpctmax; PERMPCTMAX: 4 Command> PRINT temppct; TEMPPCT: 11 Command> PRINT temppctmax; TEMPPCTMAX: 11

You can also pass back a statement handle that can be executed by a PL / SQL statement with an refcursor parameter. The PL / SQL statement can choose the query associated with the cursor. The following example opens a refcursor, which randomly chooses between ascending or descending order.

Command> VARIABLE ref REFCURSOR; Command> BEGIN> IF (mod (dbms_random.random (), 2) = 0) THEN> open: ref for select object_name from SYS.ALL_OBJECTS order by 1 asc; > ELSE> open: ref for select object_name from SYS.ALL_OBJECTS order by 1 desc; > end if; > END; > / PL / SQL procedure successfully completed.

To fetch the result set from the refcursor, use the command:

Command> PRINT ref REF: ... 143 rows found.

Or if the result set was ordered in descending order, the following would print:

Command> PRINT ref REF: ... 143 rows found.

Conditional control with the IF-THEN-ELSE command construct

The command construct enables you to implement conditional branching logic in a ttIsql session. The command tests a condition and decides whether to execute commands within the clause or the optional clause. The commands executed can be SQL statements, SQL scripts, PL / SQL blocks, or TimesTen utilities.

Grade:

For details on the syntax of the construct, see the "ttIsql" section in the Oracle Times Ten In-Memory Database Reference.

The following example creates and tests a bind variable to see if PL / SQL is enabled. It uses the command to create the bind variable from the result of the call to. The value can be tested within the conditional by testing the variable.

Command> SET AUTOVARIABLES ON; Command> CALL TTCONFIGURATION ('PLSQL'); PARAMNAME, PARAMVALUE 1 row found. Command> IF: paramvalue = 1 THEN "e: PLSQL is enabled"> ELSE "e: PLSQL is not enabled"; PLSQL is enabled

The following example checks to see that the table exists. If it does not, it executes the SQL script that creates the table; otherwise, a message is printed out.

Command> IF 0 = "SELECT COUNT (*) FROM SYS.TABLES> WHERE TBLNAME LIKE 'employees';" > THEN "e: EMPLOYEES table already exists"> ELSE "@ HR_CRE_TT.SQL;"; EMPLOYEES table already exists

Loading data from an Oracle database into a TimesTen table

You can load the results of a SQL query from a back-end Oracle database into a single table on TimesTen without creating a cache grid, cache group, and cache table to contain the results. TimesTen provides tools that execute a user-provided statement on the Oracle database and load the result set into a table on TimesTen.

The following are the major steps that are performed to accomplish this task:

  1. Create a table with the correct columns and data types on TimesTen.

  2. Provide a statement that is executed on the Oracle database to generate the desired result set.

  3. Load the result set into the table on Times Ten.

TimesTen provides two methods to accomplish these tasks:

  • The utility provides the command that, once provided the TimesTen table name and the statement, automatically creates the TimesTen table, executes the statement on the Oracle database, and loads the result set into the TimesTen table. This command is fully described in "Use ttIsql to create a table and load SQL query results".

  • The built-in procedure evaluates the user-provided statement to generate a statement that can be executed to create a table on TimesTen, which would be appropriate to receive the result set from the statement. The built-in procedure executes the statement on the Oracle database and loads the result set into the Times Ten table. These built-in procedures are described in "Use TimesTen built-in procedures to recommend a table and load SQL query results".

Both methods require the following:

  • Both the Times Ten and Oracle databases involved must be configured with the same national database character set.

  • When you connect to the Times Ten database, the connection must contain the same connection attributes that are required when using cache groups, as follows:

    • The user name, which must be the same on both the Times Ten and Oracle databases

      Grade:

      The correct privileges must be granted to these users on each database for the SQL statements that are executed on their behalf.
    • The correct passwords for each user as appropriate in the and connection attributes

    • The connection attributes that identifies the Oracle database instance

  • For either method, the user provides the following:

    • The table name on the Times Ten database where the results of the SQL query is loaded. If the owner of the table is not provided, the table is created with the current user as the owner. The table name is not required to be the same name as the table name on the Oracle database against which the SQL statement is executed. This table does not require a primary key. If the table already exists, a warning is issued and the retrieved rows are appended to the table.

    • Optionally, the number of parallel threads that you would like to be used in parallel when loading the table with the result set. These defaults to four.

    • The SQL statement that is executed against the Oracle database to obtain the required rows. The tables specified within this statement must be fully qualified, unless the tables are within the schema of the current Oracle database user. The query cannot have any parameter bindings.

      The list should contain either simple column references or column aliases. For example, any expressions in the list should be provided with a column alias. You can also use the column alias to avoid duplication of column names in the result table. For example, instead of using, use, which would create a column named.

TimesTen evaluates the statement and uses the column names, data types, and nullability information to create the table on TimesTen into which the result set is loaded. The column names and data types (either the same or mapped) are taken from the tables on the Oracle database involved in the statement. However, other Oracle Database table definition information (such as values, primary key, foreign key relationships, and so on) are not used when creating the statement for the Times Ten table.

Grade:

If the evaluation returns any unsupported data types or if the query cannot be executed on the Oracle database, such as from a syntax error, a warning is logged and a comment is displayed for the unsupported column in the output. However, if the data type is not supported by TimesTen, you can cast the data type directly in the list to a TimesTen supported data type.

The load process does not check that the column data types and sizes in the Times Ten table match the data types and sizes of the result set. Instead, the insert is attempted and if the column data types cannot be mapped or the Oracle Database data from the SQL query exceeds the TimesTen column size, TimesTen returns an error.

The load is automatically committed every 256 rows. If an error is encountered during the load, it terminates the load, but does not roll back any committed transactions. Any errors returned from the Oracle database are reported in the same manner as when using cache groups.

Because you can use the command and the built-in procedure to load into an existing TimesTen table, the following restrictions apply:

  • You cannot load into system tables, dictionary tables, temporary tables, detail tables of views, materialized view tables, materialized view log tables, or tables already in a cache group. In addition, you cannot use a synonym for the table name.

  • If you load the result set into an existing table that is the referencing table (child table) of a foreign key constraint, the constraint is not validated. As a result, rows that are missing a parent row may be loaded. Instead, you should verify all foreign keys after the table is loaded.

The following sections provide more details on each individual method:

Use ttIsql to create a table and load SQL query results

The utility provides the command, which takes a table name, the number of parallel threads, and a statement that is to be executed on the Oracle database as input parameters. From these parameters, TimesTen performs the following:

  1. Evaluates the SQL query and creates an appropriate table, if not already created, with the provided table name where the columns are those named in the SQL query with the same (or mapped) data types as those in the Oracle Database tables from which the resulting data is retrieved.

  2. Loads the results of the SQL query as executed on the Oracle database into this table. The call returns a single number indicating the number of rows loaded. Any subsequent calls to this command append retrieved rows to the table.

Grade:

See the command in "ttIsql" in the Oracle Times Ten In-Memory Database Reference for full details on syntax, requirements, restrictions, and required privileges.

The following example connects providing the DSN, user name, password for the user on TimesTen, and the password for the same user name on the Oracle database. Then, it executes the command to evaluate the statement. The table is created on Times Ten with the same column names and data types as the columns and data types of the retrieved rows. Then, the table is populated with the result set from the Oracle database over two parallel threads.

ttisql -connstr "DSN = cachedb1_1122; UID = oratt; PWD = timesten; OraclePWD = oracle" Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql connect -connstr "DSN = mydb; UID = oratt; PWD = timesten; OraclePWD = oracle"; Connection successful: DSN = mydb; UID = oratt; DataStore = / timesten / install / info / DemoDataStore / mydb; DatabaseCharacterSet = US7ASCII; ConnectionCharacterSet = US7ASCII; DRIVER = / timesten / install / lib / libtten.so; PermSize = 40; TempSize = 32; TypeMode = 0; OracleNetServiceName = inst1; (Default setting AutoCommit = 1) Command> createandloadfromoraquery employees 2 SELECT * FROM hr.employees; Mapping query to this table: CREATE TABLE "ORATT". "EMPLOYEES" ("EMPLOYEE_ID" number (6,0) NOT NULL, "FIRST_NAME" varchar2 (20 byte), "LAST_NAME" varchar2 (25 byte) NOT NULL, "EMAIL "varchar2 (25 byte) NOT NULL," PHONE_NUMBER "varchar2 (20 byte)," HIRE_DATE "date NOT NULL," JOB_ID "varchar2 (10 byte) NOT NULL," SALARY "number (8,2)," COMMISSION_PCT "number (2,2), "MANAGER_ID" number (6,0), "DEPARTMENT_ID" number (4,0)) Table employees created 107 rows loaded from oracle.

Execute the command to show the new table:

Grade:

In this example, the table owner is not specified, so it defaults to the current user. In this example, the current user is.
Command> DESCRIBE employees; Table ORATT.EMPLOYEES: Columns: EMPLOYEE_ID NUMBER (6) NOT NULL FIRST_NAME VARCHAR2 (20) INLINE LAST_NAME VARCHAR2 (25) INLINE NOT NULL EMAIL VARCHAR2 (25) INLINE NOT NULL PHONE_NUMBER VARCHAR2 ) INLINE NOT NULL SALARY NUMBER (8.2) COMMISSION_PCT NUMBER (2.2) MANAGER_ID NUMBER (6) DEPARTMENT_ID NUMBER (4) 1 table found. (primary key columns are indicated with *) Command> SELECT * FROM employees; <114, Den, Raphaely, DRAPHEAL, 515.127.4561, 2002-12-07 00:00:00, PU_MAN, 11000, , 100, 30> <115, Alexander, Khoo, AKHOO, 515.127.4562, 2003 -05-18 00:00:00, PU_CLERK, 3100, , 114, 30>… <205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00, AC_MGR, 12008 , , 101, 110> <206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00, AC_ACCOUNT, 8300, , 205, 110> 107 rows found.

The following example uses the command to create the table on TimesTen and populate it in parallel over four threads with data from the table on the Oracle database, where is less than 200.

Command> createandloadfromoraquery emp 4 SELECT * FROM hr.employees WHERE employee_id <200; Mapping query to this table: CREATE TABLE "ORATT". "EMP" ("EMPLOYEE_ID" number (6,0) NOT NULL, "FIRST_NAME" varchar2 (20 byte), "LAST_NAME" varchar2 (25 byte) NOT NULL, "EMAIL "varchar2 (25 byte) NOT NULL," PHONE_NUMBER "varchar2 (20 byte)," HIRE_DATE "date NOT NULL," JOB_ID "varchar2 (10 byte) NOT NULL," SALARY "number (8,2)," COMMISSION_PCT "number (2,2), "MANAGER_ID" number (6,0), "DEPARTMENT_ID" number (4,0)) Table emp created 100 rows loaded from oracle.

Then, the following retrieves all employees whose id is> 200 and the result set is appended to the existing table in TimesTen. A warning tells you that the table already exists and that 6 rows were added to it.

Command> createandloadfromoraquery emp 4 SELECT * FROM hr.employees WHERE employee_id> 200; Warning 2207: Table ORATT.EMP already exists 6 rows loaded from oracle.

A parallel load operation may take a long time to execute and you may want to cancel the operation. For more information on canceling a parallel load operation, see "Cancel a parallel load operation".

Use TimesTen built-in procedures to recommend a table and load SQL query results

While the command automatically performs all of the tasks for creating the TimesTen table and loading the result set from the Oracle database into it, the following two built-in procedures separate the same functionality into the following two steps:

  1. The built-in procedure evaluates the SQL query and generates the SQL statement that you can choose to execute. In order to execute this statement, the user should have all required privileges to execute the query on the Oracle database. This enables you to view the table structure without execution. However, it does require you to execute the recommended statement yourself.

  2. The built-in procedure executes the SQL query on the back-end Oracle database and then loads the result set into the Times Ten table. It requires the TimesTen table name where the results are loaded, the Oracle Database SQL statement to obtain the required rows, and the number of parallel threads that you would like to be used in parallel when loading the table with this result set.

    The call returns a single number indicating the number of rows loaded. Any subsequent calls append the retrieved rows to the table.

The following example connects providing the DSN, user name, password for the user on TimesTen, the password for a user with the same name on the Oracle database, and the for the Oracle database instance. Then, it calls the built-in procedure to evaluate the statement and return a recommended statement for the table. Finally, the example calls the built-in procedure to load the table with the result set from the Oracle database. The load is performed in parallel over four threads, which is the default.

Grade:

If is set to off, then the user must either commit or rollback manually after loading the table.
$ ttisql "DSN = mydb; uid = oratt; pwd = timesten; OraclePwd = oracle; OracleNetServiceName = inst1" Copyright (c) 1996-2013, Oracle. All rights reserved. Type? or "help" for help, type "exit" to quit ttIsql. connect "DSN = mydb; uid = oratt; pwd = timesten; OraclePwd = oracle; OracleNetServiceName = inst1"; Connection successful: DSN = mydb; UID = oratt; DataStore = / timesten / install / info / DemoDataStore / mydb; DatabaseCharacterSet = US7ASCII; ConnectionCharacterSet = US7ASCII; DRIVER = / timesten / install / lib / libtten.so; PermSize = 40; TempSize = 32; TypeMode = 0; OracleNetServiceName = inst1; (Default setting AutoCommit = 1) Command> call ttTableSchemaFromOraQueryGet ('hr', 'employees', 'SELECT * FROM hr.employees'); 1 row found. Command> CALL ttLoadFromOracle ('HR', 'EMPLOYEES', 'SELECT * FROM HR.EMPLOYEES'); <107> 1 row found. Command> SELECT * FROM hr.employees; <100, Steven, King, SKING, 515.123.4567, 2003-06-17 00:00:00, AD_PRES, 24000, , , 90> <101, Neena, Kochhar, NKOCHHAR, 515.123.4568 , 2005-09-21 00:00:00, AD_VP, 17000, , 100, 90> ... <205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00 , AC_MGR, 12008, , 101, 110> <206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00, AC_ACCOUNT, 8300, , 205, 110> 107 rows found.

The following example creates a table on the Oracle database, where is a column with a constraints and is a column with a constraint.

SQL> CREATE TABLE employees (employee_id NUMBER (6) PRIMARY KEY, first_name VARCHAR2 (20), last_name VARCHAR2 (25) NOT NULL, email VARCHAR2 (25) NOT NULL UNIQUE, phone_number VARCHAR2 (20), hire_date DATE NOT NULL, job_id VARCHAR2 (10) NOT NULL, salary NUMBER (8,2), commission_pct NUMBER (2,2), manager_id NUMBER (6), department_id NUMBER (4)); Table created.

Then, the following built-in procedure evaluates the SQL query and generates a SQL statement. Note that in the suggested SQL statement the and constraints are not carried over from the Oracle database. Nullability constraints are carried over from the Oracle database. This also applies to the command.

Command> call ttTableSchemaFromOraQueryGet ('oratt', 'employees', 'select * from oratt.employees'); 1 row found.

A parallel load operation may take a long time to execute and you may want to cancel the operation. For more information on canceling a parallel load operation, see "Cancel a parallel load operation".

Cancel a parallel load operation

You can cancel and cleanly stop all threads that are performing a parallel load operation with either the ODBC function or by pressing Ctrl-C in the utility. For more information, see "Supported ODBC functions" in the Oracle TimesTen In-Memory Database C Developer's Guide and "Canceling ODBC functions" in this book.

A parallel load operation periodically commits, so any successful operations are not rolled back. When you issue the cancel command, Times Ten performs the cancel operation:

  • Before insert threads are spawned.

  • After an insert batch commit (every 256 rows)

  • After the main thread completes a fetch from the Oracle database.

To retry a canceled parallel load operation, delete previously inserted rows from the TimesTen database to avoid duplicate rows.

Viewing and changing query optimizer plans

The following sections describe how to view the query optimizer plans, commands in the SQL command cache, or query plans for commands in the SQL command cache:

Using the showplan command

The built-in command is used to display the query optimizer plans used by the TimesTen Data Manager for executing queries. In addition, contains built-in query optimizer hint commands for altering the query optimizer plan. By using the command in conjunction with the commands summarized below, the optimum execution plan can be designed. For detailed information on the TimesTen query optimizer see "The TimesTen Query Optimizer".

  • - Displays the current optimizer hint settings and join order.

  • - Sets the join order.

  • - Sets the index hint.

  • - Enables or disables the use of hash indexes.

  • - Enables or disables merge joins.

  • - Enables or disables nested loop joins.

  • - Enables or disables serial scans.

  • - Enables or disables the use of temporary hash indexes.

  • - Enables or disables the use of an intermediate results table.

  • - Enables or disables the use of temporary range indexes.

  • - Enables or disables the use of range indexes.

  • - Enables or disables the use of rowid scans.

  • - Enables or disables the use of table locks.

  • - Clears the join order.

  • - Clears the index hint.

When using the command and the query optimizer hint commands the autocommit feature must be turned off. Use the command to turn off autocommit.

The example below shows how these commands can be used to change the query optimizer execution plan.

Command> CREATE TABLE T1 (A NUMBER); Command> CREATE TABLE T2 (B NUMBER); Command> CREATE TABLE T3 (C NUMBER); Command> INSERT INTO T1 VALUES (3); 1 row inserted. Command> INSERT INTO T2 VALUES (3); 1 row inserted. Command> INSERT INTO T3 VALUES (3); 1 row inserted. Command> INSERT INTO T1 VALUES (4); 1 row inserted. Command> INSERT INTO T2 VALUES (5); 1 row inserted. Command> INSERT INTO T3 VALUES (6); 1 row inserted. Command> autocommit 0; Command> showplan; Command> SELECT * FROM T1, T2, T3 WHERE A = B AND B = C AND A = B; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: TblLkSerialScan TBLNAME: T2 IXNAME: INDEXED CONDITION: NOT INDEXED: STEP: 2 LEVEL: 3 OPERATION: TblLkSerialScan TBLNAME: T3 IXNAME: INDEXED CONDITION: NOT INDEXED: T2.B = T3.C STEP: 3 LEVEL: 2 OPERATION: NestedLoop TBLNAME: IXNAME: INDEXED CONDITION: NOT INDEXED: STEP: 4 LEVEL: 2 OPERATION: TblLkSerialScan TBLNAME: T1 IXNAME: INDEXED CONDITION: NOT INDEXED: T1.A = T2.B AND T1.A = T2.B STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: < NULL> IXNAME: INDEXED CONDITION: NOT INDEXED: <3, 3, 3> 1 row found. Command> trytbllocks 0; Command> tryserial 0; Command> SELECT * FROM T1, T2, T3 WHERE A = B AND B = C AND A = B; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: TmpRangeScan TBLNAME: T2 IXNAME: INDEXED CONDITION: NOT INDEXED: STEP: 2 LEVEL: 3 OPERATION: RowLkSerialScan TBLNAME: T3 IXNAME: INDEXED CONDITION: NOT INDEXED: T2.B = T3.C STEP: 3 LEVEL: 2 OPERATION: NestedLoop TBLNAME: IXNAME: INDEXED CONDITION: NOT INDEXED: STEP: 4 LEVEL: 2 OPERATION: RowLkSerialScan TBLNAME: T1 IXNAME: INDEXED CONDITION: NOT INDEXED: T1.A = T2.B AND T1.A = T2.B STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: < NULL> IX NAME: INDEXED CONDITION: NOT INDEXED: <3, 3, 3> 1 row found.

In this example a query against three tables is executed and the query optimizer plan is displayed. The first version of the query simply uses the query optimizer's default execution plan. However, in the second version the and built-in hint commands have been used to alter the query optimizer's plan. Instead of using serial scans and nested loop joins the second version of the query uses temporary index scans, serial scans and nested loops.

In this way the command in conjunction with 's built-in query optimizer hint commands can be used to quickly determine which execution plan should be used to meet application requirements.

Viewing commands and explain plans from the SQL command cache

The following sections describe how to view commands and their explain plans:

View commands in the SQL command cache

The command invokes the built-in procedure to display the contents of the Times Ten SQL command cache. See "Displaying commands stored in the SQL command cache" for full details on this procedure.

If you execute the command without parameters, the full SQL command cache contents are displayed. Identical to the built-in procedure, you can provide a command ID to specify a specific command to be displayed.

In addition, the command can filter the results so that only those commands that match a particular owner or query text are displayed.

The syntax for the command is as follows: