Oracle 10: Using Hextoraw to Fill in Blob Data

Oracle 10: Using HEXTORAW to fill in blob data

Apparently you can exceed these limits if you use PL/SQL. It doesn't work if you do the HEXTORAW within the UPDATE statement directly, either--it needs to be done in a separate statement, like this:

DECLARE
  buf RAW(4000); 
BEGIN
  buf := HEXTORAW('C2B97041074...lots of hex...0CC00CD00');
  UPDATE MyTable
     SET blobData = buf
   WHERE ID = 462;
END;

For the life of me I'll never understand some of Oracle's limitations. It's like everything is its own little special case.

How to insert a large hex into a BLOB column?

The problem is transferring the data from C to the PL function since each parameter is limited in size. If the max size of the image is known you can declare a function/procedure with enough parameters and cut up the data. Unfortunately, the RAW data type is limited to 2000 bytes:

CREATE OR REPLACE PROCEDURE insert_blob(key test.id%type, 
        part1 RAW(2000), 
        part2 RAW(2000), 
        part3 RAW(2000), 
        part4 RAW(2000), 
        part5 RAW(2000)),
        part6 RAW(2000)),
        part7 RAW(2000)),
        part8 RAW(2000)),
        part9 RAW(2000)),
        part10 RAW(2000)) AS
    l_blob BLOB;
BEGIN
    DBMS_LOB.createtemporary(l_blob, FALSE);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part1), part1);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part2), part2);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part3), part3);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part4), part4);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part5), part5);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part6), part6);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part7), part7);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part8), part8);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part9), part9);
    DBMS_LOB.writeappend (l_blob, UTL_RAW.length(part10), part10);
    UPDATE test SET qrcodeIMG = l_blob WHERE id = key;
    commit;
   DBMS_LOB.freetemporary(l_blob); 
END;
/

Obviously, this is not the way it is meant to be done. If you can, using a file is a better choice.

EDIT:

Oracle has examples on how to properly write a LOB from C in the LOB chapter of the Pro*C/C++ Precompiler Programmer's Guide.

Insert HEX string as IMAGE in Oracle

your approach is right. its possibly the input HEX that is wrong? how are you getting it.

for example:

SQL> create table img (img blob);

Table created.

SQL> insert into img values (hextoraw('47494638396120002000f700000000000000330000660000990000cc0000ff002b00002b33002b66002b99002bcc002bff0055000055330055660055990055cc0055ff008
0000080330080660080990080cc0080ff00aa0000aa3300aa6600aa9900aacc00aaff00d50000d53300d56600d59900d5cc00d5ff00ff0000ff3300ff6600ff9900ffcc00ffff3300003300333300663300993300cc3300f
f332b00332b33332b66332b99332bcc332bff3355003355333355663355993355cc3355ff3380003380333380663380993380cc3380ff33aa0033aa3333aa6633aa9933aacc33aaff33d50033d53333d56633d59933d5cc3
3d5ff33ff0033ff3333ff6633ff9933ffcc33ffff6600006600336600666600996600cc6600ff662b00662b33662b66662b99662bcc662bff6655006655336655666655996655cc6655ff668000668033668066668099668
0cc6680ff66aa0066aa3366aa6666aa9966aacc66aaff66d50066d53366d56666d59966d5cc66d5ff66ff0066ff3366ff6666ff9966ffcc66ffff9900009900339900669900999900cc9900ff992b00992b33992b66992b9
9992bcc992bff9955009955339955669955999955cc9955ff9980009980339980669980999980cc9980ff99aa0099aa3399aa6699aa9999aacc99aaff99d50099d53399d56699d59999d5cc99d5ff99ff0099ff3399ff669
9ff9999ffcc99ffffcc0000cc0033cc0066cc0099cc00cccc00ffcc2b00cc2b33cc2b66cc2b99cc2bcccc2bffcc5500cc5533cc5566cc5599cc55cccc55ffcc8000cc8033cc8066cc8099cc80cccc80ffccaa00ccaa33cca
a66ccaa99ccaaccccaaffccd500ccd533ccd566ccd599ccd5ccccd5ffccff00ccff33ccff66ccff99ccffccccffffff0000ff0033ff0066ff0099ff00ccff00ffff2b00ff2b33ff2b66ff2b99ff2bccff2bffff5500ff553
3ff5566ff5599ff55ccff55ffff8000ff8033ff8066ff8099ff80ccff80ffffaa00ffaa33ffaa66ffaa99ffaaccffaaffffd500ffd533ffd566ffd599ffd5ccffd5ffffff00ffff33ffff66ffff99ffffccffffff0000000
0000000000000000021f904010000fc002c00000000200020000008ff00371429b2619fc183080dbe899630e140810407366c2870e1c47d103764bc887020135017230e8cc8f1601168251f1661387119ca7dd194edabb3a
c24cb92758c286482b188c5921ca31154267483b29120815e7c03d10e44262f955e84080ad4c88755056e80131528c1a4a0b41601ab72a5d20d490d5a458b30ec489940d31eacda10d4929a0da325d5bb4fae5abf7db94e7
c53446161b673bfb68d28f8a0d0224405421b4896aa4168102923842370099c8146b4561d29da6d91253783d68908ca944881a309323dcd59e24168b535f7150b566051a204bb46232cd0cee2b18bf77d36b87a22b437804
1194f08121a5c972d3f3634155d296ecb8991275156ba6cb568b51111bb2e28f539d5b5a46317967a713893919f05d2cf4b78a5e468beed979032f715449861023a07074c7b016692562595a7547a22a5765b6d6791f6d04
5e51134a158247134d984230504003b'));

1 row created.

SQL> commit;

Commit complete.

then in my PL/SQL IDE (pl/sql developer) i see..

Unable to insert oracle BLOB data greater than 3200 using Insert Statement

Yes. Use stream, not String/byte array to insert BLOB. Something like this:

PreparedStatement ps = conn.prepareStatement("insert into blobs (blob_value) values (?)");
InputStream in = new StringBufferInputStream(aLagrgeStringValue);
ps.setBinaryStream(1,in,(int)in.length());
ps.execute();

Prepare test data on Oracle with blob column

If you do not care that the content of the LOB data is the same, you can use the following (creating one random 10000 bytes lob value):

declare
   v_clob        clob;
   v_blob        blob;
   v_dest_offset integer := 1;
   v_src_offset  integer := 1;
   v_warn        integer;
   v_ctx         integer := dbms_lob.default_lang_ctx;
begin
   for idx in 1..5
   loop
     v_clob := v_clob || dbms_random.string('x', 2000);
   end loop;
   dbms_lob.createtemporary( v_blob, false );
   dbms_lob.converttoblob(v_blob,
                          v_clob,
                          dbms_lob.lobmaxsize,
                          v_dest_offset,
                          v_src_offset,
                          dbms_lob.default_csid,
                          v_ctx,
                          v_warn);
   insert into blob_test (id, data)
     select rownum, v_blob from dual
     connect by level <= 10000;
end;
/

See here an example session:

SQL> create table blob_test (id number primary key, data blob);

Table created.

SQL> declare
  2    v_clob        clob;
  3    v_blob        blob;
  4    v_dest_offset integer := 1;
  5    v_src_offset  integer := 1;
  6    v_warn        integer;
  7    v_ctx         integer := dbms_lob.default_lang_ctx;
  8  begin
  9    for idx in 1..5
 10    loop
 11      v_clob := v_clob || dbms_random.string('x', 2000);
 12    end loop;
 13    dbms_lob.createtemporary( v_blob, false );
 14    dbms_lob.converttoblob(v_blob,
 15                           v_clob,
 16                            dbms_lob.lobmaxsize,
 17                           v_dest_offset,
 18                           v_src_offset,
 19                           dbms_lob.default_csid,
 20                           v_ctx,
 21                           v_warn);
 22
 23    insert into blob_test (id, data)
 24    select rownum, v_blob
 25      from dual
 26     connect by level <= 10000;
 27
 28  end;
 29  /

PL/SQL procedure successfully completed.

SQL> select count(*) , max(length(data)) from blob_test;

  COUNT(*) MAX(LENGTH(DATA))
---------- -----------------
     10000             10000

If you want to have different data you can generate and insert v_lob in a loop instead.

---

Related Topics