Return to Contents

9 Substitution Variable Examples

9.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are given below.

9.2 Using a Substitution Variable

Once a substitution variable has a value, it can be referenced by prefixing the variable name with an ampersand (&).

If the variable "myv" is already defined it can be used like:

    select employee_id from employees where last_name = '&myv';

9.3 Finding All Defined Substitution Variables

The DEFINE command with no parameters shows all defined substitution variables, their values, and their types. For example:

    define

might give:

    DEFINE MYV             = "King" (CHAR)
    ...

9.4 Inserting Data Containing "&" Without Being Prompted

There are two ways to make an "&" be treated as text and not cause a prompt. The first turns all variable substitution off:

    set define off
    create table mytable (c1 varchar2(20));
    insert into mytable (c1) values ('thick & thin');

The INSERT statement stores the text "thick & thin" in the table.

The second method is useful for ignoring individual occurrences of "&" while allowing others to prefix substitution variables:

    set escape \
    create table mytable (c1 varchar2(20));
    insert into mytable (c1) values ('thick \& thin');
    insert into mytable (c1) values ('&mysubvar');

The first INSERT statement in this method stores the text "thick & thin" in the table. The second INSERT causes SQL*Plus to prompt you for a value, which is then stored.

9.5 Putting the Current Date in a Spool File Name

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

    column dcol new_value mydate noprint
    select to_char(sysdate,'YYYYMMDD') dcol from dual;

    spool &mydate.report.txt
    -- my report goes here
    select last_name from employees;
    spool off

In this example the first query puts the date in the substitution variable "mydate". There is no visible output from this query because of the NOPRINT option in the COLUMN command. In the SPOOL command the first period (.) indicates the end of the variable name and is not included in the resulting string. If "mydate" contained "20030120" from the first query then the spool file name would be "20030120report.txt".

You can use this technique to build up any string for the file name.

The period is the default value of SET CONCAT. If you have assigned another character then use it instead of a period to end the substitution variable name.

9.6 Appending Alphanumeric Characters Immediately After a Substitution Variable

If you wish to append alphanumeric characters immediately after a substitution variable, use the value of SET CONCAT to separate the variable name from the following text. The default value of SET CONCAT is a single period (.). For example:

    define mycity = Melbourne
    spool &mycity.Australia.txt

creates a file with the name "MelbourneAustralia.txt"

9.7 Putting a Period After a Substitution Variable

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

    define mycity = Melbourne
    spool &mycity..log

is the same as:

    spool Melbourne.log

9.8 Using a Fixed Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

This example makes every page of a report have exactly the same heading. It can be used for TTITLE, BTITLE, REPHEADER or REPFOOTER commands.

In a TTITLE command prefix the variable name "dept" with "&" and place it inside a quoted string:

    define dept = '60'
    ttitle left 'Salaries for department &dept'
    select last_name, salary from employees where department_id = &dept;

9.9 Using a Changing Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

This example uses a different title on every page of a report. Each title contains a value derived from query results shown on that particular page.

In a TTITLE command do not put an "&" before the variable name "dv". Put the variable name outside a quoted string:

    column department_id new_value dv noprint
    ttitle left 'Members of department ' dv
    break on department_id skip page
    select department_id, last_name from employees order by department_id, last_name;

In a BTITLE or REPFOOTER command use a COLUMN OLD_VALUE variable instead of a COLUMN NEW_VALUE variable.

9.10 Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL

If you want to use the value of a bind variable in a SQL*Plus command it must first be copied to a substitution variable.

SQL*Plus commands like SPOOL, SET and TTITLE are executed in the SQL*Plus program and are not passed to the database for execution. Because of this they do not understand bind variables.

To use a bind variable's value as the name of a spool file:

    -- Set a bind variable to a text string
    variable mybindvar varchar2(20)
    begin
      :mybindvar := 'myspoolfilename';
    end;

    -- Transfer the value from the bind variable to the substitution variable
    column mc new_value mysubvar noprint
    select :mybindvar mc from dual;

    -- Use the substitution variable
    spool &mysubvar..txt
    select * from employees;
    spool off

9.11 Passing Parameters to SQL*Plus Substitution Variables

You can pass parameters on the command line to a SQL*Plus script:

    sqlplus hr/my_password @myscript.html employees "De Haan"

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

    set verify off
    select employee_id from &1 where last_name = '&2';

Here the "SET VERIFY OFF" command stops SQL*Plus from echoing the SQL statement before and after the variables are substituted. The query returns the employee identifier for the employee "De Haan" from the "employees" table.

Parameters can also be passed to scripts called within SQL*Plus:

    SQL> @myscript.sql employees "De Haan"

9.12 Passing Operating System Variables to SQL*Plus

You can pass an operating system variable to a SQL*Plus script as a command line parameter. For example, on UNIX:

    sqlplus hr/my_password @myscript.sql $USER

or in a Windows command window:

    sqlplus hr/my_password @myscript.sql %USERNAME%

The script myscript.sql could reference the substitution variable "&1" to see the passed name.

9.13 Passing a Value to a PL/SQL Procedure From the Command Line

If you create a procedure "myproc":

    create or replace procedure myproc (p1 in number) as
    begin
      dbms_output.put_line('The number is '||p1);
    end;

and myscript.sql contains:

    begin
      myproc(&1);
    end;
    /

then calling:

    sqlplus hr/my_password @myscript.sql 88

executes the script as if it is:

    begin
      myproc(88);
    end;
    /

This method does not work if the parameter "p1" to "myproc" is "IN OUT". The variable reference is pre-processed and is effectively a hard coded value which cannot contain an OUT value. To get around this you can assign the substitution variable to a bind variable. The script myscript.sql becomes:

    variable mybindvar number
    begin
      :mybindvar := &1;
      myproc(:mybindvar);
    end;
    /

9.14 Allowing Script Parameters to be Optional and Have a Default Value

The goal is to create a script which accepts an optional parameter. If a parameter is passed from the command line then its value should be used. However, if there is no parameter, then SQL*Plus should ask for a value with a customized prompt.

Perhaps the closest solution is with a PROMPT/DEFINE sequence like this. If myscript.sql is:

    -- Name: myscript.sql
    prompt Enter a value for PAGESIZE
    set termout off
    define mypar = &1
    set termout on

    prompt Setting PAGESIZE to &mypar
    set pagesize &mypar

    select last_name from employees where rownum < 20;
    exit

you can call the script with or without a parameter. If you enter "12" at the prompt your screen looks like:

    % sqlplus hr/my_password @myscript.sql

    SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:19:40 2003
    ...

    Enter a value for PAGESIZE
    12
    Setting PAGESIZE to 12

    LAST_NAME
    -------------------------
    King
    Kochhar
    De Haan
    ...

or if you call it with a parameter "8":

    % sqlplus hr/my_password @myscript.sql 8

    SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:20:38 2003
    ...


    Enter a value for PAGESIZE
    Setting PAGESIZE to 8

    LAST_NAME
    -------------------------
    King
    Kochhar
    De Haan
    ...

Note when you pass a parameter the PROMPT text is still displayed, but you do not enter a value. The PROMPT command is the SQL*Plus "echo" or "print" statement. (It does not read input).

The only occurrence of "&1" should be where "mypar" is defined. All other references to the parameter should use "&mypar" or "&&mypar".

9.15 Passing a Value to an iSQL*Plus Dynamic Report for the Web

Variables can be passed as URL parameters to an iSQL*Plus report. For example with iSQL*Plus 10.1:

    http://machine/isqlplus/dynamic?script=http://machine/mys.sql&myv=emp&v2=dept

or in iSQL*Plus 9i:

    http://machine/isqlplus?script=http://machine/mys.sql&myv=emp&v2=dept

These define the substitution variable "&myv" as "emp" and the substitution variable "v2" as "dept" before running the script mys.sql. The script can use "&myv" and "&v2" anywhere substitution variables are allowed.

Note that iSQL*Plus prompts for a username and password before defining the variables and running the script.

9.16 Customizing Parameter Prompts for an iSQL*Plus Dynamic Report for the Web

A customized HTML form can be used to enter and validate variables to be passed to an iSQL*Plus Release 9.2 dynamic report.

Create and save a SQL*Plus script employee_name.sql on your Oracle HTTP Server. Check it can be loaded into a web browser to verify iSQL*Plus is able to access the file over HTTP. The script is:

    -- Name: employee_name.sql
    set verify off
    set pagesize 200
    set feedback off
    prompt Employee Details for Employee(s) with Last Name like &last_name%
    select *
    from employees
    where upper(last_name) like upper('&last_name%')
    /

Create an HTML file myreport.html on your Oracle HTTP Server. The file is:

    <html>
    <head>

      <title>iSQL*Plus Dynamic Report - Query by Last Name</title>
    </head>
    <body>
      <h1>iSQL*Plus Dynamic Report - Query by Last Name</h1>
      <form method=get action="http://machine/isqlplus">
      <input type="hidden" name="script"
            value="http://machine/employee_name.sql">
        Enter last name of employee:
        <input type="text" name="last_name" size="20">
        <input type="submit" value="Run Report">
        </form>
    </body>
    </html>

Replace "http://machine/" with the appropriate host name, domain name and port number of your Oracle HTTP Server, for example, "http://machine.oracle.com:7777/".

The name of the INPUT TYPE should be the same as the substitution variable name in the SQL*Plus script. For example, the input field:

    <input type="text" name="last_name" size="20">

maps to the substitution variable "&last_name" in employee_name.sql.

Load myreport.html in your web browser. Enter a name or partial name in the text field, for example, "Fay". Click the Run Report button. iSQL*Plus will prompt for database connection details and then execute the script. The Employee Details report is displayed in your web browser.

You could add Javascript to the HTML form to do any desired browser-side validation of the values entered.

9.17 Using a Variable for the SQL*Plus Return Status

To use the value of a substitution variable called "myv" as the SQL*Plus return status, use:

    EXIT myv

No ampersand (&) prefix is required before the substitution variable name.

A numeric bind variable requires a colon (:) prefix:

    EXIT :mybv

9.18 Putting the Username and Database in the Prompt

In SQL*Plus 10g add this to your glogin.sql or login.sql:

    set sqlprompt "_user'@'_connect_identifier:SQL> "

For customized prompts that query the database make sure to explicitly DEFINE any referenced substitution variables. Glogin.sql and login.sql can get run when there is no database connection. Defining variables prevents the user being prompted for values when the query fails and the variables do not get defined by it:

    set termout off
    define myv = 'Not connected'
    column myc new_value myv
    select user||'@'||global_name myc from global_name;
    set sqlprompt '&myv:SQL> '
    set termout on

SQL*Plus 9.2 and earlier don't re-execute glogin.sql and login.sql after CONNECT commands. Also variables in the SQLPROMPT are not dynamically substituted. It is possible to use the query script given above, but beware that the prompt will only be valid for the original connection.

Return to Contents