Parameters

Parameters let you bind values that will be used in processing SQL statements. These parameters are marked by using ? or by using :IDENTIFIER within the SQL statement.

ttIsql will prepare your SQL statement text. It will then look for variables that match the parameter names. For parameters where there are no matching variables, ttIsql will prompt for the values. The name of the parameter when ? is used is QMARK_N where N starts with 1 and is incremented as each ? is encountered in the statement. For example, QMARK_1, QMARK_2, etc. The name of :PARAM is PARAM.

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.

Parameter values must be terminated with a semicolon character.

The possible types of values that can be entered are:

Numeric literals. For 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'
```

Unicode string literals within single quotation marks preceded by 'N'. For example, N'abc'
A NULL value. For example, NULL
The '*' character that indicates that the parameter input process should be stopped. For example, *
The '?' character prints the parameter input help information. For example, ?

This example shows that A and B are dynamic parameters and not variables.

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> print;

The print command does not show result as they are dynamic parameters.

This examples shows that :a and :b are used as variables and also, they are used as parameters but are not dynamic.


Command> var a number;
Command> exec :a := 110;

PL/SQL procedure successfully completed.

Command> print a;
A                    : 110
Command> var b number;
Command> 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

This example shows that both :a and :b are variables and there is a dynamic parameter :c that can be combined together.


Command> var a number;
Command> exec :a := 110;
Command> var b number;
Command> exec :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.
Command> print;
A                    : 110
B                    : 99

In this example, since _QMARK_1 is not declared, ttIsql will prompt for the parameter value. The :a parameter is filled in with the A variable whose value is 110. The :b is filled in with the B variable whose value is 99. These values are then sent to the engine, which processes the statement and returns the value abc11099.

Command> select cast(? as varchar2(10)) || :a || :b from dual;

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' (VARCHAR2) > 'abc';

< abc11099 >

1 row found.