SQL>
SQL> CREATE OR REPLACE PROCEDURE dump_long(tab IN VARCHAR2,col IN VARCHAR2, whr IN VARCHAR2 := NULL, pieces IN OUT DBMS_SQL.VARCHAR2S)
2 IS
3 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
4 returnValue PLS_INTEGER;
5
6 TYPE long_rectype IS RECORD (
7 piece_len PLS_INTEGER DEFAULT 2000,
8 pos_in_long PLS_INTEGER DEFAULT 0,
9 one_piece VARCHAR2(2000),
10 one_piece_len PLS_INTEGER
11 );
12
13 rec long_rectype;
14 BEGIN
15 DBMS_SQL.PARSE (cur,'SELECT ' || col || ' FROM ' || tab ||' WHERE ' || NVL (whr, '1 = 1'),DBMS_SQL.NATIVE);
16
17 DBMS_SQL.DEFINE_COLUMN_LONG (cur, 1);
18
19 returnValue := DBMS_SQL.EXECUTE_AND_FETCH (cur);
20
21 IF returnValue > 0
22 THEN
23 LOOP
24 DBMS_SQL.COLUMN_VALUE_LONG(cur,1,rec.piece_len,rec.pos_in_long,rec.one_piece,rec.one_piece_len);
25 EXIT WHEN rec.one_piece_len = 0;
26 pieces (NVL (pieces.LAST, 0) + 1) := rec.one_piece;
27 rec.pos_in_long := rec.pos_in_long + rec.one_piece_len;
28 END LOOP;
29 END IF;
30 DBMS_SQL.CLOSE_CURSOR (cur);
31 END;
32 /
Procedure created.