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.

If you provide the name of a file when prompted for a parameter, the contents of that file will be used as the value for the parameter.

To view the file content:

$cat -n filename
1  'aaaa'bbb
2  12"34
3  xyz
4
5  '
6

You can enter a file name as a parameter after @ or @@ that contains the content. The following output is displayed:

Command> select cast(? as varchar2(100)) from dual;
...
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > @filename
< 'aaaa'bbb

12"34

xyz

'
>
1 row found.

You can also use @@ to specify files in a relative path to the script being executed with the run command.

For diagnostic information, set the verbosity level to 4, ttIsql will print a diagnostic message when the file is loaded:

Command> select cast(? as varchar2(50)) from dual;
...
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > @test4
File "test4" loaded 31 bytes
< aaaaaa
bbbbbbbb
cccc
ddddddddd
>
1 row found.

If you do not enter the file name after @ or @@, the following error message is displayed:

Enter Parameter 1 '_QMARK_1' (VARCHAR2) > @@
No file name was provided. Type '?;' for help.

If the file is not found, the following output is displayed:

Enter Parameter 1 '_QMARK_1' (VARCHAR2) > @filename
File "filename.sql" does not exist.

If the file cannot be read, the following error message appears:

Enter Parameter 1 '_QMARK_1' (VARCHAR2) > @noread
Unable to open file: "noread.sql"

This message appears if the input is a directory.

Enter Parameter 1 '_QMARK_1' (VARCHAR2) > @//

"//" is a directory

Note:

A file as a parameter is not supported for NCHAR or NVARCHAR data types.