How can I insert into a BLOB column from an insert statement in sqldeveloper?

Question

Is it possible to insert into a BLOB column in oracle using sqldeveloper?

i.e. something like:

insert into mytable(id, myblob) values (1,'some magic here');

Collected

Answer 1

Yes, it's possible, e.g. using the implicit conversion from RAW to BLOB:

insert into blob_fun values(1, hextoraw('453d7a34'));

453d7a34 is a string of hexadecimal values, which is first explicitly converted to the RAW data type and then inserted into the BLOB column. The result is a BLOB value of 4 bytes.

Francophile

Unfortunately this won't work if the BLOB is greater than 2000 bytes - because there is a limitation in Oracle that a char constant can't be greater than 4000 characters. – Endocardium

Answer 2

To insert a VARCHAR2 into a BLOB column you can rely on the function utl_raw.cast_to_raw as next:

insert into mytable(id, myblob) values (1, utl_raw.cast_to_raw('some magic here'));

It will cast your input VARCHAR2 into RAW datatype without modifying its content, then it will insert the result into your BLOB column.

More details about the function utl_raw.cast_to_raw

Sacchariferous

UTL_RAW.CAST_TO_RAW has a limit of 2000 chars. How to insert a value > 2000 chars ? (used on Oracle) – Slurp

You can use too the to_clob function. check this answer: https://mcmap.net/q/390310/-how-to-write-oracle-insert-script-with-one-field-as-clob – Archeology

Answer 3

Using SQL DEVELOPER you can add the record with an insert like this

Insert into MY_TABLE (KEYSTRING,BLOB) values (123, '');

After that you can save blob content (a big string>4000 char for example) into a file.txt

upload it from SQL DEVELOPER

• opening TABLE edit tool (you can open it by searching table from "Find Database Object" tool)
• go in "Data" tab
• search record 123
• edit BLOB column value by pencil icon

• Load Local data and upload file.txt

Executioner

Answer 4

1. insert into mytable(id, myblob) values (1,EMPTY_BLOB);
2. SELECT * FROM mytable mt where mt.id=1 for update
3. Click on the Lock icon to unlock for editing
4. Click on the ... next to the BLOB to edit
5. Select the appropriate tab and click open on the top left.
6. Click OK and commit the changes.

Sun

This case not working. – Vulpecula