PL-SQL: why does a dynamic statement using bind-variable input not work?

Question

I am trying to create and execute a dynamic SQL statement (using Oracle PL/SQL) and when things didn't work I condensed it to the below two code snippets:

My understanding was that the following two statements would be equivalent (table_name, row_name and row_value are all VARCHAR2 strings):

query_statement VARCHAR2(1000);

    -- variant a:
        query_statement := 'select * from ' || table_name || ' where ' || row_name || ' = ' || row_value;
        dbms_output.put_line('query="' || query_statement || '"');        
        execute immediate (query_statement);

    -- variant b:
        query_statement := 'select * from :table where :row = :value';
        dbms_output.put_line('query="' || query_statement || '"');        
        execute immediate (query_statement) using table_name, row_name, row_value;

But not so! The first variant works fine, the second always fails with an error:

** An error was encountered: -903 ORA-00903: invalid table name

From a readability point of view I would prefer variant b but I don't get, why that second variant fails and why the table_name is flagged as wrong. After all that very same name obviously works in variant a. The emitted queries also looks identical to me - of course modulo the fact that the second string still contains placeholders only while the first string contains already concrete values.

Could some kind soul shed some light on this? Why is variant b not working? What am I missing?

I'm not sure where this is documented exactly, but bind variables are for parameters like where cheese_type = :b1 where the same query can be run for different values, not for structural components that would result in different queries altogether like where :b1 :b2 :b3 or :b1 :b2 :b3 :b4. Dynamic SQL is a quite different thing. — William Robertson
– William Robertson, Commented Jan 21, 2020 at 0:05

XING · Accepted Answer · 2020-01-21 02:49:24Z

2

As mentioned by @William, usage of bind variable as Tablename is prohibited. On similar lines, you cannot use bind variables as Column Names and Order By clause as well.

Although this is not documented anywhere but its important to note that replacing these ordinal notation affects the execution plan.

answered Jan 21, 2020 at 2:49

XING

9,9164 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marmite Bomber · Accepted Answer · 2020-01-21 21:52:01Z

1

You can't use a bind variable as a table_reference. As documented in the link, the table_reference must be static part of the statement.

 select * from :table  -- ILLEGAL

Contrary to that the usage in the WHERE clause is allowed

 where :row = :value  -- LEGAL, but questionable

but it doesn't do what you expect. The whereclause returns TRUE, if both bind variables passed are same. I.e. it is equivalent to

 where 'name' = 'xxxx'

and not to

 where name = 'xxxx'

edited Jan 21, 2020 at 21:52

answered Jan 21, 2020 at 7:44

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

1 Comment

mmo Over a year ago

Thanks William Robertson, Marmite Bomber and XING for responding and clarifying this! This was very helpful! Sorry, that I can accept only one of your responses as accepted answer!

Collectives™ on Stack Overflow

PL-SQL: why does a dynamic statement using bind-variable input not work?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related