Re: Using a PLSQL Table with an IN clause

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_at_mubimedia.com (Robert Bowen) wrote in message news:<68b4256f.0204040244.40dcf9f_at_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 <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 - 15:58:08 CST