Copy clob data
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> CREATE OR REPLACE PROCEDURE copy_example IS
2 clobSrc CLOB;
3 clobDest CLOB;
4 src_offsetPos INTEGER := 1;
5 dest_offsetPos INTEGER := 7;
6 amount_var INTEGER := 5;
7 BEGIN
8 SELECT clobData INTO clobSrc FROM myTable WHERE id = 2;
9 SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE;
10
11 readClob(1);
12 DBMS_LOB.COPY(clobDest, clobSrc, amount_var,dest_offsetPos, src_offsetPos);
13 readClob(1);
14
15 ROLLBACK;
16 END copy_example;
17 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
Related examples in the same category