Home » SQL & PL/SQL » SQL & PL/SQL » Read BLOB from Table into CLOB for further processing (Oracle, 19.0.0.0.0, Linux)
Read BLOB from Table into CLOB for further processing [message #689556] Tue, 13 February 2024 18:56 Go to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
I'm just wondering how this might be implemented.

Current Process:

A file is uploaded to a BLOB column in a table and the file is put on our OS system.  An External Table is used to read the contents of the file from the OS.  Those contents are processed.


New Process:

A file is uploaded to a BLOB column in a table.  The file won't exist on the OS system (database/OS going to the cloud and we won't have access to the OS system).  Read the BLOB column and convert the BLOB into a CLOB.  Somehow process the contents of the CLOB.


So, I'm not sure how the CLOB will be used to process the contents.  Read the CLOB line by line and process that line?

Anyone have any ideas on what this process would look like?  Just trying to wrap my head around what is needed.  Can it even be done this way?

Read BLOB from Table
Convert BLOB to CLOB (maybe process each line here; Could an External Table be used to obtain the contents?)
Read the contents of the CLOB line by line
Process each line
Re: Read BLOB from Table into CLOB for further processing [message #689559 is a reply to message #689556] Wed, 14 February 2024 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at DBMS_LOB.CONVERTTOCLOB procedure.

Re: Read BLOB from Table into CLOB for further processing [message #689561 is a reply to message #689559] Wed, 14 February 2024 02:56 Go to previous messageGo to next message
NevemTeve
Messages: 25
Registered: November 2017
Junior Member

if is_this_always_a_textfile then
   if the_content_can_be_represented_in NLS_CHARACTERSET then
       use datatype 'CLOB'
   else
       use datatype 'NCLOB'
   fi
else
   use datatype 'BLOB'
fi
icon14.gif  Re: Read BLOB from Table into CLOB for further processing [message #689563 is a reply to message #689561] Wed, 14 February 2024 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure! But when a table is created, data are there and client don't want to change his table and loading process, choice is limited.

[Updated on: Wed, 14 February 2024 03:27]

Report message to a moderator

Re: Read BLOB from Table into CLOB for further processing [message #689564 is a reply to message #689559] Wed, 14 February 2024 08:50 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Michel Cadot wrote on Wed, 14 February 2024 07:22

Have a look at DBMS_LOB.CONVERTTOCLOB procedure.

Thank you.  I have some code that uses the procedure and creates a CLOB of the contents of the file.  One step completed.

Now, how do I get the contents of that CLOB into a table for processing.  I was doing that using an External Table but that was reading the file off of the OS system.  How would I do this with a CLOB variable?  I need to put the contents into a table for processing.  Read each line of the CLOB?

[Updated on: Wed, 14 February 2024 08:50]

Report message to a moderator

Re: Read BLOB from Table into CLOB for further processing [message #689565 is a reply to message #689564] Wed, 14 February 2024 09:16 Go to previous messageGo to next message
NevemTeve
Messages: 25
Registered: November 2017
Junior Member
Are you using some sort of client-platform, such as OCI, Pro*C, JDBC, PL/SQL?
Re: Read BLOB from Table into CLOB for further processing [message #689566 is a reply to message #689565] Wed, 14 February 2024 09:34 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
PL/SQL
Re: Read BLOB from Table into CLOB for further processing [message #689567 is a reply to message #689564] Wed, 14 February 2024 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Duane wrote on Wed, 14 February 2024 15:50
Michel Cadot wrote on Wed, 14 February 2024 07:22

Have a look at DBMS_LOB.CONVERTTOCLOB procedure.

Thank you.  I have some code that uses the procedure and creates a CLOB of the contents of the file.  One step completed.

Now, how do I get the contents of that CLOB into a table for processing.  I was doing that using an External Table but that was reading the file off of the OS system.  How would I do this with a CLOB variable?  I need to put the contents into a table for processing.  Read each line of the CLOB?

A CLOB is just a string, use SUBSTR, INSTR... in a loop.
Something like:
SQL> declare
  2    txt  clob;
  3    idx  integer;
  4    nl   varchar2(2) := chr(10);
  5  begin
  6    txt := 'Hello,'||nl||'I am Michel.'||nl||'Have a nice day!'; -- replace by your clob loading
  7    idx := instr(txt,nl);
  8    loop
  9      exit when idx = 0;
 10      dbms_output.put_line(substr(txt,1,idx-1));
 11      txt := substr(txt, idx+1);
 12      idx := instr(txt,nl);
 13    end loop;
 14    if txt is not null then dbms_output.put_line(txt); end if;
 15  end;
 16  /
Hello,
I am Michel.
Have a nice day!

PL/SQL procedure successfully completed.
Replace the value of NL by your newline characters depending on your system.

[Updated on: Wed, 14 February 2024 12:16]

Report message to a moderator

Re: Read BLOB from Table into CLOB for further processing [message #689568 is a reply to message #689567] Wed, 14 February 2024 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A better code as it does not write the CLOB over and over until it is exhausted would be:
SQL> declare
  2    txt  clob;
  3    line varchar2(4000);
  4    lg   integer;
  5    i1   integer;
  6    i2   integer;
  7    nl   varchar2(2) := chr(10)||chr(13);
  8    nllg integer := length(nl);
  9  begin
 10    txt := 'Hello,'||nl||'I am Michel.'||nl||'Have a nice day!'; -- replace by your clob loading
 11    lg  := length(txt);
 12    i1  := 1;
 13    loop
 14      i2 := instr(txt,nl,i1);
 15      exit when i2 = 0;
 16      dbms_output.put_line(substr(txt,i1,i2-i1));
 17      i1 := i2+nllg;
 18    end loop;
 19    if i1 < length(txt) then dbms_output.put_line(substr(txt,i1)); end if;
 20  end;
 21  /
Hello,
I am Michel.
Have a nice day!

PL/SQL procedure successfully completed.

[Updated on: Wed, 14 February 2024 12:30]

Report message to a moderator

Re: Read BLOB from Table into CLOB for further processing [message #689569 is a reply to message #689568] Wed, 14 February 2024 13:26 Go to previous message
Duane
Messages: 557
Registered: December 2002
Senior Member
Michel Cadot wrote on Wed, 14 February 2024 18:28

A better code as it does not write the CLOB over and over until it is exhausted would be:
SQL> declare
  2    txt  clob;
  3    line varchar2(4000);
  4    lg   integer;
  5    i1   integer;
  6    i2   integer;
  7    nl   varchar2(2) := chr(10)||chr(13);
  8    nllg integer := length(nl);
  9  begin
 10    txt := 'Hello,'||nl||'I am Michel.'||nl||'Have a nice day!'; -- replace by your clob loading
 11    lg  := length(txt);
 12    i1  := 1;
 13    loop
 14      i2 := instr(txt,nl,i1);
 15      exit when i2 = 0;
 16      dbms_output.put_line(substr(txt,i1,i2-i1));
 17      i1 := i2+nllg;
 18    end loop;
 19    if i1 < length(txt) then dbms_output.put_line(substr(txt,i1)); end if;
 20  end;
 21  /
Hello,
I am Michel.
Have a nice day!

PL/SQL procedure successfully completed.

Thank you.

Here's where I'm at.  Pulling the file from the table and converting it to a CLOB.  I'm trying to use "connect by" to substr the CLOB and then I will need to PIVOT the data to insert that data into three columns in a table.  At that point, I'm back to where I was before using an External Table to read in the data and process it.

My problem with the "connect by" is the LEVEL increases by 1.  Ideally, I would want the level to be 1, 2, 3 FOR EACH ROW.  That way I can do "pivot (min(txt) for line in (1 as value_1, 2 as value_2, 3 as value_3))"

Data:


ID,Term,Recruiter
A01,202420,Name 1
A0102,202420,Name 2
A010203,202420,Name 3
A01020304,202420,Name 4
A01,202420,Name 5
A02,202420,Name 6
A03,202420,Name 7
A04,202420,Name 8
A05,202420,Name 9


Table


CREATE TABLE FILE_CONTENTS
(
  ID        VARCHAR2(10 CHAR),
  TERM      VARCHAR2(6 CHAR),
  RECRUITER VARCHAR2(50 CHAR)
)


Code I have so far.  This code is without a PIVOT.  Still need to exclude the HEADER row.


declare
  
  DestinationClob   clob;
  SourceBlob        blob;
  DestinationOffset integer := 1;
  SourceOffset      integer := 1;
  LanguageContext   integer := dbms_lob.default_lang_ctx;
  Warning           integer;
  Counter           number  := 0;
  Value1            varchar2(10);
  Value2            varchar2(6);
  Value3            varchar2(50);


  begin
    select file
      into SourceBlob
      from file_table
        where id = '1';
    
    dbms_lob.createTemporary(lob_loc => DestinationClob, 
                             cache   => false);

    dbms_lob.converttoclob(dest_lob     => DestinationClob,
                           src_blob     => SourceBlob,
                           amount       => dbms_lob.lobmaxsize,
                           dest_offset  => DestinationOffset,
                           src_offset   => SourceOffset,
                           blob_csid    => dbms_lob.default_csid,
                           lang_context => LanguageContext,
                           warning      => Warning);

    for i in (select regexp_substr(txt, '[^\,]+', 1, level) txt,
                     level line
                from (select dbms_lob.substr(DestinationClob, 4000, 1) txt
                        from dual)
                  connect by level <= regexp_count(DestinationClob, '[^\,]+'))
      loop
        case
          when i.line = 1
            then
              Value1 := i.txt;
                  
          when i.line = 2
            then
              Value2 := i.txt;
                  
          when i.line = 3
            then
              Value3 := i.txt;
                  
            else
              Value1 := '1';
        end case;
            
        counter := counter + 1;
            
        if Counter = 3
          then
            Counter := 0;
                
            insert into file_contents
              (id,
               term,
               recruiter)
           
              values
                (Value1,
                 Value2,
                 Value3);
        end if;
            
        dbms_output.put_line(i.txt||' '||i.line);
      end loop;
      
      commit;

  end;


Version with the PIVOT but it only returns the HEADER row because only the first row is being pivoted because the LEVEL is increasing by 1 (1, 2, 3, 4, 5, 6.....).


declare
  
  DestinationClob clob;
  SourceBlob      blob;
  DestOffset      integer := 1;
  SourceOffset    integer := 1;
  LanguageContext integer := dbms_lob.default_lang_ctx;
  Warning         integer;

  begin
    select file
      into SourceBlob
      from file_table
        where id = '1';
    
    dbms_lob.createTemporary(lob_loc => DestinationClob, 
                             cache   => false);

    dbms_lob.converttoclob(dest_lob     => DestinationClob,
                           src_blob     => SourceBlob,
                           amount       => dbms_lob.lobmaxsize,
                           dest_offset  => DestOffset,
                           src_offset   => SourceOffset,
                           blob_csid    => dbms_lob.default_csid,
                           lang_context => LanguageContext,
                           warning      => Warning);

    for i in (select *
                from (select value_1,
                             value_2,
                             value_3
                        from (select regexp_substr(txt, '[^\,]+', 1, level) txt,
                                     level line
                                from (select dbms_lob.substr(DestinationClob, 4000, 1) txt
                                        from dual)
                                connect by level <= regexp_count(DestinationClob, '[^\,]+'))
                          pivot (min(txt) for line in (1 as value_1, 2 as value_2, 3 as value_3))))
      loop
        dbms_output.put_line(i.value_1||i.value_2);
      end loop;
      
      commit;

  end;

Previous Topic: Xmlattribute not returning tag when the value is null
Next Topic: Traverse CLOB with Connect By
Goto Forum:
  


Current Time: Thu May 23 11:20:42 CDT 2024