CODEWITHSUNDEEP
sqloracle-databaseblob
Shabar
Shabar

Reputation: 2821

How to insert BLOB into Oracle table via SQL

I wanted to inser below as a BLOB into Oracle table

enter image description here

I looked at this solution as well.Sloution: But problem is pasting these chars in the SQL show ��

INSERT INTO JOB_DETAILS (JOB_DATA) VALUES (hextoraw('��'))

Upvotes: 0

Views: 2764

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11586

It's not the simple, because those are characters not the hex representation of the characters. For example

SQL> host cat data.txt
Hello

SQL> host od -x data.txt
0000000 6548 6c6c 206f 0a0d
0000010

So the hex for Hello appears to be "6548 6c6c 206f 0a0d"

But on this platform (windows) we byte swap data, and have a couple characters to terminate in the end of line. So when I ditch the last two (0a0d) and load it:

SQL> create table t ( b blob);

Table created.

SQL> insert into t values ( hextoraw('65486c6c206f'));

1 row created.

SQL> select utl_raw.cast_to_varchar2(b) from t;

UTL_RAW.CAST_TO_VARCHAR2(B)
-------------------------------------------------------------
eHll o

You can see that the bytes have been swapped. I need to take that into acount

SQL> delete t;

1 row deleted.

SQL> insert into t values ( hextoraw('48656c6c6f20'));

1 row created.

SQL> select utl_raw.cast_to_varchar2(b) from t;

UTL_RAW.CAST_TO_VARCHAR2(B)
-------------------------------------------------------------
Hello

But in reality, you'd rarely add a blob by using hex strings. Presumably your data is stored in a file. In which case, you would load it using DBMS_LOB.LOADBLOBFROMFILE, eg

SQL> set timing on
SQL> declare
  2    l_bfile  bfile;
  3    l_blob   blob;
  4
  5    l_dest_offset integer := 1;
  6    l_src_offset  integer := 1;
  7  begin
  8    insert into test_blob_bas (id, blob_data)
  9    values (1, empty_blob())
 10    return blob_data into l_blob;
 11
 12    l_bfile := bfilename('TEMP', 'myfile.dat'); 
 13    dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
 14    dbms_lob.loadblobfromfile (
 15      dest_lob    => l_blob,
 16      src_bfile   => l_bfile,
 17      amount      => dbms_lob.lobmaxsize,
 18      dest_offset => l_dest_offset,
 19      src_offset  => l_src_offset);
 20    dbms_lob.fileclose(l_bfile);
 21
 22    commit;
 23  end;
 24  /

PL/SQL procedure successfully completed.

Upvotes: 3

Related Questions