Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG : DBMS_SQL « System Packages « Oracle PL/SQL Tutorial





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.










31.26.DBMS_SQL
31.26.1.dbms_sql.number_table
31.26.2.Close a cursor
31.26.3.Assign date with DBMS_SQL package
31.26.4.Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'
31.26.5.DBMS_SQL.PARSE
31.26.6.An example of using DBMS_SQL.OPEN_CURSOR
31.26.7.Package for running dynamic sql based on DBMS_SQL package
31.26.8.Wrap dbms_sql package
31.26.9.Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
31.26.10.DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
31.26.11.DBMS_SQL.EXECUTE an update statement
31.26.12.Use DBMS_SQL to update a table and get the number of rows updated
31.26.13.DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
31.26.14.Use dynamic SQL to check the business logic
31.26.15.Use DBMS_SQL package to parse math expression
31.26.16.Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
31.26.17.DBMS_SQL.LAST_ERROR_POSITION
31.26.18.Dump query with dbms_sql
31.26.19.Use dbms_sql.describe_columns
31.26.20.Print table with dynamic query