SQL & PL/SQL

Your V3 should be a Record Type and not a Ref Cursor. And you should not try to OPEN V2.

Ok, But I donot want to use the record type as there are approx 50 columns we are selecting in the Ref cursor  so it will be very difficult to maintain this.

Is there any other way other than the record type.

No there is no short cut. You need to define them.

Here is an example.

SQL> create or replace package emp_pkg
  2  as
  3    type emp_rec is record (empno emp.empno%type, ename emp.ename%type);
  4    type emp_rc is ref cursor return emp_rec;
  5    procedure get_emp (p_deptno in emp.deptno%type, p_emp_rc out emp_rc);
  6  end;
  7  /

Package created.

SQL> create or replace package body emp_pkg
  2  as
  3    procedure get_emp (p_deptno in emp.deptno%type, p_emp_rc out emp_rc)
  4    is
  5    begin
  6       open p_emp_rc
  7       for
  8       select empno, ename
  9         from emp
 10        where deptno = p_deptno;
 11    end;
 12  end;
 13  /

Package body created.

SQL> declare
  2    l_emp_rc emp_pkg.emp_rc;
  3    l_emp_rec l_emp_rc%rowtype;
  4  begin
  5    emp_pkg.get_emp(10, l_emp_rc);
  6
  7    loop
  8      fetch l_emp_rc into l_emp_rec;
  9      exit when l_emp_rc%notfound;
 10      dbms_output.put_line
 11      (
 12          'EmpNo ' || rpad(to_char(l_emp_rec.empno), 10, ' ') ||
 13          'Ename ' || l_emp_rec.ename
 14      );
 15    end loop;
 16    close l_emp_rc;
 17  end;
 18  /
EmpNo 7782      Ename CLARK
EmpNo 7839      Ename KING

PL/SQL procedure successfully completed.

SQL>

Thanks ,

So Can I conclude I have to always declare the Record variable or the same no of individual variables to fetch the data.

Regards,
Papi

960593 wrote:

Thanks ,

So Can I conclude I have to always declare the Record variable or the same no of individual variables to fetch the data.


Regards,
Papi

Yes

In very, very rare cases, we can avoid explicitly defining record structure, using generic code.

But this style of work is very complex and should generally be avoided.

Below is an example of how it might look (QUEST Pipelines tip of the month: www.quest-pipelines.com - tip 09.2007).

Regards

Dynamic Ref Cursor with Dynamic Fetch - an 11g version

Sometimes there is a problem as follows:

We've got a function that, based on dynamically generated query, returns a ref cursor variable.

Now we want to use this ref cursor variable in our procedure, but we don't know the record structure.

The problem is how to make a "FETCH l_ref_cur INTO record_variable" when we don't know the record variable structure.

This is a hard problem, because ref cursors do not (directly) support description!

We are going to use "a good old" DBMS_SQL package and its procedure DESCRIBE_COLUMNS in order to make an unknown record variable.

This is an 11g version of my Oracle Magazine tip from 2003: "Dynamic Ref Cursor with Dynamic Fetch"

http://www.oracle.com/technology/oramag/code/tips2003/042003.html

Prior to 11g calling function must return not only ref cursor, but the original query also.

In 11g we can use new features DBMS_SQL.TO_CURSOR_NUMBER and DBMS_SQL.TO_REFCURSOR

(to convert ref cursor to DBMS_SQL cursor and vice versa) - now the calling function can return only the ref cursor.

1. "Generic" package

First we are going to make a "dyn_fetch" package.

Procedure "describe_columns" creates "g_record_def" dynamic record structure, with column names as col_1 (col_2 ...),

because SELECT clause in the query can be without aliases (for example "SELECT deptno || dname FROM dept").

CREATE OR REPLACE PACKAGE dyn_fetch IS

   g_count      NUMBER;

   g_record_def VARCHAR2 (32000);

   PROCEDURE describe_columns (p_ref_cur IN OUT SYS_REFCURSOR);

   PROCEDURE fetch_ref_cur (

      p_ref_cur SYS_REFCURSOR,

      p_process_def VARCHAR2);

END;

/

CREATE OR REPLACE PACKAGE BODY dyn_fetch IS

   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 (p_ref_cur IN OUT SYS_REFCURSOR) IS

      l_cur           INTEGER;

      l_desc_tab      DBMS_SQL.DESC_TAB3; -- prije DBMS_SQL.DESC_TAB;

      l_type          VARCHAR2 (100);

      l_col_type      PLS_INTEGER;

      l_col_max_len   PLS_INTEGER;

      l_col_precision PLS_INTEGER;

      l_col_scale     PLS_INTEGER;

   BEGIN

      l_cur := DBMS_SQL.TO_CURSOR_NUMBER (p_ref_cur); -- new feature in 11g

      DBMS_SQL.DESCRIBE_COLUMNS3 (l_cur, g_count, l_desc_tab); -- prije DBMS_SQL.DESCRIBE_COLUMNS

      p_ref_cur := DBMS_SQL.TO_REFCURSOR (l_cur); -- new feature in 11g

      g_record_def := NULL;

      FOR i IN 1..g_count LOOP

         l_col_type      := l_desc_tab(i).col_type;

         l_col_max_len   := l_desc_tab(i).col_max_len;

         l_col_precision := l_desc_tab(i).col_precision;

         l_col_scale     := l_desc_tab(i).col_scale;

         IF    l_col_type = varchar2_type THEN

            l_type := 'VARCHAR2(' || l_col_max_len || ')';

         ELSIF l_col_type = number_type THEN

            IF l_col_precision = 0 THEN

               l_type := 'NUMBER';

            ELSE

               l_type := 'NUMBER(' || l_col_precision || ',' || l_col_scale || ')';

            END IF;

         ELSIF l_col_type = date_type THEN

            l_type := 'DATE';

         ELSIF l_col_type = rowid_type THEN

            l_type := 'ROWID';

         ELSIF l_col_type = char_type THEN

            l_type := 'CHAR(' || l_col_max_len || ')';

      -- ELSIF  l_col_type = ...

            -- long_type, raw_type ...

         END IF;

         g_record_def := g_record_def || ' col_' || i || ' ' || l_type || ',';

      END LOOP;

      g_record_def := RTRIM (g_record_def, ',');

   END;

   PROCEDURE fetch_ref_cur (

      p_ref_cur     SYS_REFCURSOR,

      p_process_def VARCHAR2)

   IS

      l_statement VARCHAR2 (32000);

   BEGIN

      l_statement :=

      '  DECLARE

            TYPE record_t IS RECORD (' || dyn_fetch.g_record_def || ');

            l_record  record_t;

         BEGIN

            LOOP

               FETCH :p_ref_cur INTO l_record;

               EXIT WHEN :p_ref_cur%NOTFOUND;' ||

               p_process_def || '

            END LOOP;

            CLOSE :p_ref_cur;

         END;

      ';

      EXECUTE IMMEDIATE l_statement USING p_ref_cur; -- works in 10g

   END;

END;

/

2. Function (or procedure) that returns ref cursor

CREATE OR REPLACE FUNCTION test_ref_cur RETURN SYS_REFCURSOR IS

   l_query   VARCHAR2 (32000);

   l_ref_cur SYS_REFCURSOR;

BEGIN

   l_query :=

   '  SELECT e.empno,  e.ename,

             e.deptno, d.dname

        FROM emp  e,

             dept d

       WHERE e.deptno = d.deptno

   ';

   OPEN l_ref_cur FOR l_query;

   RETURN l_ref_cur;

END;

/

3. Procedure that uses function that returns ref cursor

Procedure first calls "test_ref_cur" function that returns ref cursor.

Then it calls the "dyn_fetch.describe_columns" procedure in order to get the dynamically generated record structure,

and "dyn_fetch.fetch_ref_cur" procedure, sending two parameters: ref cursor and process definition through the (internal) "process_def" function (in this case to show up rows with DBMS_SQL.PUT_LINE).

CREATE OR REPLACE PROCEDURE test_fetch_ref_cur IS

   l_ref_cur SYS_REFCURSOR;

   FUNCTION process_def RETURN VARCHAR2 IS

      l_process_def VARCHAR2 (32000);

   BEGIN

      l_process_def := 'DBMS_OUTPUT.PUT_LINE (';

      FOR i IN 1 .. dyn_fetch.g_count LOOP

         l_process_def := l_process_def || ' l_record.col_' || i || ' || ''>>'' || ';

      END LOOP;

      l_process_def := RTRIM (l_process_def, ' || ''>>'' || ') || ');';

      RETURN l_process_def;

   END;

BEGIN

   l_ref_cur := test_ref_cur;

   dyn_fetch.describe_columns (l_ref_cur);

   dyn_fetch.fetch_ref_cur (

      p_ref_cur     => l_ref_cur,

      p_process_def => process_def);

END;

/

We can test this with:

SET SERVEROUTPUT ON;

EXECUTE test_fetch_ref_cur;

Tell us what PROBLEM you are trying to solve. Then tell us why you are using this method to try to solve that problem.

Your code has so many issues it couldn't possibly work.

PROCEDURE monetary_instrument_extract(cdate IN CHAR, monInstrumentExtract_cv OUT AtchleyExtractCurType);

That defines a procedure with TWO parameters.

Atchley_Mir_Extract.Monetary_Instrument_Extract('06-18-14',v2);

That calls a the procedure and passes ONLY ONE parameter.

How can that possibly work?

FETCH v2 INTO v3 ;

Huh? V2 is a cursor datatype but you have NOT associated ANY query with that cursor.

OPEN V2 FOR select * from emp;

That statement associates a query with the cursor so now I can fetch V2 if I want.