Re: Using a PLSQL Table with an IN clause

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 Tue Apr 02 2002 - 10:04:31 CST