Re: Using a LIST with IN CLAUSE

I will admit that I didn't read much of the procedure. I got side tracked by the ORA-00600 errors.
Can you duplicate the errors on another database with the same code?

Ora-600 means call support.

--
Dave A


<tb151_at_my-deja.com> wrote in message news:8t4uiv$rkk$1_at_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.