SQL Plus - Argument

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

The arguments of a script become become substitution variable:

  • &1 for the first one,
  • &2 for the second one,
  • etc ….

SQL*PLUS doesn't have any flow control (IF statement), you have to do them in SQL or PL/SQL.

Advertising

3 - Initialization

If no argument has been given, you can still initialize them with this snippet:

COLUMN 1 new_value 1
COLUMN 2 new_value 2
 
SET termout off
SELECT NULL AS "1"
,      NULL AS "2"
FROM   dual 
WHERE  1=2;
SET termout ON
 
PROMPT The VALUE FOR the FIRST argument IS: &1
PROMPT The VALUE FOR the SECOND argument IS: &2
 
DEFINE 1
DEFINE 2
 
UNDEFINE 1
UNDEFINE 2

Example:

@myScript  1 
The value for the first argument is: 1
DEFINE 1               = "1" (CHAR)
The value for the second argument is:
DEFINE 2               = "" (CHAR)

The variable 2 is NULL and can be tested with the function NVL.

lang/sqlplus/argument.txt · Last modified: 2017/09/06 19:29 by gerardnico