0

I want to extract BLOB data and write a file (suggest it should be excel or txt or any other) out of it. A single cell has upto 60k characters. I wanted to write a script that reads whole table with BLOB data and write into a file. In below code ROWNUM works only for one row. what's the alternative? Or is there another script that can help me to achieve my ultimate objective to read BLOB and write file? 

SET SERVEROUTPUT ON;
    DECLARE
       TotalRows NUMBER;
       TotalChar NUMBER;
       CharCounter NUMBER;
    BEGIN
    SELECT count(*) INTO TotalRows FROM <TableName>;
            --dbms_output.Put_line(RC);
            --END;
       FOR RC IN 1..TotalRows
       LOOP
           -----------------Code for Rows starts--------------------------------------------------------------------------------
            dbms_output.Put_line('Row '||RC||' Started.');
            SELECT Length(<ColumnWithBLOBDataType>) INTO TotalChar FROM <TableName> where **Rownum = RC**;
            dbms_output.Put_line('Crossed Char counting query. TotalChar='||TotalChar);
            CharCounter:=TotalChar/2000+1;
            dbms_output.Put_line('Loop will run these many times= '||CharCounter|| ' and Total Chars=' ||TotalChar);
            For CC IN 1..CharCounter
            LOOP
                dbms_output.Put_line('Trip: '||CC);
            END LOOP;  
        -----------------Code for Rows Ends----------------------------------------------------------------------------------------
            TotalChar :=0;
            dbms_output.Put_line('Row '|| RC||' Done. TotalChar='|| TotalChar);
       END LOOP;
       dbms_output.Put_line('Exited loop 1.');
    END;
JNevill
  • 46,980
  • 4
  • 38
  • 63
user7345601
  • 11
  • 1
  • "writes it into a file": do you want to write all blobs into one, single, big file? – wolφi Jul 02 '18 at 12:37
  • BLOB data is basically application configuration data which I want to read and want to compare with same configuration after changes to pin point all the changes made. Plz suggest if there is a better way to achieve that. I was thinking to write that in a file and compare with another file generated after changes. Thanks! – user7345601 Jul 04 '18 at 06:41
  • The max chars in a cell i saw was 60k chars. And configuration file that is created by the application is of less than 50MB. Please suggest feasible/best solution considering I am not an expert in programming howeever can do a little bit coding in PL SQL, VB, .net etc. – user7345601 Jul 04 '18 at 06:53
  • If it's configuration data, then `CLOB` might be a better datatype than `BLOB` which is for binary data. – wolφi Jul 04 '18 at 07:08
  • Personally, I'd probably leave the data in the database and make a snapshot of the before state: `CREATE TABLE mybefore AS SELECT * FROM mytable`. After the change, I'd compare the table mybefore and mytable to let SQL find which cells have changed... – wolφi Jul 04 '18 at 07:09
  • Thanks Mate! However I think we will be able to find where it changed but not what changed. Table has 4 columns and only 23 rows. BLOB column has 22k to characters 60k hexadecimal characters and I assume lot of objects are contained within one cell. Problem is we are not aware of data and its structure. Is there a way to compare two tables? – user7345601 Jul 04 '18 at 09:16
  • Yes, please see [this question](https://stackoverflow.com/questions/47676737/compare-blob-against-other-blobs-in-oracle-database). Ideally, you would have some sort of `diff` comparison, but I don't know of one. – wolφi Jul 05 '18 at 08:33

1 Answers1

1

You normally don't use ROWNUM to select rows from a table. It's not safe and not necessary. Normally, you can do it with a single FOR SELECT loop:

DECLARE
   CharCounter NUMBER;
   part VARCHAR2(30000);
   offset NUMBER;
BEGIN
  FOR r IN (SELECT c,
                   rownum as rc,
                   dbms_lob.getlength(c) as totalchar
              FROM mytable)
  LOOP
    -----------------Code for Rows starts--------------------------------------------------------------------------------
    dbms_output.put_line('Row '||r.rc||' Started.');

    dbms_output.put_line('Crossed Char counting query. TotalChar='||r.totalchar);

    offset := 1;
    WHILE (offset <= r.totalchar) LOOP
      part := dbms_lob.substr(r.c, 20000, offset);
      offset := offset + length(part);      
      dbms_output.put(part);
    END LOOP;
    dbms_output.put_line('');

  END LOOP;
END;
/
wolφi
  • 8,091
  • 2
  • 35
  • 64
  • Thanks! I will try this and will let you know. – user7345601 Jul 04 '18 at 06:55
  • Mate I tried this, it works, ie it goes through all the rows however due to Character String Buffer, it gives error (ORA-06502: PL/SQL: numeric or value error: character string buffer too small). – user7345601 Jul 04 '18 at 08:44
  • Mate I tried this, it works, i.e. it goes through all the rows however due to Character String Buffer, it gives error (ORA-06502: PL/SQL: numeric or value error: character string buffer too small. ORA-06512: at line 13 06502. 00000 - "PL/SQL: numeric or value error%s"). If i decrease 20000 to 15000 in (part := dbms_lob.substr(r.DATA, 20000, offset), it returns 11 rows along with this same error. Also, I tried to use utl_raw.cast_to_varchar2 with dbms_lob.substr(r.DATA, 20000, offset) at r.DATA, then it has same error as above with zero rows. – user7345601 Jul 04 '18 at 08:49
  • This might be a multibyte character problem. The max length of a PL/SQL VARCHAR2 is 32767. A character can have max 4 bytes. Therefore I'd try with a buffer size of 8000. – wolφi Jul 04 '18 at 09:01
  • And, this answer shows only how to read the data from your table. It's not a good method to write to one or more files... – wolφi Jul 04 '18 at 09:02
  • Thanks, Any way to create the file please? – user7345601 Jul 05 '18 at 08:15
  • That's a separate question ;-) It can be done with `UTL_FILE`, but the file needs to be reachable from the database server. – wolφi Jul 05 '18 at 08:27
  • Your welcome. Please have a look at https://stackoverflow.com/help/someone-answers – wolφi Jul 06 '18 at 09:15