Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!cycny01.gnilink.net!cyclone1.gnilink.net!small1.nntp.aus1.giganews.com!border1.nntp.aus1.giganews.com!intern1.nntp.aus1.giganews.com!nntp.giganews.com!nntp.tcp.co.uk!news.tcp.co.uk.POSTED!not-for-mail
NNTP-Posting-Date: Fri, 10 Oct 2003 06:01:08 -0500
From: Phenton <Phenton@bt.co.uk>
Newsgroups: comp.databases.oracle.misc
Subject: Re: PL/SQL Nested Table  inside a SQL statement
Date: Fri, 10 Oct 2003 12:01:05 +0100
Message-ID: <nu3dov80lda99qb98162dqt82qfkbe6kte@4ax.com>
References: <91c9533c.0310090901.a856986@posting.google.com>
X-Newsreader: Forte Agent 1.92/32.572
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 33
NNTP-Posting-Host: 195.212.28.195
X-Trace: sv3-kmKvj+72piGjn9aUSxm4SvIIeCQpAAaouY7JAXN9qDTpMgrHFNXx7zHd23dg6WCoWriahSEH8Wi8voi!sJwioBK/MfATB/yqqRazd26zd/TfkLzDWfUNMl5c+NeF/0mdOWndQ85ph5HD0u+cDvroRrODFpPo!R/OXkwo=
X-Complaints-To: abuse@tcp.co.uk
X-DMCA-Complaints-To: abuse@tcp.co.uk
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.1
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:132182

On 9 Oct 2003 10:01:54 -0700, mirkocarlini@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@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.