Re: Using a PLSQL Table with an IN clause

From: damorgan <damorgan_at_exesolutions.com>
Date: Thu, 04 Apr 2002 16:49:23 GMT
Message-ID: <3CAC8414.A9F36746@exesolutions.com>

Belief is a wonderful thing. Very comforting. Usually not worth a lot when it comes to resolving problems. Here's what Oracle says on metalink about 00600.

WHAT IS AN ORA-600 ERROR AND HOW TO TREAT IT:

ORA-600 is an internal error generated by the generic kernel code of the Oracle RDBMS software. It is different from other Oracle errors in many ways. The following is a list of these differences.

An ORA-600 error may or may not be displayed on the screen. Therefore, screen output should not be relied on for capturing information on this error. Information on ORA-600 errors are found in the database alert and trace files. We recommend that you check these files frequently for database errors. (See the Alert and Trace Files section for more information.)
Each ORA-600 error comes with a list of arguments usually enclosed in square brackets and follow the error on the same line for example: ORA-00600 [14000][51202][1][51200][][]

Each argument has a specific meaning which can only be interpreted by an Oracle support analyst. The arguments may also change meaning from version to version therefore customers are not advised to memorize them.

3. Every occurrence of an ORA-600 should be reported to Oracle Support.

Unlike other errors, you can not find help text for these errors. Only Oracle technical support should diagnose and take actions to prevent or resolve damage to the database.

4. Each ORA-600 error generates a database trace file. (See the Alert and

Trace Files section for more information.)

Pay special attention to number 3, above. I am so glad you know better than Oracle does about this error. I get a warm and fuzzy feeling being blown off for giving you good advice. Please be sure to post for all of us how you solved the problem without calling Oracle and without reinstalling the product from scratch.

Daniel Morgan

Robert Bowen wrote:

> 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 - 10:49:23 CST