Oracle PL/SQL/Large Objects/Insert

LOB manipulations using DML statements.

   <source lang="sql">

SQL> SQL> set serveroutput on SQL> SQL> SQL> CREATE TABLE lobdemo (

 2    key NUMBER PRIMARY KEY,
 3    clob_col  CLOB,
 4    blob_col  BLOB,
 5    bfile_col BFILE
 6  );

Table created. SQL> SQL> INSERT INTO lobdemo (key, clob_col, blob_col, bfile_col)

 2    VALUES (50, "This is a character literal",
 3                 HEXTORAW("FEFEFEFEFEFEFEFEFEFE"),
 4                 NULL);

1 row created. SQL> SQL> INSERT INTO lobdemo (key, clob_col, blob_col, bfile_col)

 2    VALUES (51, "This is another character literal",
 3                 HEXTORAW("ABABABABABABABABABAB"),
 4                 NULL);

1 row created. SQL> SQL> drop table lobdemo; Table dropped. SQL>

</source>
   
  

Use "Insert ... select" statement with CLOB, BLOB and BFILE

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE lobdemo (

 2    key NUMBER PRIMARY KEY,
 3    clob_col  CLOB,
 4    blob_col  BLOB,
 5    bfile_col BFILE
 6  );

Table created. SQL> SQL> INSERT INTO lobdemo (key, clob_col, blob_col, bfile_col)

 2    VALUES (50, "This is a character literal",
 3                 HEXTORAW("FEFEFEFEFEFEFEFEFEFE"),
 4                 NULL);

1 row created. SQL> SQL> INSERT INTO lobdemo (key, clob_col, blob_col, bfile_col)

 2    VALUES (51, "This is another character literal",
 3                 HEXTORAW("ABABABABABABABABABAB"),
 4                 NULL);

1 row created. SQL> SQL> SQL> INSERT INTO lobdemo

 2    SELECT key + 10, clob_col, blob_col, NULL
 3      FROM lobdemo
 4      WHERE key IN (50, 51);

2 rows created. SQL> SQL> drop table lobdemo; Table dropped. SQL>

</source>