Mega Code Archive
Stored procedure to display the contents of the col_blob and col_clob columns
<![CDATA[
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>
]]>