Re: Query from array?

From: joel garry <joel-garry_at_home.com> Newsgroups: comp.databases.oracle.misc
Subject: Re: Query from array?
Date: Tue, 8 Mar 2011 14:45:55 -0800 (PST) Organization: http://groups.google.com
Lines: 30
Message-ID: <d9f98e3a-c594-4a8d-9c21-7b4aa0e35aea_at_x8g2000prh.googlegroups.com> References: <04bd557b-cfd9-4f10-b657-9458859f5ef9_at_d26g2000prn.googlegroups.com> NNTP-Posting-Host: 12.232.112.189
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1299624355 3398 127.0.0.1 (8 Mar 2011 22:45:55 GMT) X-Complaints-To: groups-abuse_at_google.com NNTP-Posting-Date: Tue, 8 Mar 2011 22:45:55 +0000 (UTC) Complaints-To: groups-abuse_at_google.com Injection-Info: x8g2000prh.googlegroups.com; posting-host.232.112.189; posting-account=tpQovAkAAABNoH5bwsZAiff2L0zxGwdv User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13)  Gecko/20101203 Firefox/3.6.13 ( .NET CLR 3.5.30729),gzip(gfe) Xref: textnews.cambrium.nl comp.databases.oracle.misc:29771

On Mar 8, 10:39 am, The Magnet <a..._at_unsu.com> wrote:
> This is kind of funky.  But the application uses what looks like these
> PIPE ROW functions and uses that as a loop driver:
>
> FOR v_rec IN (SELECT * FROM
> TABLE(data_list_functions.pipe_data_list_100()) WHERE rownum <= 100)
> LOOP
>
> At any rate, what I need to do is the reverse of what one would
> normally do.  I need to remove all records from a given table that do
> NOT exist in this array above.
>
> So the record set returned contains new / old records to be added /
> deleted.  But records in the table that are not in the array, those
> need to be removed.
>
> Any thoughts?  It's kind of ass backwards, but this is what I was
> given to work with.

If I'm understanding this correctly (unlikely), you need to read all the pipeline before you can decide something is not in it. So read it all into a gtt, then delete from table where not in gtt.

jg

--
_at_home.com is bogus.
"Squiggly."  “At first it started to vibrate, then it started to
hurt.” 7-8 year old girls describe getting shot.
http://www.signonsandiego.com/news/2011/mar/07/hearing-begins-man-charged-carlsbad-school-shootin/