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_inmyTable2
2 IS
3
4 v_key1 myTable.key%TYPE;
5 blobValue1 myTable.col_blob%TYPE;
6 clobValue1 myTable.col_clob%TYPE;
7 v_key2 myTable.key%TYPE;
8 blobValue2 myTable.col_blob%TYPE;
9 clobValue2 myTable.col_clob%TYPE;
10 v_buffer VARCHAR2(1000);
11 v_offset NUMBER;
12 v_amount NUMBER;
13 v_dest_offset NUMBER := 1;
14 v_src_offset NUMBER := 1;
15
16
17 BEGIN
18
19 SELECT key, col_blob, col_clob
20 INTO v_key1, blobValue1, clobValue1
21 FROM myTable
22 WHERE key = 1
23 FOR UPDATE;
24
25 SELECT key, col_blob, col_clob
26 INTO v_key2, blobValue2, clobValue2
27 FROM myTable
28 WHERE key = 2
29 FOR UPDATE;
30
31 v_amount := 80;
32 v_offset := 1;
33 DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer);
34 DBMS_OUTPUT.PUT_LINE ('Clob1 Contents => ' || v_buffer);
35
36 v_amount := 80;
37 v_offset := 1;
38 DBMS_LOB.READ (clobValue2, v_amount, v_offset, v_buffer);
39 DBMS_OUTPUT.PUT_LINE ('Clob2 Contents => ' || v_buffer);
40
41 v_amount := 80;
42 v_offset := 1;
43 DBMS_LOB.COPY (clobValue1, clobValue2, v_amount, v_dest_offset, v_src_offset);
44
45 v_amount := 80;
46 v_offset := 1;
47 DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer);
48 DBMS_OUTPUT.PUT_LINE ('Clob1 Contents => ' || v_buffer);
49
50 v_amount := 80;
51 v_offset := 1;
52 DBMS_LOB.READ (clobValue2, v_amount, v_offset, v_buffer);
53 DBMS_OUTPUT.PUT_LINE ('Clob2 Contents => ' || v_buffer);
54
55 END;
56 /
Procedure created.
SQL> exec sp_inmyTable2
Clob1 Contents => ZYXW
Clob2 Contents => ABCD
Clob1 Contents => ABCD
Clob2 Contents => ABCD
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>