Re: Using a LIST with IN CLAUSE

I get the same errors in three different databases...

Any ideas?

In article <svcjshmutg9f13_at_corp.supernews.com>,   "Dave A" <dave_and_vanna_at_hotmail.com> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 25 2000 - 09:17:44 CDT