Read clob type data, DBMS_LOB.READ : CLOB « Large Objects « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Large Objects
3. CLOB

SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
  2  BEGIN
  3    SELECT clob_column INTO clob_par FROM myTable WHERE id = id_par;
  4  END initClob;
  5  /

Procedure created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
  2    clob_var CLOB;
  3    char_buffer_var VARCHAR2(50);
  4    offset INTEGER := 1;
  5    amount_var INTEGER := 50;
  6  BEGIN
  7    initClob(clob_var, id_par);
  8    DBMS_LOB.READ(clob_var, amount_var, offset, char_buffer_var);
  9    DBMS_OUTPUT.PUT_LINE('char_buffer_var = ' || char_buffer_var);
 10    DBMS_OUTPUT.PUT_LINE('amount_var = ' || amount_var);
 11  END readClob;
 12  /

Procedure created.

SQL>
SQL> drop table myTable;

Table dropped.