Save data to clob type column : CLOB « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. CLOB

SQL>
SQL> set echo on
SQL>
SQL> create global temporary table myTable
  2  ( id    int primary key,
  3    c_lob clob,
  4    b_lob blob
  5  )
  6  /

Table created.

SQL>
SQL> create sequence myTable_seq;

Sequence created.

SQL>
SQL> create or replace function to_blob( p_cname in varchar2,
  2                    p_tname in varchar2,
  3                    p_rowid in rowid ) return blob
  4  as
  5      l_blob blob;
  6      l_id   int;
  7  begin
  8      select myTable_seq.nextval into l_id from dual;
  9
 10      execute immediate
 11         'insert into myTable (id,b_lob)
 12          select :id, to_lob( p_cname )
 13            from ' || p_tname ||
 14         ' where rowid = :rid '
 15       using IN l_id, IN p_rowid;
 16
 17      select b_lob into l_blob from myTable where id = l_id ;
 18
 19      return l_blob;
 20  end;
 21  /

Function created.

SQL>
SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL> drop sequence myTable_seq;

Sequence dropped.