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;

Oquendo

Just FYI: Based on your examples, you aren't really using PL/SQL. I think you mean that you have a SQLPlus script. Technically, your exec commands are calling PL/SQL but overall what you are showing here are SQLPlus commands. – Karlie

Well, technically he's using both - the variable command in the script declares a PL/SQL bind variable. – Testify

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.

Testify

Only minor quibble is that your SQLPlus invocation example doesn't work (at least for me). In order to pass the value on the command line, you have to be calling a script, e.g. sqlplus user/pwd@server @myscript customers2008. – Karlie

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.

Karlie

Answer 3

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.

Intervene

Answer 4

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

Kennard

Hah! Great minds think alike - even the doc link is identical. – Testify

Indeed! The links are identical up to the anchor :) – Kennard

Answer 5

If you are using SQL Developer then:

VARIABLE tableNm CHAR;
VARIABLE cur     REFCURSOR;
EXEC :tableNm := 'customers2008';

DECLARE
  v_sql VARCHAR2(200) := 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(:tableNm);
BEGIN
  OPEN :cur FOR v_sql;
END;
/

PRINT :cur

Westfahl