Why do I need the keyword TABLE in this dynamic query although I don't need in a normal query-CodePudding

db fiddle isn't working now. I will upload the code later...

SELECT COUNT (*) FROM sys.odcivarchar2list ('2', '2');

this query work without using the key word table. But in a dynamic query:

DECLARE
    c             INTEGER;
    towtimestwo   SYS.odcivarchar2list := sys.odcivarchar2list ('2', '2');
BEGIN
    
     EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM  :1'
        INTO c
        USING towtimestwo;
    DBMS_OUTPUT.put_line (c);

END;

it doesn't work:

invalid table name

therefore I must add the keyword.

DECLARE
    c             INTEGER;
    towtimestwo   SYS.odcivarchar2list := sys.odcivarchar2list ('2', '2');
BEGIN
    
     EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM  table( :1)'
        INTO c
        USING towtimestwo;
    DBMS_OUTPUT.put_line (c);

END;

Why?

and I'm using Oracle 19.0

CodePudding user response：

Because when the dynamic statement

'SELECT COUNT (*) FROM  :1'

is parsed it sees the :1 as a table name, and you can't use a bind variable for the table name, or any other fixed part. You can only use bind variables for, well, variables, i.e. data.

It doesn't matter that you're passing in a collection that can be treated as a table; the SQL parser doesn't know (or care) yet.

For the statement:

'SELECT COUNT (*) FROM  table( :1)'

it now does see the :1 as a variable, because of the table expression table() around it, so that is valid.

You can use your original static SQL statement as a dynamic statement:

'SELECT COUNT (*) FROM sys.odcivarchar2list (''2'', ''2'')'

or with alternative quoting:

q'^SELECT COUNT (*) FROM sys.odcivarchar2list ('2', '2')^'

because those also don't try to use a bind variable as the source table.

db<>fiddle