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

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.B600DF63_at_home.nl>,   Frank van Bortel <fbortel_at_home.nl> wrote:
> 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
>
> slaydawg_at_my-deja.com wrote:
>
> > 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;
> >
> > --
> >

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 01 2000 - 23:10:36 CET