Friday, August 23, 2013

SQL*Plus DEFINE - versatile usage to pass multiple values to an IN clause

SQL*Plus DEFINE specifies a user or predefined variable and assigns a CHAR value to it, or lists the
value and variable type of a single variable or all variables.

Whenever you run a stored query or script, SQL*Plus substitutes the value of variable
for each substitution variable referencing variable (in the form &variable or &&variable).
SQL*Plus will not prompt you for the value of variable in this session until you
UNDEFINE variable.

DEFINE is very versatile when it comes to handle multiple values.
Say you want to define a variable which contains multiple values that you plan to use with an IN operator.

Tested in Oracle Database 12c, but works also in 11g or 10g
SQL> select banner from v$version where rownum =1;

Oracle Database 12c Enterprise Edition Release - 64bit Production

This is as easy as:

SQL> define own = "'SYS', -
> 'SYSTEM', -
> 'NULL'"

This is interpreted as it follows;

SQL> define own
DEFINE OWN             = "'SYS',  'SYSTEM',  'NULL'" (CHAR)

And now we will use it in a select:

SQL> select count(*), owner
  2  from dba_objects
  3  where owner in (&own)
  4  group by owner;
old   3: where owner in (&own)
new   3: where owner in ('SYS',  'SYSTEM',  'NULL')

---------- ----------------------------------------
       635 SYSTEM
     41873 SYS

No comments:

Post a Comment