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

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;
>
> --
> ========================================================================
> =
> --==
> --== 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.

--
Gtrz,

Frank van Bortel