Re: Re: read BLOB / insert into table in plsql ?

From: Ian Cary <Ian.Cary_at_ons.gsi.gov.uk>
Date: Thu, 15 Jul 2004 11:07:37 +0100
Message-ID: <OF6F6EA0F4.B8B40BF0-ON80256ED2.00371A3E-80256ED2.0037A169@ons.gov.uk>

Hopefully this might help.

Assuming that the BLOB column contains ordinary csv data rather then excel formatted data. e.g just contains the following

1,fred
2,bill
3,george

and also that the length of each row and column is < 4000 bytes the follwing piece of code will strip out the rows and column values.

You should be able to adapt it to suit your needs.

declare
  blob_col blob;

  raw_cr     raw(100) := utl_raw.cast_to_raw(chr(10));
  csv_line   varchar2(4000);
  col_val    varchar2(4000);
  line_start integer := 1;
  line_end   integer := 1;
  get_lines  boolean := TRUE;
  x          integer := 0;
  col_start  integer := 1;
  col_end    integer := 1;
  get_cols   boolean := TRUE;
  y          integer := 0;

begin
  select filedata
  into blob_col
  from test_blob_data
  where filename = 'test.csv';
  while get_lines loop
    x := x + 1;
    line_end := dbms_lob.instr(blob_col,raw_cr,line_start);     if nvl(line_end,0) = 0 then

       line_end := dbms_lob.getlength(blob_col)+1;
       get_lines := FALSE;

    end if;
    csv_line :=
utl_raw.cast_to_varchar2(dbms_lob.substr(blob_col,line_end-line_start,line_start));

    line_start := line_end + 1;
    col_start := 1;
    if length(csv_line) > 0 then

       get_cols  := TRUE;
       y := 0;
       while get_cols loop
         y:= y + 1;
         col_end := instr(csv_line,',',col_start);
         if nvl(col_end,0) = 0 then
            col_end := length(csv_line)+1;
            get_cols := FALSE;
         end if;
         col_val := substr(csv_line,col_start,col_end-col_start);
         dbms_output.put_line('Row '||x||'   Column '||y||'   Value
'||col_val);
         col_start := col_end + 1;
       end loop;
      end if;

  end loop;
end;

/

Row 1 Column 1 Value 1
Row 1 Column 2 Value fred
Row 2 Column 1 Value 2
Row 2 Column 2 Value bill
Row 3 Column 1 Value 3
Row 3 Column 2 Value george

For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

---

Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications

---

Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics

---

---

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

---

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 15 2004 - 05:04:38 CDT