Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Using a PLSQL Table with an IN clause

Re: Using a PLSQL Table with an IN clause

From: Robert Bowen <robert.bowen_at_mubimedia.com>
Date: 4 Apr 2002 02:44:10 -0800
Message-ID: <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 <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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US