Oracle PL/SQL/Large Objects/HEXTORAW

HEXTORAW function

 
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>

Insert statement with HEXTORAW

 
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.