Re: PL/SQL Nested Table inside a SQL statement

On 9 Oct 2003 10:01:54 -0700, mirkocarlini_at_yahoo.co.uk (Mirko) wrote:

>Hi,
>I have a PL/SQL procedure (inside a package) with the following SQL code:
>
> INSERT INTO set_day_substore_r_k
> (day_id, ...)
> SELECT e.day_id,...
> FROM
> e,
> TABLE(datetimes_required) dtr,
> -- only find the capture_dates within the range of the datetimes we require
> e.capture_date >= dtr.dt AND
> e.capture_date <
> (dtr.dt + reporting_common.day_interval)
>
>ORA-22905: cannot access rows from a non-nested table item
>ORA-06512: at "M.PACKAGENAME", line 842
>ORA-06512: at line 1
>
>Line 842 is the BEGIN of the procedure.
>
>Any ideas?
>THANK YOU,
>Mirko PLEASE REPLY TO: mirko.carlini_at_libero.it

AFAIK You can only use select ... table(A_Collection) operations if A_Collection is defined explicitly for the data dictionary. Types defined locally in procedure or packages are not.

All you have to do is use : create type A_Collection as table of .....

Phenton. Received on Fri Oct 10 2003 - 06:01:05 CDT