Update of table from .csv file

[KL1]

I need to update specific columns on a table from data held in a csv file. My understanding is that SQL*Loader cannot do this. Is there any quick and easy way to do it or is the best way to use UTL_FILE functionality.

Thanks in advance
Karen

 

[sem]

You may load your data into some staging table and then make all updates using that table. Another way is to create external table (if your Oracle is modern enough) and do the same.

Regards, Dima

http://seminihin.narod.ru

 

[KL1]

Thought I might have to do something like that.
Thanks for you help.

Karen

 

[HimanB]

Hi Karen,
Use following code to Convert the each line of your cSv file into an array and then read the array to Insert into table.
You willhave add code in this package to Open your CSV file by using UTL_FILE, read each line and pass the same to Csv_To_Array procedure in pv_Csv_String variabe.

Note: Do not forget to flush array after insertion of Each record.

HTH

Regards
Himanshu

CREATE OR REPLACE PACKAGE gen_util IS
  -- declaration used for Csv_To_Array
  type Array_t is table of varchar2(200) ;
  --
  /* Example use
    declare
      l_array gen_util.array_t ;
      li_count binary_integer ;
    begin
      gen_util.csv_to_array('a,b,c,d,e,f,g,h,i', li_count, l_array, ',') ;
      for a in 1..l_array.count loop
        dbms_output.put_line(to_char(a) || ' ' || l_array(a)) ;
      end loop ;
    end ;
    -- gives the output
       a
       b
       c
       d
       e
       f
       g
       h
       i
*/
  procedure Csv_To_Array(
    pv_Csv_String        in  varchar2,
    pi_Count             out binary_integer,
    pa_Array             out array_t,
    pv_Separator         in  varchar2        := ','
  ) ;

END gen_util;
/

CREATE OR REPLACE PACKAGE BODY gen_util IS
  procedure Csv_To_Array(
    pv_Csv_String        in  varchar2,
    pi_Count             out binary_integer,
    pa_Array             out array_t,
    pv_Separator         in  varchar2        := ','
  ) is
    li_start_separator pls_integer     := 0 ;
    li_stop_separator  pls_integer     := 0 ;
    li_length          pls_integer     := 0 ;
    li_idx             binary_integer  := 0 ;
    lb_quote_enclosed  boolean         := false ;
    li_offset          pls_integer     := 1 ;
  begin
    pa_array := array_t() ;
    li_length          := length(pv_Csv_String) ;
    if li_length > 0 then
      loop
        li_Idx := li_Idx + 1 ;
        --
        lb_quote_enclosed := false ;
        if substr(pv_Csv_String, li_start_separator + 1, 1) = '"' then
          lb_quote_enclosed := true ;
          li_offset := 2 ;
          li_stop_Separator := instr(pv_Csv_String, '"', li_start_Separator + li_offset, 1) ;
        else
          li_offset := 1 ;
          li_stop_Separator := instr(pv_Csv_String, pv_Separator, li_start_Separator + li_offset, 1) ;
        end if ;
        if li_stop_Separator = 0 then
          li_stop_Separator := li_length + 1 ;
        end if ;
        --
        Pa_Array.Extend ;
        Pa_Array(li_idx) := (substr(
          pv_Csv_String, li_start_Separator + li_offset,
          (li_stop_Separator - li_start_Separator - li_offset)
        )) ;
        --
        exit when li_stop_Separator >= li_length ;
        if lb_quote_enclosed then
          li_stop_separator := li_stop_separator + 1 ;
        end if ;
        li_start_Separator := li_stop_Separator ;
      end loop ;
    end if ;
    pi_count := li_idx ;
  end Csv_To_Array ;
END gen_util;
/