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

I don't think it is possible. We always concatenate the query as a string and then execute it.

Answer 2

Use EXECUTE IMMEDIATE?

Answer 3

Substitution variables work:

SQL> select * from &table_name;
Enter value for table_name: dual
old   1: select * from &table_name
new   1: select * from dual

D
-
X

Answer 4

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 5

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.

Answer 6

You have to do something like this:

EXECUTE IMMEDIATE 'select * from' || tableNm;

This is because Oracle does not allow bind variables for table (or any other object names).

There are significant security implications with the EXECUTE IMMEDIATE approach: when the tableNm value is user-supplied, you are wide open to SQL injection attacks.

Answer 7

you can not bind table names in PL/SQL queries

Answer 8

I am trying to write a query which will count no of records in all the table of a schema and I am writing the following query.

declare
v_count number(10);
begin
for c1 in (select tname from tab)
loop
execute immediate 'select count(*) into v_count from :a' using c1.tname;
dbms_output.put_line(c1.tname || '--'||v_count);
end loop;
end;
/

Where am I going wrong here?