Oracle PL/SQL/Large Objects/Insert

LOB manipulations using DML statements.

 
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>

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

 
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>