ttIsql
ttIsql to run SQL statements and call TimesTen built-in procedures from this utility. Additionally, you can run SQL interactively from the command line and call a TimesTen built-in procedure with call procedure-name.
The ttIsql command attempts to cancel an ongoing ODBC function when the user presses Ctrl-C.
On UNIX and Linux systems, this utility is supported for TimesTen Data Manager DSNs. Use ttIsqlCS for client/server DSNs.
The ttIsql utility starts with AUTOCOMMIT turned on, even when running a script. You can turn AUTOCOMMIT off and back on as necessary.
For a detailed description on running SQL from ttIsql, use the -helpfull option.
For more details on the ttIsql utility, see the chapter Using the ttIsql Utility in Oracle TimesTen In-Memory Database Operations
Guide.
Required Privilege
This utility requires no privileges.
Usage in TimesTen Scaleout and TimesTen Classic
This utility is supported in both TimesTen Classic and TimesTen Scaleout.
Syntax
ttIsql {-h | -help | -? | -helpcmds | - helpfull}
ttIsql {-V | -version}
ttIsql [-f inputFile] [-v verbosity] [-e commands | sql_statement]
[-interactive] [-N ncharEncoding] [-wait] {-connStr connection_string | DSN}Options
ttIsql has the options:
| Option | Description |
|---|---|
|
|
An ODBC connection string that specifies a database location, driver, and optionally other connection attribute settings. |
|
|
Specifies an ODBC data source name of the database to be connected. |
|
|
Specifies a semicolon separated list of |
|
|
Read SQL statements from |
|
|
Prints a usage message and exits. |
|
|
Prints a short list of the interactive commands. |
|
|
Prints a full description of the interactive commands. |
|
|
Forces interactive mode. This is useful when running from an |
|
|
Specifies the character encoding method for Valid values are If no value is specified, TimesTen uses the system's native language characters. |
|
|
Prints the release number of |
|
|
Specifies the verbosity level. One of:
|
|
- |
Waits until successful connect. |
Commands
Also see the list of ttIsql Set/Show Attributes.
Boolean commands can accept the values "ON" and "OFF" or "1" and "0".
ttIsql has the commands:
| Command | Description |
|---|---|
|
|
Gets input from a user and The prompt is displayed before waiting for input, if specified without the |
|
|
Lists, in a single column, the names of all the PL/SQL functions that match the given pattern selected from If passthrough is enabled, lists PL/SQL functions matching the pattern in the Oracle database. See the |
|
|
Describes the indexes that it finds on the tables that match the input pattern selected from If passthrough is enabled, lists indexes on tables matching the pattern in the Oracle database. See the |
|
|
Lists, in a single column, the names of all the PL/SQL packages that match the given pattern selected from If passthrough is enabled, lists PL/SQL packages matching the pattern in the Oracle database. See the |
|
|
Lists, in a single column, the names of all the PL/SQL procedures that match the given pattern selected from If passthrough is enabled, lists PL/SQL procedures matching the pattern in the Oracle database. See the |
|
|
Lists, in a single column, the names of all the sequences that match the given pattern selected from If passthrough is enabled, lists sequences on tables matching the pattern in the Oracle database. See the |
|
|
Lists, in a single column, the names of all synonyms that match the given pattern. When a pattern is missing, the pattern defaults to " If passthrough is enabled, lists synonyms on tables matching the pattern in the Oracle database. See the |
|
|
Lists, in a single column, the names of all the tables that match the given pattern selected from If passthrough is enabled, lists tables matching the pattern in the Oracle database. See the |
|
|
Lists, in a single column, the names of all the views that match the specified pattern selected from If passthrough is enabled, lists views matching the pattern in the Oracle database. |
|
|
Lists, in a single column, the names of all the TimesTen built-in procedures that match the given pattern. When the pattern is missing, the pattern defaults to " See the |
|
|
Exits |
|
|
Reports information on cache groups defined in the currently connected data source, including the state of any terminated databases that contain autorefresh cache groups. If the optional argument is not specified then information on all cache groups in the current data source is reported. |
|
|
Generates an Oracle SQL*Plus compatible script for the installation or uninstallation of Oracle database objects associated with a readonly cache group, a user managed cache group with incremental autorefresh or an AWT cache group. If If If the optional |
|
|
Changes the current directory. This is the equivalent of the After changing to the directory Subsequent commands that rely on relative paths will use this directory as the starting point. Examples of affected commands are |
|
|
Clears the history buffer. Also see |
|
|
Sets the client timeout value in seconds for the current connection. If no value is specified, displays the current value. See Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide for information about the relationship between the client timeout, SQL timeout, and PL/SQL timeout. |
|
|
Closes the prepared command identified by connection name Use |
|
|
Displays the contents of the TimesTen SQL command cache. Specify the If passthrough is enabled, the command ID is not passed through to the Oracle database. |
|
|
Commits the current transaction (durably if |
|
|
Commits the current transaction durably. |
|
|
Compacts the database. |
|
|
Compares the values of two variables and reports if they are different. The first difference is reported. |
|
|
Connects to the database with the specified ODBC If no password is supplied in this format, If no user is given, If When |
|
|
Takes a table name, the number of threads for parallel load and an Oracle Creates the table in TimesTen if the table does not exist. Then, loads the table with the query result from the Oracle database. If the command creates the table, the table column names and types are derived from the query result. Notes:
Required Privileges: Requires |
|
|
Defines a string substitution alias. If no value is provided, You must |
|
|
List information on tables, synonyms, views, materialized views, sequences, cache groups, PL/SQL functions, PL/SQL procedures, PL/SQL packages and TimesTen built-in procedures in that order when the argument is Describes the parameters and results columns when the argument is If If If the table or materialized view being described is in a TimesTen Scaleout database, this command reports the distribution scheme. When describing cache groups, reports information on cache groups defined in the currently connected data source, including the state of any terminated databases that contain autorefresh cache groups. If the command is describing a sequence in a TimesTen Scaleout database, displays the batch field. The command alias is Use |
|
|
Disconnects from the database. If |
|
|
Prints size information in KB, MB, GB or TB. For TimesTen Scaleout, provides the size of the element. The default is MB. The output indicates the unit returned. |
|
|
Echoes the specified messages, terminated by the end of the line. A semicolon is not required to end the line. Messages are not echoed if verbosity is set to 0. |
|
|
You can use the If TimesTen does not find an exact file match for the specified You can edit a SQL statement that is stored in the history list of the current If you run the You can only use one parameter at a time. The You can specify the default editor by defining the Command> DEFINE _EDITOR=vi If you do not define the For more details, see Using the ttIsql Edit Command in Oracle TimesTen In-Memory Database Operations Guide. |
|
|
Runs the prepared command n connection The If no argument is supplied, runs the most recent command. Use |
|
|
Runs and fetches all results from prepared command Use |
|
|
Explains the plan for the specified SQL statement, including prepared A digit that is not qualified with the If passthrough is enabled, the command ID is not passed through to the Oracle database. |
|
|
Fetches all results from prepared command If Use |
|
|
Fetches up to If Use |
|
|
Fetches one result from prepared command If Use |
|
|
Frees prepared command If no command is specified, frees the most recent command. Use |
|
|
Lists, in a single column, the names of PL/SQL functions owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to If passthrough is enabled, lists PL/SQL functions matching the pattern in the Oracle database. See the |
|
|
Performs that specified statement on a grid database. |
|
|
Formats the contents of the If the This command is not supported in TimesTen Classic. |
|
|
Prints brief or detailed help information for commands. If specific commands are given as arguments then detailed help for each command is printed. If you do not know the exact name of a command, try typing just a few characters that may be part of the command name. If If If If no argument is given then brief help information for all commands is printed. |
|
|
Lists previously run commands. The The output of this command omits consecutive duplicate commands. Use the Use the Use the The history list stores up to 100 of the most recently run commands. See the |
|
|
Runs an operating system command. The command is run in the same console as This command sets the environment variable The value of the variable is the connection string of the current connection. To see the exit status of the command, use the |
|
|
The |
|
|
Describes the indexes that it finds on the tables owned by the current user that match the input pattern. When a name pattern is missing, the pattern defaults to If passthrough is enabled, lists indexes on tables matching the pattern in the Oracle database. See the |
|
|
Formats the contents of the If the |
|
|
Lists, in a single column, the names of PL/SQL packages owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to If passthrough is enabled, lists PL/SQL packages matching the pattern in the Oracle database. See the |
|
|
Prepares the specified SQL statement. If the The Use |
|
|
Prints the value of the specified bind variable or all variables if no variable is specified. If the variable is a |
|
|
Lists, in a single column, the names of PL/SQL procedures owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to If passthrough is enabled, lists PL/SQL procedures matching the pattern in the Oracle database. See the |
|
|
Exits |
|
|
Specifies that the message on the line should be treated as a comment. When |
|
|
Reports information on replication schemes defined in the currently connected data source. This information describes all elements associated with the replication schemes. If the optional argument is not specified then information on all replication schemes defined in the current data source is reported. |
|
|
Disables( If the connection retry feature is enabled then connection attempts to a data source that initially fail due to a temporary situation are retried until the connection attempt succeeds. For example, if data source recovery is in progress when attempting to connect, the connection retry feature causes the connect command to continue to attempt a connection until the recovery process is complete. If the optional argument is omitted then the connection retry feature is enabled by default. |
|
|
Rolls back the current transaction. |
|
|
The
Only variables that are character based ( |
|
|
Reads and runs SQL commands from The When you specify See Example Parameters of Command String Substitution for a description of |
|
|
Writes the history buffer to the specified Consecutive duplicate commands are omitted. Use the Use the Use See the |
|
|
Lists, in a single column, the names of sequences owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to If passthrough is enabled, lists sequences on tables matching the pattern in the Oracle database. See the |
|
|
Sets the specified If no value is specified, displays the current value of the specified attribute. For a description of accepted attributes, see Set/Show Attributes. |
|
|
Specifies the join order for the optimizer. |
|
|
Sets the index hint for the query optimizer. |
|
|
Sets the value of a scalar bind variable or an element of an array bind variable. For example: For more information, see Declaring and Setting Bind Variables in Oracle TimesTen In-Memory Database Operations Guide. |
|
|
Displays the value for the specified For a description of accepted attributes, see Set/Show Attributes. |
|
|
Enables or disables the storing of join orders.
Call the |
|
|
Suspends operation for |
|
|
Writes a copy of the terminal output to the file If you do not provide an extension to
When you specify the value If you specify a spool command while one is running, the active spool is closed and a new files is opened. |
|
|
Prints results of an ODBC call to |
|
|
Prints results of an ODBC call to |
|
|
Prints results of an ODBC call to |
|
|
Prints results of a call to |
|
|
Clears statistics for specified table (or all tables if no table is specified). |
|
|
Estimates statistics for specified table (or all tables if no table is specified). If you estimate statistics with an empty table list, statistics on system tables are updated also, if you have privileges to update the system tables. |
|
|
Updates statistics for specified table (or all tables if no table is specified). If |
|
|
Lists, in a single column, the names of synonyms owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to If passthrough to an Oracle database is enabled, lists synonyms on tables matching the pattern in the Oracle database. See the |
|
|
Lists, in a single column, the names of tables owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to If passthrough to an Oracle database is enabled, lists tables matching the pattern in the Oracle database. See the |
|
|
For each table that matches the pattern, lists the contents of the See the |
|
|
Undefines a string substitution alias. |
|
|
Clears join order advice to optimizer. |
|
|
Clears the index hint for the query optimizer. |
|
|
Displays the list of current connections and their IDs. If connid is specified, switches to the given connection ID. To use the name of the first connection, you can specify If See the |
|
The syntax for binding multiple values to an array using the variable array_name
'[' array_size ']'
|
Declares a bind variable that can be referenced in a statement or displays the definition of the variable if the type is missing. Type can be one of the following: Assigns a value to a single variable or multiple values if the data type is an array. You can assign a value later with the For more information, see Declaring and Setting Bind Variables in Oracle TimesTen In-Memory Database Operations Guide. |
|
|
Reports version information. |
|
|
Lists, in a single column, the names of views owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to " If passthrough to an Oracle database is enabled, lists views matching the pattern in the Oracle database. See the |
|
|
Runs the given statement once a second until the query returns the expected result or a timeout occurs. The query must have only one column and must return exactly one row. Any errors in the query terminate the loop. |
|
|
Similar to the Runs the given statement once a second until the query returns the expected result or a timeout occurs. The |
|
|
Provide direction on how to handle errors when in |
|
|
Deletes a persistent XLA bookmark. If a bookmark to delete is not specified then the status of all current XLA bookmarks is reported. Also see ttXlaDeleteBookmark in Oracle TimesTen In-Memory Database C Developer's Guide. Requires |
Syntax for the IF-THEN-ELSE Command Construct
This section provides the syntax for the IF-THEN-ELSE construct. For more details on using the IF-THEN-ELSE command construct, see Conditional Control with the IF-THEN-ELSE Command Construct in the Oracle TimesTen In-Memory Database Operations
Guide.
IF [NOT]
{ Literal1 | :BindVariable1 }
{ = | IN }
{ Literal2 | :BindVariable2 | SelectStatement }
THEN "ThenCommands"
[ ELSE "ElseCommands" ] ;
The ttIsql IF-THEN-ELSE command has the parameters:
| Parameter | Description |
|---|---|
|
|
The The |
|
|
Using |
|
|
A value that can be part of a comparison. |
|
|
A bind variable is equivalent to a parameter. You can use the |
|
= | |
You can use the |
|
|
A provided The |
|
|
All commands in the |
Restrictions for the IF-THEN-ELSE construct are as follows:
-
You cannot compare variables of the LOB data type.
-
The values are compared case-sensitive with
strcmp. A character padded value might not match aVARCHAR2because of the padding.
Syntax for the WHENEVER SQLERROR Command
Run the WHENEVER SQLERROR command to prescribe what to do when a SQL error occurs. For more details and examples on how to use the WHENEVER SQLERROR command, see Error Recovery with WHENEVER SQLERROR command in the Oracle TimesTen In-Memory Database Operations
Guide.
WHENEVER SQLERROR { ExitClause | ContinueClause | SUPPRESS |
SLEEP Number | ExecuteClause }
When you specify EXIT, always exit ttIsql if an error occurs. ExitClause is as follows:
EXIT [ SUCCESS | FAILURE | WARNING | Number | :BindVariable ]
[ COMMIT | COMMIT ALL | ROLLBACK ]
When you specify CONTINUE, ttIsql continues to the next command, even if an error occurs. ContinueClause is as follows:
CONTINUE [ COMMIT | COMMIT ALL | ROLLBACK | NONE ]
Run specified commands before continuing. ExecuteClause is as follows:
EXECUTE "Cmd1;Cmd2;...;"
The WHENEVER SQLERROR command options are as follows:
-
EXIT: Always exitttIsqlif an error occurs. Specify what is performed beforettIsqlexits with one of the following.SUCCESSis the default option forEXIT.-
SUCCESSorFAILUREorWARNING: ReturnSUCCESS(value 0),FAILURE(value 1), orWARNING(value 2) to the operating system afterttIsqlexits for any SQL error. -
Number: Specify a number from 0 to 255 that is returned to the operating system as a return code. OncettIsqlexits, you can retrieve the error return code with the appropriate operating system commands. For example, useecho $statusin the C shell (csh) orecho $?in the Bourne shell (sh) to display the return code.The return code can be retrieved and processed within batch command files to programmatically detect and respond to unexpected events.
-
:BindVariable: Returns the value in a bind variable that was previously created inttIsqlwith thevariablecommand. The value of the variable at the time of the error is returned to the operating system in the same manner as theNumberoption.Note:
The bind variable used within the
WHENEVERSQLERRORcommand cannot be defined as aLOB,REFCURSOR, or any array data type.
In addition, you can specify whether to commit or rollback all changes before exiting
ttIsql.-
COMMIT: Runs aCOMMITand saves changes only in the current connection before exiting. The other connections exit with the normal disconnect processing, which rolls back any uncommitted changes. -
COMMIT ALL: Runs aCOMMITand saves changes in all connections before exiting. -
ROLLBACK: Before exiting, runs aROLLBACKand abandons changes in the current connection and, by default, in all other connections. The other connections exit with the normal disconnect processing, which automatically rolls back any uncommitted changes.
-
-
CONTINUE: Do not exit if an error occurs. The SQL error is displayed, but the error does not causettIsqlto exit. The following options enable you to specify what is done before continuing to the nextttIsqlcommand:-
NONE: This is the default. Take no action before continuing. -
COMMIT: Runs aCOMMITand saves changes in the current connection before continuing. -
COMMIT ALL: Runs aCOMMITand saves changes in all connections before continuing. -
ROLLBACK: Before continuing, runs aROLLBACKand abandons changes in the current connection and, by default, in all other connections. The other connections exit with the normal disconnect processing, which automatically rolls back any uncommitted changes.
-
-
SUPPRESS: Do not show any error messages and continue. -
SLEEP: Sleep for a specified number of seconds before continuing. -
EXECUTE: Run specified commands before continuing. Each command is separated from the other commands by a semicolon (;). If any command triggers additional errors, those errors may cause additional actions that could potentially result in a looping condition.
Set/Show Attributes
Also see the list of ttIsql Commands. Some commands appear here as attributes of the set command. In that case, you can use them with or without the set command.
Boolean attributes can accept the values "ON" and "OFF" or "1" and "0".
The ttIsql set command has the attributes:
| Attribute | Description |
|---|---|
|
|
With show command only. Displays the setting of all the |
|
|
Turns |
|
|
Turns |
|
|
Turns the If no argument is specified, the current value of The initial value of When the value is on ( You can also enable this attribute without specifying the |
|
|
Prints the connection string returned from the driver from the |
|
|
Sets the character used to prefix substitution variables to
Default value for |
|
|
Enables or disables dynamic load of data from an Oracle database to a TimesTen dynamic cache group. By default, dynamic load of data from an Oracle database is enabled. |
|
|
With the If |
|
|
Turns the If |
|
|
With the |
|
|
Controls the display of status messages after running the statement. When |
|
|
Sets isolation level. If no argument is supplied, displays the current value. You can also enable this attribute without specifying the |
|
|
Specifies the offset into the LOB that The behavior is the same as |
|
|
Reports or controls the maximum number of characters for The default value is The command setting is valid for all connections in a session. |
|
|
Specifies the size of the chunk that |
|
|
Reports or enables handling of multiple connections.By default, If the argument If no value is supplied, the command displays the value of the You can also enable this attribute without specifying the |
|
|
Specifies the character encoding method for
If no value is specified, TimesTen uses the system's native language characters. You can also enable this attribute without specifying the |
|
|
Sets or shows the string to be displayed when the The option does not affect the SQL user, only the display of |
|
|
Enables or disables First Row Optimization. If the optional argument is omitted, First Row Optimization is enabled. You can also enable this attribute without specifying the |
|
|
Prints the current optimizer flag settings and join order. This attribute cannot be used with the |
|
|
Sets the cache passthrough level for the current transaction. Because
If no optional argument is supplied, the current setting is displayed. After the transaction, the passthrough value is reset to the value defined in the connection string or in the DSN or the default setting if no value was supplied to either. You can also enable this attribute without specifying the set command. Note: Some Oracle objects may not be described by |
|
|
Sets the prefetch count size for the current connection. If the optional argument is omitted, the current prefetch count size is reported. Setting the prefetch count size can improve result set fetch performance. The When you set the prefetch count to You can also enable this attribute without specifying the |
|
|
Replaces the To specify a prompt with spaces, you must quote the string. The leading and trailing quotes are removed. A prompt can have a string format specifier ( |
|
|
With the With the Specify a value in seconds that indicates the number of seconds that a query can run before TimesTen writes a warning to the daemon log. |
|
|
Controls the row delimiters in result sets. When The default is |
|
|
With the The default is
This command is not supported in |
|
|
Enable or disable printing of the current wall clock time. |
|
|
Enables ( You can also enable this attribute without specifying the |
|
|
Specifies the number of seconds to wait for a SQL statement to run before returning to the application for all subsequent calls. If no time or The value of You can also enable this attribute without specifying the set command. See Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide for information about the relationship between the client timeout, SQL timeout, and PL/SQL timeout. |
|
|
Enables or disables printing of query timing. You can also enable this attribute without specifying the |
|
|
Enables or disables use of hash indexes by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables materialization by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables use of merge joins by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables use of nested loop joins by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables |
|
|
Enables or disables use of row-level locking by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables use of serial scans by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables use of temporary hashes by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables use of table-level locking by the optimizer at the transaction level. You can also set this attribute without specifying the |
|
|
Enables or disables use of temporary tables by the optimizer at the transaction level. You can also enable this attribute without specifying the set command. |
|
|
Enables or disables use of temporary range indexes by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Enables or disables use of range indexes by the optimizer at the transaction level. You can also enable this attribute without specifying the |
|
|
Changes the verbosity level. The verbosity level argument can be an integer value of 0, 1, 2, 3 or 4. If the optional argument is omitted then the current verbosity level is reported. You can also enable this attribute without specifying the |
|
|
Sets or displays the current value of the vertical setting. The default value is If statement is supplied, the command temporarily turns vertical on for the given statement. This form is only useful when the vertical flag is off. The You can also enable this attribute without specifying the |
Comment Syntax
The types of comment markers are:
-- [comment_text] /* [comment_text] */
The C-style comments, delineated by "/*" at the beginning and "*/" at the end, can span multiple lines.
The comments delimited by the
-
character should not span multiple lines. If a comment marker is encountered while processing a line, ttIsql ignores the remainder of the line.
'--' at the beginning of a line is considered a SQL comment. The line is considered a comment and no part of the line is included in the processing of the SQL statement. A line that begins with '--+' is interpreted as a segment of a SQL statement.
The comment markers can work in the middle of a line.
Example:
monitor; /*this is a comment after a ttIsql command*/
Command Shortcuts
By default, ttIsql supports keystroke shortcuts when entering commands. To turn this feature off, use:
Command> set editline=0;
The ttIsql keystroke shortcuts are:
| Keystroke | Action |
|---|---|
|
Left Arrow |
Moves the insertion point left (back). |
|
Right Arrow |
Moves the insertion point right (forward). |
|
Up Arrow |
Scroll to the command before the one being displayed. Places the cursor at the end of the line. If the command being added to the history is identical to the most recently added command, it is skipped. |
|
Up Arrow < |
Scrolls to the PL/SQL block before the one being displayed. |
|
Down Arrow |
Scrolls to a more recent command history item and puts the cursor at the end of the line. If the command being added to the history is identical to the most recently added command, it is skipped. |
|
Down Arrow < |
Scrolls to the next PL/SQL block after the one being displayed. |
|
|
Moves the insertion point to the beginning of the line. |
|
|
Moves the insertion point to the end of the line. |
|
|
"Kill" - Saves and erases the characters on the command line from the current position to the end of the line. |
|
|
"Yank"- Restores the characters previously saved and inserts them at the current insertion point. |
|
|
Forward character - move forward one character. (See Right Arrow.) |
|
|
Backward character - moved back one character. (See Left Arrow.) |
|
|
Previous history. (See Up Arrow.) |
|
|
Next history. (See Down Arrow.) |
Parameters
With dynamic parameters, you are prompted for input for each parameter on a separate line. Values for parameters are specified the same way literals are specified in SQL.
SQL_TIMESTAMP columns can be added using dynamic parameters. (For example, values like '1998-09-08 12:1212').
Parameter values must be terminated with a semicolon character.
The possible types of values that can be entered are:
-
Numeric literals. Example:
1234.5 -
Time, date or timestamp literals within single quotation marks. Examples:
'12:30:00''2000-10-29''2000-10-29 12:30:00''2000-10-29 12:30:00.123456'
-
Unicode string literals within single quotation marks preceded by
'N'. Example:N'abc' -
A NULL value. Example:
NULL -
The '*' character that indicates that the parameter input process should be stopped. Example:
* -
The '?' character prints the parameter input help information. Example:
?
Examples
Example Parameters of Command String Substitution
Command> select * from dual where :a > 100 and :b < 100; 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 'A' (NUMBER) > 110 Enter Parameter 2 'B' (NUMBER) > 99 < X > 1 row found. Command> var a number; exec :a := 110; PL/SQL procedure successfully completed. Command> print a A : 110 Command> var b number; exec :b := 99; PL/SQL procedure successfully completed. Command> select * from dual where :a > 100 and :b < 100; < X > 1 row found. Command> print A : 110 B : 99 Command> select * from dual where :a > 100 and :b < 100 and :c > 0; Enter Parameter 3 'C' (NUMBER) > 1 < X > 1 row found.
Default Options
You can set the default command-line options by exporting an environment variable called TTISQL. The value of the TTISQL environment variable is a string with the same syntax requirements as the TTISQL command line. If the same option is present in the TTISQL environment variable and the command line then the command line version always takes precedence.
Examples
Execute commands from ttIsql.inp.
% ttIsql -f ttIsql.inp
Enable all output. Connect to DSN RunData and create the database if it does not exist.
% ttIsql -v 4 -connStr "DSN=RunData;AutoCreate=1"
Print the interactive commands.
% ttIsql -helpcmds
Print the full help text.
% ttIsql -helpfull
Display the setting for all ttIsql set/show attributes:
Command> show all; Connection independent attribute values: autoprint = 0 (OFF) columnlabels = 0 (OFF) define = 0 (OFF) echo 1 (ON) FEEDBACK ON multipleconnections =0 (OFF) ncharencoding = LOCALE (US7ASCII) prompt = 'COMMAND>' timing = 0 (OFF) verbosity = 2 vertrical = 0 (OFF) Connection specific attribute values: autocommit = 1 (ON) Client timeout = 0 Connection String DSN=repdb1_1121;UID=timesten; DataStore=/DS/repdb1_1121; DatabaseCharacterSet=AL32UTF8; ConnectionCharacterSet=US7ASCII; DRIVER=/sw/tthome/install/lib/libtten.so; PermSize=20;TempSize=20; No errors. isolation = READ_COMMITTED Prefetch count = 5 Query threshold = 0 seconds (no threshold) Query timeout = 0 seconds (no timeout) serveroutput OFF Current Optimizer Settings: Scan: 1 Hash: 1 Range: 1 TmpHash: 1 TmpTable: 1 NestedLoop: 1 MergeJoin: 1 GenPlan: 0 TblLock: 1 RowLock: 1 Rowid: 1 FirstRow: 1 IndexedOr: 1 PassThrough: 0 BranchAndBound: 1 ForceCompile: 0 CrViewSemCheck: 1 ShowJoinOrder: 0 CrViewSemCheck: 1 UserBoyerMooreStringSearch: 0 DynamicLoadEnable: 1 DynamicLoadErrorMode: 0 NoRemRowIdOpt: 0 Current Join Order: <> Command
Prepare and execute an SQL statement.
% ttIsql -connStr "DSN=RunData" ttIsql (c) 1996-2011, TimesTen, Inc. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. (Default setting AutoCommit=1) Command> prepare 1 SELECT * FROM my_table; exec 1; fetchall;
Example vertical command:
Command> call ttlogholds; < 0, 265352, Checkpoint , DS.ds0 > < 0, 265408, Checkpoint , DS.ds1 > 2 rows found. Command> vertical call ttlogholds; HOLDLFN: 0 HOLDLFO: 265352 TYPE: Checkpoint DESCRIPTION: DS.ds0 HOLDLFN: 0 HOLDLFO: 265408 TYPE: Checkpoint DESCRIPTION: DS.ds1 2 rows found. Command>
To create a new user, use single quotes around the password name for an internal user:
% ttIsql -connStr "DSN=RunData" ttIsql (c) 1996-2000, TimesTen, Inc. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. (Default setting AutoCommit=1) Command> CREATE USER terry IDENDTIFIED BY `secret';
To delete the XLA bookmark mybookmark, use:
% ttIsql -connStr "DSN=RunData" ttIsql (c) 1996-2000, TimesTen, Inc. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. (Default setting AutoCommit=1) Command> xlabookmarkdelete; XLA Bookmark: mybookmark Read Log File: 0 Read Offset: 268288 Purge Log File: 0 Purge Offset: 268288 PID: 2004 In Use: No 1 bookmark found. Command> xlabookmarkdelete mybookmark; Command> xlabookmarkdelete; 0 bookmarks found.
To run a SELECT query until the result "X" is returned or until the query times out at 10 seconds, use:
% ttIsql -connStr "DSN=RunData" ttIsql (c) 1996-2000, TimesTen, Inc. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. (Default setting AutoCommit=1) Command> waitfor X 10 select * from dual;
Example of Managing XLA Bookmarks
You can use the xlabookmarkdelete command to both check the status of the current XLA bookmarks and delete them. This command requires XLA privilege or object ownership.
For example, when running the XLA application, 'xlaSimple', you can check the bookmark status by entering:
Command> xlabookmarkdelete; XLA Bookmark: xlaSimple Read Log File: 0 Read Offset: 630000 Purge Log File: 0 Purge Offset: 629960 PID: 2808 In Use: No 1 bookmark found.
To delete the bookmark xlaSimple, enter:
Command> xlabookmarkdelete xlaSimple;
Example Parameters Using "variable" and "print"
Substitution in ttIsql is modeled after substitution in SQL*Plus. To enable the substitution feature, use set define on or set define substitution_char'. The substitution character when the user specifies 'on' is '&'. It is disabled with 'set define off'. By default, substitution is off. The default is off because the & choice for substitution character conflicts with TimesTen's use of ampersand as the BIT AND operator. When enabled, the alphanumeric identifier following the substitution character is replace by the value assigned to that identifier. When disabled, the expansion is not performed. New definitions can be defined even when substitution is off. You can use the define command to list the definitions ttIsql predefines.
Command> show define define = 0 (OFF) Command> define DEFINE _PID = "9042" (CHAR) DEFINE _O_VERSION = "TimesTen Release 11.2.1.0.0" (CHAR) Command> select '&_O_VERSION' from dual; < &_O_VERSION > 1 row found. Command> set define on SELECT '&_O_VERSION' FROM DUAL; < TimesTen Release 11.2.1.0.0 > 1 row found.
If the value is not defined, ttIsql prompts you for the value. When prompting with only one substitution character specified before the identifier, the identifier is defined only for the life of the one statement. If two substitution characters are used and the value is prompted, it acts as if you have explicitly defined the identifier.
Command> SELECT '&a' FROM DUAL; Enter value for a> hi < hi > 1 row found. Command> define a symbol a is UNDEFINED The command failed. Command> SELECT '&&a' FROM DUAL; Enter value for a> hi there < hi there > 1 row found. Command> define a DEFINE a = "hi there" (CHAR)
Additional definitions are created with the define command:
Command> define tblname = sys.dual define tblname DEFINE tblname = "sys.dual" (CHAR) Command> select * from &tblname; < X > 1 row found.
Arguments to the run command are automatically defined to '&1', '&2', ... when you add them to the run or @ (and @@) commands: Given this script:
CREATE TABLE &1 ( a INT PRIMARY KEY, b CHAR(10) ); INSERT INTO &1 VALUES (1, '&2'); INSERT INTO &1 VALUES (2, '&3');SELECT * FROM &1;
Use the script:
Command> SET DEFINE ON Command> @POPULATE mytable Joe Bob; CREATE TABLE &1 ( a INT PRIMARY KEY, b CHAR(10) ); INSERT INTO &1 VALUES (1, '&2'); 1 row inserted. INSERT INTO &1 VALUES (2, '&3'); 1 row inserted. SELECT * FROM &1; < 1, Joe > < 2, Bob > 2 rows found.
This example uses the variable command. It deletes an employee from the employee table. Declare empid and name as variables with the same data types as employee_id and last_name. Delete the row, returning employee_id and last_name into the variables. Verify that the correct row was deleted.
Command> VARIABLE empid NUMBER(6) NOT NULL; VARIABLE name VARCHAR2(25) INLINE NOT NULL; DELETE FROM employees WHERE last_name='Ernst' RETURNING employee_id, last_name INTO :empid,:name; 1 row deleted. Command> PRINT empid name; EMPID : 104 NAME : Ernst
Notes
The ttIsql utility supports only generic REF CURSOR variables, not specific REF CURSOR types.
The ttIsql utility command line accepts multiline PL/SQL statements, such as anonymous blocks, that are terminated with the "/" on it's own line. For example:
Command> set serveroutput on
BEGIN
dbms_ouput.put_line ('Hi There');
END;
/
Hi There
PL/SQL block successfully executed.
Command>
For UTF-8, NCHAR values are converted to UTF-8 encoding and then output.
For ASCII, those NCHAR values that correspond to ASCII characters are output as ASCII. For those NCHAR values outside of the ASCII range, the escaped Unicode format is used. For example:
U+3042 HIRAGANA LETTER A
is output as
Command> SELECT c1 FROM t1; < a\u3042 >
NCHAR parameters must be entered as ASCII N-quoted literals:
Command> prepare SELECT * FROM t1 WHERE c1 = ?; exec;
Type '?;' for help on entering parameter values. Type '*;' to stop the parameter entry process.
Enter Parameter 1> N'XY';
On Windows, this utility is supported for all TimesTen Data Manager and Client DSNs.