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.