Re: Using a LIST with IN CLAUSE

I don't understand why this works, but try creating TBL_MY_USER_NAMES as a type and then use it inside your PL/SQL block. We had the same problem the last place I worked. If we declared the type in an anonymous block, we got the same error. If we created the type using the CREATE TYPE statement and then used it inside our anonymous block, it worked fine. I would love it if someone much smarter than me could explain why this method works.

In article <8t4uiv$rkk$1_at_nnrp1.deja.com>,   tb151_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 25 2000 - 10:16:31 CDT