|
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>
|