Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using a PLSQL Table with an IN clause
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 <damorgan_at_exesolutions.com> wrote in message news:<3CA9D75C.E67754EC_at_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
Received on Thu Apr 04 2002 - 04:44:10 CST