Path: news.easynews.com!easynews!feedwest.news.agis.net!aleron.net!sfo2-feed1.news.algx.net!allegiance!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: crappygolucky@hotmail.com (crappy) Newsgroups: comp.databases.oracle.misc Subject: Re: Using a PLSQL Table with an IN clause Date: 4 Apr 2002 13:58:08 -0800 Organization: http://groups.google.com/ Lines: 111 Message-ID: References: <68b4256f.0204010602.43646f31@posting.google.com> <3CA903EA.F94E45C8@stunk.org> <68b4256f.0204020804.3da6c98c@posting.google.com> <3CA9D75C.E67754EC@exesolutions.com> <68b4256f.0204040244.40dcf9f@posting.google.com> NNTP-Posting-Host: 208.207.95.30 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1017957489 13012 127.0.0.1 (4 Apr 2002 21:58:09 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 4 Apr 2002 21:58:09 GMT Xref: easynews comp.databases.oracle.misc:79686 X-Received-Date: Thu, 04 Apr 2002 14:55:52 MST (news.easynews.com) don't give up just yet. you've worked so hard on it. try (MUNICIPIO.ID IN (SELECT column_value FROM TABLE (CAST(v_munisTable AS tMunisTable) ) ) ) note "column_value" instead of * robert.bowen@mubimedia.com (Robert Bowen) wrote in message news:<68b4256f.0204040244.40dcf9f@posting.google.com>... > Well ... Thanks, that's very helpful. Why not post RTFM? > > I happen to think you are wrong, that I am definitely doing something > wrong and that's why I am getting such a strange error. I seriously > doubt that I have some kind of system problem with Oracle. > > In the end, after reading all the posts on asktom.oracle.com (do a > search for nested table) I came up with this code: > > ... > (MUNICIPIO.ID IN (SELECT * FROM TABLE (CAST(v_munisTable AS > tMunisTable) ) ) ) > ... > > I also used a "create type tMunisTable as table of number" executed > from SQL*Plus instead of using a type defined in a package (see my > previous post in this thread) so it would be visible by SQL. But no > go. I forget the error but it's not a pretty one and I can only assume > that it's impossible to use a nested table variable in an IN clause > without using dynamic sql and a FOR..IN LOOP to spit out all the data. > > In the end it's moot because when I execute the REAL query with a list > of 70 municipios (towns) it takes about 3 minutes! The REAL data could > have something like 1,000 towns and would take even longer! Of course, > if you think about it it's just doing 1,000 ORs which takes a BUTTLOAD > of resources. So I have had to do it in another way. > > I post my results in case another poor soul has the same problem (many > have, from what I have seen ...) to save them their time, and wish > them luck. > > The asktom.oracle.com site is excellent by the way, and answers a > bunch of complex oracle questions. > > Thanks to all to have tried to help. The towel has now been thrown in. > Bob > > damorgan wrote in message news:<3CA9D75C.E67754EC@exesolutions.com>... > > This will be really simple: ORA-00600 has one and only one meaning ... > > phone Oracle support or contact them through metalink. You may have other > > problems ... but the biggest one has to do with the Oracle software and > > needs to be attended to first. > > > > Daniel Morgan > > > > > > > > Robert Bowen wrote: > > > > > I have decided to post exactly what I am doing, in the hope that > > > someone can give me a hand. The examples on asktom.oracle.com are a > > > bit overcomplicated in that he is storing and retrieving info from a > > > table saved in a database table. > > > > > > I am simply creating a table, adding dato to it, and later trying to > > > use it in an IN clause. Here is more or less what I have: > > > > > > -- Package with my types > > > PACKAGE PTYPES IS > > > TYPE CURSORTYPE IS REF CURSOR; > > > TYPE TMUNIS IS TABLE OF NUMBER; > > > END PTYPES; > > > > > > -- Package with real code > > > > > > -- Variables > > > v_munisTable PTYPES.TMUNIS := PTYPES.TMUNIS(); > > > v_index NUMBER := 1; > > > v_tMuni NUMBER; > > > > > > -- Code > > > -- When I find a municipio that meets my criteria, I add it to the > > > table > > > v_munisTable.EXTEND; > > > v_munisTable(v_index) := v_tMuni; > > > v_index := v_index +1; > > > > > > -- Now I try to spit it out, using code I grabbed from > > > asktom.oracle.com > > > SELECT ID FROM MUNICIPIO WHERE > > > MUNICIPIO.ID IN > > > (select * from TABLE ( cast ( v_munisTable as PTYPES.TMUNIS) ) ); > > > > > > ... It compiles but upon execution gives this error: > > > ORA-00600: internal error code, arguments: [15419], [severe error > > > during > > > PL/SQL execution], [], [], [], [], [], [] > > > > > > I realize now that the select statement is completely wrong, that it > > > refers to a table saved as a column in a table in the BD, and it NOT > > > what I need. > > > > > > I simply need to spit out all the info in my table and use it in an IN > > > clause. But from what I have seen the only way to do this is with a > > > FOR..IN LOOP which you can't use inside an IN clause. > > > > > > THAT is my problem. So ... like ... how the heck do I do this? > > > > > > Thanks again, > > > Bob