how to use a variable in oracle script for the table name

Question

I have an pl\sql script where I want to set the table name used in the script to a variable. So, from some examples I found on the web, I wrote the code below. The first section works, so I think my general syntax is correct,but the second section, where I attempt to use a variable for a table name it errors ("SQL Error: ORA-00903: invalid table name").

Anybody know what I'm doing wrong...I don't do a lot of PL\SQL so maybe I'm just missing something obvious.

--works
variable numOfrecords number;
exec :numOfrecords := 10;
select * from customers2008 where rownum < :numOfrecords;

--does not work
 variable tableNm CHAR;
 exec :tableNm := 'customers2008';
 print tableNm;
 select * from :tableNm;

Answer 1

If you are running this script from sqlplus (which looks to be the case), you want to use the DEFINE command, which allows you to create sqlplus substition variables that are just straight string substitution, e.g.:

define tableNm = 'customers2008'
select * from &tableNm;

See Using Sql*Plus for more information on how these are used. You can pass values into your script from the command line using the predefined positional substition variables, like this:

define tableNm = &1
select * from &tableNm;

... and then invoke sqlplus like so:

sqlplus user/pwd@server @myscript.sql customers2008

If you don't pass in a value on the command line, the script invoker will be prompted for the value.

See Dave Costa's answer below for the differences between bind and substitution variables.

Answer 2

To try to add some explanation:

The method that you were trying to use is called a bind variable. A bind variable is identified in Oracle SQL by a colon followed by an identifier. The purpose of a bind variable is that its value does not need to be known when parsing the SQL statement; the statement can be parsed once and then executed multiple times with different values bound to the variable.

In order for a SQL statement to be parsed, the table and column names involved must be known. So the table name can't be represented by a bind variable, because the value would not be known at parse time.

If you are simply executing SQL and inline PL/SQl via SQLPlus, then substitution variables are an easy way to deal with this issue, as Steve explained. A substitution variable is replaced with its value when the SQLPlus client reads the command, before it even sends it to Oracle for parsing.