How do I execute a SQL statement that uses a variable as a table name ?

The Oracle (tm) Users' Co-Operative FAQ

How do I execute an SQL statement that uses a variable as a table name ?

In SQLPlus, it is quite simple :

SQL> set verify off
SQL> select * from &which_table;
Enter value for which_table: dept
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.

The use of the ampersand (&) in front of a name makes SQLPlus look for a variable with the name and if it finds one, substitutes the value into the command. If it doesn't find one, then you will be prompted to supply a name which will be used instead. Note that I have used 'set verify off' to avoid SQLPlus listing the before and after values of the variable substitution.

If you have a script like the following, then you will be prompted for the variable name each time :

select count(*) from &which_table;
select * from &which_table;

Assuming the above is saved as test.sql, then running it gives the following results :

SQL> @test
Enter value for which_table: dept
  COUNT(*)
----------
         4
1 row selected.
Enter value for which_table: dept
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.

Notice how I was prompted both times for the table name. This is a bit of a pain having to keep typing the same value, especially if you have some long running SQL in the script. To avoid this problem, change the script to use two ampersands (&&) in front of the variable name, as follows :

select count(*) from &&which_table;
select * from &&which_table;

Running it now gives the following results :

SQL> @test
Enter value for which_table: dept
  COUNT(*)
----------
         4
1 row selected.
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.

This time, I was only prompted the first time for the table name. This will happen every time this script is run - only the very first usage of a variable will be prompted for - if it cannot be found as an existing variable. If you know what you want the variable's value to be before you run the script, you can do it this way instead :

SQL> define which_table = 'DEPT'
SQL> @test
  COUNT(*)
----------
         4
1 row selected.
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.

As you can see, I was not prompted at all for the table name. From this point onwards, every time SQLPlus sees &which_table or &&which_table in a script, the value 'DEPT' will be substituted. How do we undefine a variable so that we get prompted again, or so that some other script which uses the same variable name doesn't pick up a possibly incorrect value ?. Quite simply undefine it !

SQL> undefine which_table
SQL> @test
Enter value for which_table: DEPT

You can see from the above, that we are prompted for the table name again. The script we are using to test out variable substitution can be modified as follows to make it even better :

select count(*) from &1;
select * from &1;

The change replaces '&which_table' with '&1' which is a digit one. This refers to its position on the command line when the script was executed. Parameters supplied to a script number from one upwards. Now we can do the following :

SQL> @test dept
  COUNT(*)
----------
         4
1 row selected.
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.
SQL> @test salgrade
  COUNT(*)
----------
         5
1 row selected.
     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999
5 rows selected.

This latest version of the script is much more usable as there is no need to define variables before use. However if you forget to pass a parameter to the script, the prompt is a bit strange - it asks you to provide a value for '1' instead of a more meaningful name.

Further reading:

SQLPlus training manuals from official Oracle training.

SQL*Plus User's Guide and Reference manual, the chapter on Writing Interactive Commands.