Using a LIST with IN CLAUSE

From: <tb151_at_my-deja.com>
Date: Tue, 24 Oct 2000 21:22:46 GMT
Message-ID: <8t4uiv$rkk$1@nnrp1.deja.com>

Can somebody please help with this. I am not an oracle developer and perhaps have gotten myself in over my head.

I need to write a procedure that takes as the in clause a list of variables, and uses that in a select statement, to return certain rows. I know I can do this by creating collection objects, and using the psuedo-functions (the, table, multiset, and cast) but I can't seem to get it to work.

Below is the smaple code I am working with. Up until the last statement will work on your machine, (you could modify this statement and the list to select values from one of your tables).

I keep getting an oracle error:

ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], []

ORA-06544: PL/SQL: internal error, arguments: [pfrrun.c:pfrbnd1()], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [0]

I have tried to follwo examples posted here, but have found them confusing. Am I using the correct collection type? Isn't the TABLE function the one that makes a collection searchable? Can somebody please help? Do I need to name the variables my collection -- if so how?

Thanks, todd

DECLARE

   FRONT       integer;
   BACK        integer;


   /*

       nested table type

   */
   TYPE TBL_MY_USER_NAMES IS TABLE OF PERSON.USER_NAME_UC%TYPE;    OUR_NAMES TBL_MY_USER_NAMES := TBL_MY_USER_NAMES ();

   STR_SQL     varchar2 (30);
   NAME        PERSON.USER_NAME%TYPE;

BEGIN    STR_SQL := 'BROWN38,BROWN24';
   dbms_output.ENABLE;
/*

       Loop through, splitting the string and inserting
     values into a temp table

   */
   FRONT := 1;
   BACK := INSTR (STR_SQL, ',', FRONT);    WHILE BACK != 0
   LOOP

      OUR_NAMES.EXTEND;
      OUR_NAMES (OUR_NAMES.LAST) := SUBSTR (STR_SQL, FRONT, BACK -
FRONT);
      FRONT := BACK + 1;
      BACK := INSTR (STR_SQL, ',', FRONT);

   END LOOP;
/*

   Get last one
   */
   IF BACK = 0 THEN

      OUR_NAMES.EXTEND;
      OUR_NAMES (OUR_NAMES.LAST) := SUBSTR (STR_SQL, FRONT, LENGTH

(STR_SQL));
   END IF;
/*

   Dump temp table as proof table is built    */
   FOR element IN 1..OUR_NAMES.COUNT
   LOOP
      dbms_output.PUT_LINE (OUR_NAMES(element));    END LOOP;

       SELECT USER_NAME
         INTO NAME
         FROM PERSON
        WHERE USER_NAME_UC IN (SELECT * from TABLE(OUR_NAMES));

   dbms_output.PUT_LINE (NAME);
END; Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 24 2000 - 16:22:46 CDT