Dynamic Ref Cursor

1. Make the "generic" package.

CREATE OR REPLACE PACKAGE dyn_fetch IS
   TYPE ref_cur_t IS REF CURSOR;

   g_query    VARCHAR2 (32000);
   g_count    NUMBER;
   g_desc_tab DBMS_SQL.DESC_TAB;

   varchar2_type CONSTANT PLS_INTEGER := 1;
   number_type   CONSTANT PLS_INTEGER := 2;
   date_type     CONSTANT PLS_INTEGER := 12;
   rowid_type    CONSTANT PLS_INTEGER := 11;
   char_type     CONSTANT PLS_INTEGER := 96;

   long_type     CONSTANT PLS_INTEGER := 8;
   raw_type      CONSTANT PLS_INTEGER := 23;
   mlslabel_type CONSTANT PLS_INTEGER := 106;
   clob_type     CONSTANT PLS_INTEGER := 112;
   blob_type     CONSTANT PLS_INTEGER := 113;
   bfile_type    CONSTANT PLS_INTEGER := 114;

   PROCEDURE describe_columns;
   FUNCTION record_def RETURN VARCHAR2;
END;

2. Create the package that returns the query and ref cursor.

CREATE OR REPLACE PACKAGE test IS

   PROCEDURE set_query (p_query VARCHAR2 := NULL);
   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t;
END;
/

CREATE OR REPLACE PACKAGE BODY test IS
   PROCEDURE set_query (p_query VARCHAR2 := NULL) IS
      l_query VARCHAR2 (32000) :=
      '  SELECT e.empno, e.ename,'   ||
      '         e.deptno, d.dname'   ||
      '    FROM emp  e,'             ||
      '         dept d'              ||
      '   WHERE e.deptno = d.deptno';
   BEGIN
      IF p_query IS NULL THEN
         dyn_fetch.g_query := l_query;
      ELSE

         dyn_fetch.g_query := p_query;
      END IF;
   END;

   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t IS
      l_ref_cur dyn_fetch.ref_cur_t;
   BEGIN
      OPEN l_ref_cur FOR dyn_fetch.g_query;
      RETURN l_ref_cur;
   END;
END;

By Perry    Popularity  (1757 Views)