CODEWITHSUNDEEP
sqloracle-databasescriptingplsql
Gern Blanston
Gern Blanston

Reputation: 42660

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

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;

Upvotes: 16

Views: 83938

Answers (5)

MT0
MT0

Reputation: 167822

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

Upvotes: 0

Arkady
Arkady

Reputation: 15059

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.

Upvotes: 8

Steve Broberg
Steve Broberg

Reputation: 4394

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.

Upvotes: 15

Dave Costa
Dave Costa

Reputation: 48111

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.

Upvotes: 8

Eddie Awad
Eddie Awad

Reputation: 3729

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

Upvotes: 6

Related Questions