Read clob data to varchar2 type variable : CLOB « Large Objects « Oracle PL/SQL Tutorial






SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clobData    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 clobData 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    clobVariable CLOB;
  3    charVariable VARCHAR2(50);
  4    offsetPos INTEGER := 1;
  5    amount_var INTEGER := 50;
  6  BEGIN
  7    initClob(clobVariable, id_par);
  8    DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable);
  9    DBMS_OUTPUT.PUT_LINE('charVariable = ' || charVariable);
 10    DBMS_OUTPUT.PUT_LINE('amount_var = ' || amount_var);
 11  END readClob;
 12  /

Procedure created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE write_example(id_par IN INTEGER) IS
  2    clobVariable CLOB;
  3    charVariable VARCHAR2(10) := 'pretty';
  4    offsetPos INTEGER := 7;
  5    amount_var INTEGER := 6;
  6  BEGIN
  7    SELECT clobData INTO clobVariable FROM myTable WHERE id = id_par FOR UPDATE;
  8
  9    readClob(1);
 10    DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable);
 11    readClob(1);
 12
 13  END write_example;
 14  /

Procedure created.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.








34.4.CLOB
34.4.1.Creating Tables Containing CLOB Objects
34.4.2.Initialize CLOB column
34.4.3.Read clob data to varchar2 type variable
34.4.4.Read clob type data, DBMS_LOB.READ
34.4.5.Adding Content to a CLOB
34.4.6.Loading data to the CLOB by using BFILE
34.4.7.Performing basic string operations on CLOBs
34.4.8.Reading and Writing to a CLOB
34.4.9.Obtain Clob data pointer
34.4.10.close Clob data pointer
34.4.11.Open the CLOB
34.4.12.Insert into clob column
34.4.13.Update clob data
34.4.14.Compare date value after to_char() and trim()
34.4.15.Convert string to clob
34.4.16.Copy clob data