Search code examples
sqloracle-databaseoracle-sqldevelopersubstitution-variables

ORA-22905: cannot access rows from a non-nested table item

accept y CHAR   PROMPT 'Please type 1 for TEST_104 or type the table_name:- '

Then I type 1 for the default table or for any other table I type test_105.

Then I checked like below from dual its working fine.

select decode('&y' ,'1','TEST_104','&y') from dual;

But in select statement its not working.

SELECT * FROM decode('&y', '1', 'TEST_104','&y') ;

So I have used the cast function like below.

SELECT * FROM table (cast(decode('&y', '1', 'TEST_104','&y'))) ;

Still not working. Any suggestions please.

Solution

  • &y is a substitution variable - it is processed by the client application that you are using (i.e. SQL*Plus, SQL Developer, SQLCl but not all client application support bind variables) and effectively performs a find-replace on the text of the SQL statement before it is sent to the database to substitute &y with whatever text you have input to replace it with and then the substituted text is sent to the database.

    The database:

    • Never sees the original query, only the substituted query;
    • Does not understand substitution variables (they are processed entirely on the client application and not the database).

    Therefore:

    ACCEPT y CHAR PROMPT 'Please type the table_name:- '

SELECT * FROM &y;

    would work as whatever the user types would replace &y and SELECT * FROM table_name; is valid syntax.

    However:

    ACCEPT y CHAR PROMPT 'Please type 1 for TEST_104 or type the table_name:- '

SELECT * FROM decode('&y', '1', 'TEST_104','&y');

    Is not valid because the query SELECT * FROM decode('table_name', '1', 'TEST_104','table_name'); (which is what the database would see) is not syntactically valid SQL as an identifier for the table you are SELECTing from CANNOT be dynamically derived from a string.

    Similarly, wrapping decode in a TABLE() collection expression is not valid syntax because the decode function is not returning a collection.

    What you probably want is to set a DEFAULT value in the ACCEPT:

    ACCEPT y CHAR DEFAULT 'TEST_104' PROMPT 'Please type the table_name (default TEST_104) :- '

SELECT * FROM &y;