This block demonstrates the use of DBMS_LOB.COPY. : dbms_lob « System Packages « Oracle PL / SQL






This block demonstrates the use of DBMS_LOB.COPY.

  
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE lobdemo (
  2    key NUMBER,
  3    clob_col CLOB,
  4    blob_col BLOB);

Table created.

SQL>
SQL>
SQL> DECLARE
  2    v_Row100 lobdemo%ROWTYPE;
  3    v_Row101 lobdemo%ROWTYPE;
  4  BEGIN
  5    SELECT *
  6      INTO v_Row100
  7      FROM lobdemo
  8      WHERE key = 100;
  9    SELECT *
 10      INTO v_Row101
 11      FROM lobdemo
 12      WHERE key = 101
 13      FOR UPDATE;
 14
 15    DBMS_OUTPUT.PUT_LINE('Initial values of row 101:');
 16    LOBUtils.Print(v_Row101.clob_col);
 17    LOBUtils.Print(v_Row101.blob_col);
 18
 19    DBMS_LOB.COPY(v_Row101.clob_col, v_Row100.clob_col, 26, 50, 1);
 20
 21    DBMS_LOB.COPY(v_Row101.blob_col, v_Row100.blob_col, 5, 2, 1);
 22
 23    DBMS_OUTPUT.PUT_LINE('After copy, values of row 101:');
 24    LOBUtils.Print(v_Row101.clob_col);
 25    LOBUtils.Print(v_Row101.blob_col);
 26  END;
 27  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5


SQL>
SQL> drop table lobdemo;

Table dropped.

SQL>

   
  








Related examples in the same category

1.Use dbms_lob.getchunksize to get the clob column size
2.Call dbms_lob.write to write value to clob type value
3.Use dbms_lob.writeappend to append value to clob type value
4.Use dbms_lob.compare to compare
5.Use dbms_lob.compare to compare clob type value with offset
6.Use dbms_lob for load clob data from file
7.This block demonstrates DBMS_LOB.GETLENGTH.
8.DBMS_LOB.SUBSTR: Select the first 50 characters of clob_col, and the first 25 bytes of blob_col, for each row.
9.Use DBMS_LOB.GETLENGTH to get the length of a clob type variable
10.DBMS_LOB.CREATETEMPORARY
11.DBMS_LOB.ERASE
12.DBMS_LOB.FREETEMPORARY
13.DBMS_LOB.ISTEMPORARY
14.DBMS_LOB.READ
15.DBMS_LOB.WRITE
16.This script tests the DBMS_LOB.LOADFROMFILE procedure