ORA-00931: missing identifier error when using DBMS_UTILITY.COMMA_TO_TABLE

[slay...@my-deja.com]

I am going to need to read a file in CSV format to update my database.
I decided to try and use PL/SQL and the DBMS_UTILITY.COMMA_TO_TABLE
procedure to accomplish this.

Before I actually put in the code to read the file, I want to make sure
I understand how th utility works.

When I run the simple procedure detailed
below I am getting a "ORA-00931: missing identifier" error.

Does anyone have any clues to the solution of this?

I am posting this on the comp.database.oracle.server board also.

THE RESULTS OF THE SQL:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 79
ORA-06512: at "SYS.DBMS_UTILITY", line 108
ORA-06512: at "ERICDBA.PR_USER_MAINT", line 50
ORA-06512: at line 2

THE PROCEDURE THAT I AM RUNNING:
create or replace
procedure pr_user_maint(in_file_name in varchar2)

as

rec_in varchar2(4095) := 'A,B,C,1,2,3';
num_items BINARY_INTEGER := 0;
tab_rec_in DBMS_UTILITY.uncl_array;

FUNCTION fn_List_Item_Count(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2
DEFAULT ','
) RETURN NUMBER
IS
nItemCount NUMBER;
BEGIN
IF vList IS NULL
OR SUBSTR(vList,1,LENGTH(vListItemDelimiter)) =
vListItemDelimiter
OR INSTR(vList||vListItemDelimiter,
vListItemDelimiter||vListItemDelimiter) != 0 THEN
RETURN 0;
END IF;
nItemCount := 0;
WHILE INSTR(vList||vListItemDelimiter,vListItemDelimiter,1,
nItemCount + 1) != 0 LOOP
nItemCount := nItemCount + 1;
END LOOP;
RETURN nItemCount;
END fn_List_Item_Count;

--
========================================================================
=
--==
--== M A I N P R O C E S S
--==
--
========================================================================
=
begin
--
/* in_LANId in varchar2,
in_Domain in varchar2,
in_FirstName in varchar2,
in_LastName in varchar2,
in_Email in varchar2,
in_Phone in varchar2,
*/
--
-- Find out how many items are in the csv list
num_items := fn_List_Item_Count(rec_in, ',');
dbms_output.put_line('Num items is ' || num_items);
-- Load up the PL/SQL table with the list
DBMS_UTILITY.comma_to_table
(rec_in,num_items,tab_rec_in);
for i in 1..num_items loop
dbms_output.put_line(tab_rec_in(i));
end loop;
--
end pr_user_maint;

Sent via Deja.com http://www.deja.com/
Before you buy.

[Frank van Bortel]

dbms_output.put_line only accepts strings.
dbms_output.put_line('Num items is ' || num_items); should read:
dbms_output.put_line('Num items is ' || to_char(num_items));
and you seem to call comma_to_table in the wrong way.
I see an array as third (OUT) param, but that may be dependant
on your version

slay...@my-deja.com wrote:

--
Gtrz,

Frank van Bortel

[slay...@my-deja.com]

I found out that the base problem is a bug in dbms_util. The
comma_to_table procedure cannot tokenize strings that begin with a
digit.

If I change my rec_in parameter from this:
rec_in varchar2(4095) := 'A,B,C,1,2,3';

To this:
rec_in varchar2(4095) := 'A,B,C,D,E,F';

Everything works fine.

I had to parse through the file myself because some of my fields (phone
number, zip) start with digits.

Alan

In article <3A0055AF...@home.nl>,

[Eric Givler]

I wrote my own comma to table because of the wierd behavior of dbms_utility.
I think it errors out during a "name tokenize" call or something likethat.

<slay...@my-deja.com> wrote in message news:8tpifi$5n6$1...@nnrp1.deja.com...