Oracle PL/SQL/Large Objects/HEXTORAW
HEXTORAW function
<source lang="sql">
SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 clob_col CLOB, 4 blob_col BLOB);
Table created. SQL> SQL> SQL> DECLARE
2 v_BLOB BLOB; 3 BEGIN 4 INSERT INTO lobdemo (key, blob_col) 5 VALUES(55, HEXTORAW("0011223344556677889900AABBCCDDEEFF")) 6 RETURNING blob_col INTO v_BLOB; 7 8 DBMS_OUTPUT.PUT_LINE( 9 "Length of newly INSERTed BLOB is: " || 10 DBMS_LOB.GETLENGTH(v_BLOB)); 11 END; 12 /
Length of newly INSERTed BLOB is: 17 PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table lobdemo; Table dropped. SQL> SQL>
</source>
Insert statement with HEXTORAW
<source lang="sql">
SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 clob_col CLOB, 4 blob_col BLOB);
Table created. SQL> SQL> INSERT INTO lobdemo (key, clob_col, blob_col)
2 VALUES (50, "This is a character literal", 3 HEXTORAW("FEFEFEFEFEFEFEFEFEFE"));
1 row created. SQL> SQL> INSERT INTO lobdemo (key, clob_col, blob_col)
2 VALUES (51, "This is another character literal", 3 HEXTORAW("ABABABABABABABABABAB"));
1 row created. SQL> SQL> INSERT INTO lobdemo
2 SELECT key + 10, clob_col, blob_col 3 FROM lobdemo 4 WHERE key IN (50, 51);
2 rows created. SQL> SQL> SQL> drop table lobdemo; Table dropped.
</source>