"Oracle Built-in Packages" sample doesn't work?

From: Matt Houseman <mhousema_at_ix.netcom.com>
Date: Sun, 15 Oct 2000 14:37:25 -0700
Message-ID: <8sd7jb$sb9$1@slb1.atl.mindspring.net>

All,

I have a comma delimited list that needs to be converted to a table. Fortunately, I have Steven Feuerstein's "Oracle Built-in Packages" book with an example on exactly how to do this. Unfortunately, the example doesn't work. I've pasted in the example from page 542 below:

CREATE OR REPLACE PROCEDURE upd_from_list (

   empno_list IN VARCHAR2,
   sal_list IN VARCHAR2)
IS

   empnos DBMS_UTILITY.UNCL_ARRAY;
   sals DBMS_UTILITY.UNCL_ARRAY;
   numemps INTEGER;
BEGIN
   DBMS_UTILITY.COMMA_TO_TABLE (empno_list, numemps, empnos);    DBMS_UTILITY.COMMA_TO_TABLE (sal_list, numemps, sals);    FOR rownum IN 1 .. numemps
   LOOP

      UPDATE emp SET sal = TO_NUMBER (sals(rownum))
       WHERE empno = TO_NUMBER (empnos(rownum));

   END LOOP;
END;
/

When I attempt to execute this using the scott/tiger schema, I get the following:

SQL> exec upd_from_list ( '7369,7499', '900,1700' ) BEGIN upd_from_list ( '7369,7499', '900,1700' ); END;

*
ERROR at line 1:

ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 79
ORA-06512: at "SYS.DBMS_UTILITY", line 108
ORA-06512: at "SCOTT.UPD_FROM_LIST", line 9
ORA-06512: at line 1

Anyone have any ideas? I would like to use the DBMS_UTILITY.COMMA_TO_TABLE for parsing a list of numbers into a table exactly like the above example, but clearly there something wrong...

Thanks,
Matt Received on Sun Oct 15 2000 - 16:37:25 CDT