Oracle PL/SQL/Large Objects/HEXTORAW

Материал из SQL эксперт
Перейти к: навигация, поиск

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>