Stored procedure to display the contents of the col_blob and col_clob columns : BLOB « PL SQL Data Types « Oracle PL/SQL Tutorial

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

SQL>
SQL> create table myTable
  2  (key NUMBER PRIMARY KEY
  3  ,col_blob BLOB
  4  ,col_clob CLOB);

Table created.

SQL>
SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(1, HEXTORAW('101F'), 'ZYXW');

1 row created.

SQL>
SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(2, HEXTORAW('111101F'), 'ABCD');

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_inmyTable
  2  IS
  3          v_key1          myTable.key%TYPE;
  4          blobValue1         myTable.col_blob%TYPE;
  5          clobValue1         myTable.col_clob%TYPE;
  6          v_key2          myTable.key%TYPE;
  7          blobValue2         myTable.col_blob%TYPE;
  8          clobValue2         myTable.col_clob%TYPE;
  9          v_buffer        VARCHAR2(1000);
 10          v_offset        NUMBER;
 11          v_amount        NUMBER;
 12  BEGIN
 13          SELECT key, col_blob, col_clob
 14          INTO v_key1, blobValue1, clobValue1
 15          FROM myTable
 16          WHERE key = 1;
 17
 18          v_amount := 80;
 19          v_offset := 1;
 20          DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer);
 21          DBMS_OUTPUT.PUT_LINE ('Clob Contents => ' ||  v_buffer);
 22
 23          v_amount := 80;
 24          v_offset := 1;
 25          DBMS_LOB.READ (blobValue1, v_amount, v_offset, v_buffer);
 26          DBMS_OUTPUT.PUT_LINE ('Blob Contents => ' ||  v_buffer);
 27  END;
 28  /

Procedure created.

SQL>
SQL> exec sp_inmyTable
Clob Contents => ZYXW
Blob Contents => 101F

PL/SQL procedure successfully completed.

SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>