I tried this a couple of years ago and it worked fine AS LONG AS THE DATA
WAS CHARACTER only. I couldn't get it to work with character data where the
data was numeric. (eg. '1', '2'... instead of 1,2,3,4...) I think the whole
thing is stupid that it doesn't work with character data that are numbers.
It worked if it was 'abc',def'...
Jim
"Matt Houseman" <mhousema_at_ix.netcom.com> wrote in message
news:8sd7jb$sb9$1_at_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 - 18:37:32 CDT
