Oracle BLOB Inserting and retrieving data:

  How to insert and select data in Oracle BLOB column:

SQL> CREATE TABLE LABORAOBJECTS

(
  NAME        VARCHAR2(128 BYTE)                NOT NULL,
  OBJECTDATA  BLOB
)  2    3    4    5  ;

Table created.

SQL> Insert into LABORAOBJECTS(NAME, OBJECTDATA)  Values ('BlobXYZabcPool.stage', RAWTOHEX('22'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from LABORAOBJECTS;

NAME
--------------------------------------------------------------------------------
OBJECTDATA
--------------------------------------------------------------------------------
BlobXYZabcPool.stage
3232


This will give you the hexadecimal value to get the exact value which we have inserted use the below method:



SQL>  select * from LABORAOBJECTS;

NAME                                                                OBJECTDATA
------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
BlobXYZabcPool.stage                                                3232

SQL> col OBJECTDATA format a56
SQL> /

NAME                                                                OBJECTDATA
------------------------------------------------------------------- --------------------------------------------------------
BlobXYZabcPool.stage                                                3232



SQL> SELECT ASCIISTR(CHR(TO_NUMBER('3232','xxxx'))) from dual;

ASCIISTR(C
----------
22
---------------