Cursor design and refactoring question

Question

I have many cursors that all return rows with the same fields: a numeric ID field and an XMLType field. Every time I access one of these cursors (each cursor has now got its own function for access), I go through the same pattern:

--query behind cursor is designed to no more than one row.
for rec in c_someCursor(in_searchKey => local_search_key_value) loop
    v_id := rec.ID
    v_someXMLVar := rec.XMLDataField
end loop;

if v_someXMLVar is null then
    /* A bunch of mostly-standard error handling and logging goes here */
end if;

exception
    /* all cursor access functions have the same error-handling */
end;

As the pattern became more obvious, it made sense to centralize it in a single function:

    function fn_standardCursorAccess(in_cursor in t_xmlCursorType, in_alt in XMLType) return XMLType is
            v_XMLData XMLType;
        begin
            dbms_application_info.set_module(module_name => $$PLSQL_UNIT, action_name => 'fn_standardCursorAccess');
            loop
                fetch in_cursor
                    into v_XMLData;
                exit when in_cursor%notfound;
            end loop;
            /*some additional standard processing goes here*/
            return v_XML;
        exception
        /*standard exception handling happens here*/
    end;

The problem I've run into is in calling this function. I now have to call it like this:

open v_curs for select /*blah blah blah*/ where key_field = x and /*...*/;
v_data := fn_standardCursorAccess(v_curs,alt);
close v_curs;

What I'd like to do is call it like this:

open v_curs for c_getSomeData(x);
v_data := fn_standardCursorAccess(v_curs,alt);
close v_curs;

...reason being to minimize the amount of changes to my code (I don't want to have to cut/paste all these cursors to the functions that depend on them, and in the case where multiple functions depend on the same cursor, I'll have to wrap that in a new function).

Unfortunately, this does not work, Oracle returns an error saying

Error: PLS-00222: no function with name 'C_GETSOMEDATA' exists in this scope

Is what I'm trying to do even possible?

(Oracle version is 10.2)

EDIT: I think a better way to describe what I'm doing is pass a reference to an explicit cursor to a function that will perform some common routines on the data returned by the cursor. It appears that I cannot use an open-for statement with an explcit cursor, is there any other way to get a reference to an explicit cursor so I can pass that reference to a function? Maybe there is some other way I could approach this problem?

EDIT: Copying and pasting from my earlier reply to R Van Rijn's reply:

I tried declaring the cursor in the package specification, and referencing it with the package name: open v_curs for PKG.c_getSomeData(x);... This gives me a new error, saying that PKG.c_getSomeData must be a function or array to be used that way.

UPDATE: I spoke to our DBA here, he says it is not possible to have a ref cursor point to an explicit cursor. It looks like I can't do this after all. Bummer. :(

Answer 1

concerning the Error PLS-00222:

An identifier being referenced as a function 'c_getSomeData' was not declared or actually represents another object (for example, it might have been declared as a procedure).

Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure

This means you must create a function that actually returns some value(s).

Answer 2

Does this test script and output represent what you are trying to do? Instead of open v_curs for c_getSomeData(x); I'm setting the cursor variable = to the output from the function.

Our Test Data:

set serveroutput on

--create demo table
drop table company;
create table company 
(
 id number not null,
 name varchar2(40)
);

insert into company (id, name) values (1, 'Test 1 Company');
insert into company (id, name) values (2, 'Test 2 Company');
insert into company (id, name) values (3, 'Test 3 Company');

commit;

Create Packages

create or replace package test_pkg as

  type cursor_type is ref cursor;

  function c_getSomeData(v_companyID number) return cursor_type;

end test_pkg;
/

create or replace package body test_pkg as

  function c_getSomeData(v_companyID number) return cursor_type
  is 
    v_cursor cursor_type;
  begin

    open v_cursor for
    select id,
           name
      from company
     where id = v_companyID;

    return v_cursor;
  end c_getSomeData;

end test_pkg;
/

Run Our Procedure

declare
  c test_pkg.cursor_type;
  v_id company.id%type;
  v_name company.name%type;
begin
  c := test_pkg.c_getSomeData(1);

  loop 
    fetch c
    into  v_id, v_name;
    exit when c%notfound;
    dbms_output.put_line(v_id || ' | ' || v_name);
  end loop;

  close c;

end;
/

1 | Test 1 Company

PL/SQL procedure successfully completed.

Answer 3

I confess to finding your requirements a trifle hard to divine. You have posted a lot of code, but as I suggested in my comment, not the parts which would illuminate the problem. So possibly the following is way off-beam. But it is an interesting issue.

The following code shows how we can define a common, geneneric REF CURSOR, populate it with specific data from different queries, and then process them in a standardised fashion. Again, I apologise if this does not fit your business logic; if such is the case, please edit your question to explain where I have made a bloomer..

Here is the generic ref cursor. ...

create or replace package type_def is
    type xml_rec is record (id number, payload xmltype);
    type xml_cur is ref cursor return xml_rec;
end type_def;
/

and here is the standatd processor

create or replace procedure print_xml_cur 
    ( p_cur in type_def.xml_cur )
is
    lrec type_def.xml_rec;
begin
    loop
        fetch p_cur into lrec;
        exit when p_cur%notfound;
        dbms_output.put_line('ID='||lrec.id);
        dbms_output.put_line('xml='||lrec.payload.getClobVal());
    end loop;
    close p_cur;
end print_xml_cur;
/

Two procedures which return the standard cursor with different data....

create or replace function get_emp_xml
    ( p_id in emp.deptno%type )
    return type_def.xml_cur
is
    return_value type_def.xml_cur;
begin
    open return_value for 
        select deptno
               , sys_xmlagg(sys_xmlgen(ename))
        from emp
        where deptno = p_id
        group by deptno;
    return return_value;
end get_emp_xml;
/

create or replace function get_dept_xml
    ( p_id in dept.deptno%type )
    return type_def.xml_cur
is
    return_value type_def.xml_cur;
begin
    open return_value for 
        select deptno
               , sys_xmlagg(sys_xmlgen(dname))
        from dept
        where deptno = p_id
        group by deptno;
    return return_value;
end get_dept_xml;
/

Now let's put it all together ....

SQL> set serveroutput on size unlimited
SQL>
SQL> exec print_xml_cur(get_emp_xml(40))
ID=40
xml=<?xml
version="1.0"?>
<ROWSET>
<ENAME>GADGET</ENAME>
<ENAME>KISHORE</ENAME>
</ROWSET>


PL/SQL procedure successfully completed.

SQL> exec print_xml_cur(get_dept_xml(20))
ID=20
xml=<?xml version="1.0"?>
<ROWSET>
<DNAME>RESEARCH</DNAME>
</ROWSET>


PL/SQL procedure successfully completed.

SQL>

Answer 4

It appears that what I wanted to do (have an open-for statement reference an existing explicit cursor) is simply not allowed in Oracle. :(