DBMS_LOB package. : Clob « Data Type « Oracle PL / SQL

DBMS_LOB package.

  
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE ReverseLOB(
  2    p_InputLocator IN CLOB,
  3    p_OutputLocator IN OUT CLOB,
  4    p_ChunkSize IN NUMBER) AS
  5
  6    v_InputOffset      BINARY_INTEGER;
  7    v_OutputOffset     BINARY_INTEGER;
  8    v_LOBLength        BINARY_INTEGER;
  9    v_CurrentChunkSize BINARY_INTEGER;
 10    e_TrimLength       EXCEPTION;
 11    PRAGMA EXCEPTION_INIT(e_TrimLength, -22926);
 12
 13  BEGIN
 14    v_LOBLength := DBMS_LOB.GETLENGTH(p_InputLocator);
 15
 16    BEGIN
 17      DBMS_LOB.TRIM(p_OutputLocator, v_LOBLength);
 18    EXCEPTION
 19      WHEN e_TrimLength THEN
 20        NULL;
 21    END;
 22
 23    v_InputOffset := 1;
 24    v_OutputOffset := v_LOBLength + 1;
 25
 26    LOOP
 27      EXIT WHEN v_InputOffset > v_LOBLength;
 28
 29      IF (v_LOBLength - v_InputOffset + 1) > p_ChunkSize THEN
 30        v_CurrentChunkSize := p_ChunkSize;
 31      ELSE
 32        v_CurrentChunkSize := v_LOBLength - v_InputOffset + 1;
 33      END IF;
 34
 35      v_OutputOffset := v_OutputOffset - v_CurrentChunkSize;
 36
 37      DBMS_LOB.COPY(p_OutputLocator,
 38                    p_InputLocator,
 39                    v_CurrentChunkSize,
 40                    v_OutputOffset,
 41                    v_InputOffset);
 42
 43      v_InputOffset := v_InputOffset + v_CurrentChunkSize;
 44    END LOOP;
 45  END ReverseLOB;
 46  /

Procedure created.

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

Table created.

SQL>
SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (1, 'abcdefghijklmnopqrstuvwxyz');

1 row created.

SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (2, EMPTY_CLOB());

1 row created.

SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (3, EMPTY_CLOB());

1 row created.

SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (4, EMPTY_CLOB());

1 row created.

SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (5, EMPTY_CLOB());

1 row created.

SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (6, EMPTY_CLOB());

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT key, clob_col
  2    FROM lobdemo
  3    WHERE key BETWEEN 1 AND 6
  4    ORDER BY key;

       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         1
abcdefghijklmnopqrstuvwxyz

         2


         3



       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         4


         5


         6



6 rows selected.

SQL>
SQL> DECLARE
  2    v_Source CLOB;
  3    v_Destination CLOB;
  4    v_Key NUMBER;
  5    CURSOR c_Destinations IS
  6      SELECT key, clob_col
  7      FROM lobdemo
  8      WHERE key BETWEEN 2 and 6
  9      FOR UPDATE;
 10  BEGIN
 11    SELECT clob_col
 12      INTO v_Source
 13      FROM lobdemo
 14      WHERE key = 1;
 15
 16    OPEN c_Destinations;
 17    LOOP
 18      FETCH c_Destinations INTO v_Key, v_Destination;
 19      EXIT WHEN c_Destinations%NOTFOUND;
 20
 21      IF (v_Key = 2) THEN
 22        ReverseLOB(v_Source, v_Destination, 4);
 23      ELSIF (v_Key = 3) THEN
 24        ReverseLOB(v_Source, v_Destination, 2);
 25      ELSIF (v_Key = 4) THEN
 26        ReverseLOB(v_Source, v_Destination, 1);
 27      ELSIF (v_Key = 5) THEN
 28        ReverseLOB(v_Source, v_Destination, 10);
 29      ELSIF (v_Key = 6) THEN
 30        ReverseLOB(v_Source, v_Destination, 30);
 31      END IF;
 32    END LOOP;
 33    CLOSE c_Destinations;
 34    COMMIT;
 35  END;
 36  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT key, clob_col
  2    FROM lobdemo
  3    WHERE key BETWEEN 1 AND 6
  4    ORDER BY key;

       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         1
abcdefghijklmnopqrstuvwxyz

         2
yzuvwxqrstmnopijklefghabcd

         3
yzwxuvstqropmnklijghefcdab


       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         4
zyxwvutsrqponmlkjihgfedcba

         5
uvwxyzklmnopqrstabcdefghij

         6
abcdefghijklmnopqrstuvwxyz


6 rows selected.

SQL>
SQL> DROP TABLE lobdemo;

Table dropped.

SQL>
SQL>

1.	clob data type
2.	Use clob type in PL/SQL
3.	Use dbms_lob.erase to remove value from clob type value
4.	Use dbms_lob.fileopen to open bfile
5.	Creating an Internal LOB Table
6.	Copying Internal LOBs
7.	Appending and Writing to LOBs
8.	Print out the length of the CLOBs previously INSERTed
9.	Compare clob data
10.	Compare two clobs
11.	Append data to clob
12.	Assign string value to clob type variable
13.	Clob type column
14.	Copy clob data
15.	In() function with clob data
16.	Initialize clob
17.	Read clob type data, DBMS_LOB.READ
18.	Use between ... and with clob data
19.	Use clob to store xml data
20.	clob type data as string
21.	erase clob data
22.	use like operator with clob type data

DBMS_LOB package. : Clob « Data Type « Oracle PL / SQL

Related examples in the same category