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

Question

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

Interesting... [db<>fiddle is working for 18c](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a6d3d3a4b1921973c1054dc0278f3972), and shows the issue, but the 21c instance seems to be broken. — Alex Poole, Jun 29 '22 at 06:47
As of 21c [the documentation](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6) doesn't mention `TABLE` keyword as optional (though, it is, but not documented). I'd say it shouldn't be used in production code. — astentx, Jun 29 '22 at 07:17
@astentx That is discussed in [this question](https://stackoverflow.com/a/72590661). It was probably a feature released in Oracle 12.2 but it doesn't appear in the release notes and, as you noted, the syntax diagrams don't mention it and there are cases (like this) where the SQL engine will fail to parse the syntax when the table collection expression is excluded so, like you say, its probably best to assume that that the `TABLE()` syntax is needed (rather than trying to leave it out). — MT0, Jun 29 '22 at 08:10

Alex Poole · Accepted Answer · 2022-06-29T06:54:10.103

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

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

1 Answers1