Re: Ref cursor in stored proc

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 19 Oct 2008 00:46:19 +0200
Message-ID: <48FA673B.7000406@gmail.com>

Tim Mickelson schrieb:
> Hello
>
> If I call a function from a stored procedure and the function returns
> a ref cursor. The only info I have and want is that this ref cursor has
> a field, "NAME", but I know nothing of all other fields, how do I fetch
> this field name?
>
>
> E.g. this does not work since the ref cursor has many values, not just
> NAME.
>
>
>
> FUNCTION TESTFUNCTION(input_var IN VARCHAR2) RETURN VARCHAR2 IS
> TYPE RecType IS RECORD(nome IMPRESA_PARIX.NAME%TYPE);
> Cursore IMP_REFCUR;
> rec RecType;
> BEGIN
> -- Call function that returns IMP_REFCUR (ref cursor)
> Cursore := SEARCH_PARAMETRICA('MKM S.R.L.');
>
> LOOP
> FETCH Cursore INTO rec;
> DBMS_OUTPUT.PUT_LINE(rec.NAME);
> EXIT WHEN Cursore%NOTFOUND;
> END LOOP;
>
> RETURN NULL;
> END TESTFUNCTION;
If you are on 10gR2 (even 9iR2 it should work), you can do something like this:

SQL> select * from v$version where rownum<=1;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> create or replace package testpkg is

   2 type imp_refcur is ref cursor;
   3 procedure testproc;
   4 end;
   5 /

Package created.

SQL> show err
No errors.
SQL> create or replace package body testpkg is

   2 function testfunction(p_num in number) return imp_refcur is    3 l_cur imp_refcur;
   4 begin
   5 open l_cur for 'select empno,ename name,sal from emp e where e.deptno = ' || p_num;

   6 return l_cur;
   7 end;
   8 procedure testproc is
   9 cursore imp_refcur;
  10 begin

  11      cursore := testfunction(20);
  12      for r in (select extractvalue(t.column_value, '//NAME') name
  13                  from table(xmlsequence(cursore)) t) loop
  14        dbms_output.put_line(r.name);
  15      end loop;

  16 end;
  17 end;
  18 /

Package body created.

SQL> show err
No errors.
SQL> exec testpkg.testproc
SMITH
JONES
SCOTT
ADAMS
FORD PL/SQL procedure successfully completed.

If you are on 11g, you can convert ref cursor into dbms_sql cursor, so you can access field you like in more flexible way

Best regards

Maxim Received on Sat Oct 18 2008 - 17:46:19 CDT