Thursday, April 01, 2010


It was 5:00PM and I was asked to write a function which needs to parse a comma delimited string. I was lazy and trying to avoid the lengthy substr/instr loop. I remember I read somewhere that there is builtin function exactly does the same thing.
After a search, I found out the function dbms_utility.comma_to_table and that's when the nightmare started.

After I finished the function and tested with 'abc,efg', it works well.
When I put it into real use
This is what I got:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at "SYS.DBMS_UTILITY", line 160
ORA-06512: at "SYS.DBMS_UTILITY", line 202
ORA-06512: at line 7
00931. 00000 - "missing identifier"
"Unfortunately dbms_utility.comma_to_table is NOT a general purpose utility as its name would suggest. It only works with comma-separated names that are valid database object names, i.e. up to 30 chars beginning with a letter"