Oracle SQL: create a dumy table from a substitution variable

Question

In Oracle SQL, I can create a dummy table thanks to the code shown below:

select crca.*
from my_real_table real_table, 
     table(ntde.ENCRYPT_ALL(:inputParam)) enc
where
    ...

I would like to be able to do same thing without using ntde.ENCRYPT_ALL, I would like to do something like that:

select crca.*
from my_real_table real_table, 
     table(:inputParam) enc
where
    ...

It does not work and I get this error:

00000 - "cannot access rows from a non-nested table item"
*Cause: attempt to access rows of an item whose type is not known at
parse time or that is not of a nested table type
*Action: use CAST to cast the item to a nested table type

Do you know how to do that please?

You have tagged [oracle-sqldeveloper], if this is what you are using then you probably need to [edit] your question to include a [MRE] with a complete example of how you are generating the bind variable and what value the bind variable has because I do not believe that SQL developer supports storing collection data types in bind variables. The list of data types it does support is given in the [`VARIABLE` documentation of the sql/plus user guide](https://docs.oracle.com/cd/E18283_01/server.112/e16604/ch_twelve050.htm). — MT0, Oct 14 '20 at 00:01
Also, `:name` is a bind variable. `&name` is a substitution variable. — MT0, Oct 14 '20 at 00:11

score 2 · Accepted Answer · answered Oct 13 '20 at 23:52

As the exception says, use CAST:

SELECT c.*
FROM   my_real_table r 
       CROSS JOIN TABLE( CAST(:inputParam AS your_collection_type) ) c

This assumes that:

the bind variable contains an collection (and not a string); and
the bind variable is being passed from an application (this is a Java example); and
you have created an SQL collection type to cast the bind variable to. For example:
```
CREATE TYPE your_collection_type AS TABLE OF NUMBER;
```
Or, instead of creating your own collection type, you could use a built in collection (or VARRAY) such as SYS.ODCIVARCHAR2LIST.

Oracle SQL: create a dumy table from a substitution variable

1 Answers1