Returning cursor from dynamic SQL

[slicendice]

Hi
If I have a function that returns a cursor, is it possible to call that function using dynamic SQL? I know it's possible to call a function using something like:

DECLARE
  vSQL    VARCHAR2(100);
  iParam  INTEGER;
BEGIN
  vSQL   := 'BEGIN some_function(:1) END;';
  iParam := 5;
  EXECUTE IMMEDIATE vSQL USING iParam;
END;

...but I'm not sure how I would handle a cursor that was returned by the function.

Thanks

 

[SantaMufasa]

SliceNDice,

Before I spend any time creating a solution to address your need, I wanted to confirm one of your specifications. Thoughout your post, you mention the term function. Yet the context of your post indicates a procedure.

I'll attempt to differentiate the two, and you can clarify for me:[ul][li]Function: A set of PL/SQL code that has a name and returns a discrete value. You invoke the named function in contexts that you would for data expressions. You can refer to a stored function in either standard SQL or in PL/SQL: Example:

create or replace function double (x number) return number is
begin
    return x * 2;
end;
/

Function created.

select '5 Doubled is '||double(5) Message from dual;

MESSAGE
---------------
5 Doubled is 10

1 row selected.

[/li][li]Procedure: A set of PL/SQL code that has a name and executes the code from within the context of a PL/SQL block. You invoke the named procedure in contexts that you would for called execution modules. You cannot refer to a procedure from standard SQL (since SQL refers to data expressions from within its scope, not procedural objects). Example:

create or replace procedure double (x number, y out number) is
begin
    y := x * 2;
end;
/

Procedure created.

set serveroutput on format wrap
declare
    data_hold number;
begin
    double (5,data_hold);
    dbms_output.put_line('5 Doubled is '||data_hold);
end;
/

5 Doubled is 10

PL/SQL procedure successfully completed.

[/li][/ul]Please clarify whether you want a solution for a function or for a procedure.



Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[slicendice]

Sorry, my bad...didn't make it clear. It was a function I was thinking of...

Thanks for your help

 

[Dagon]

create or replace package ret_curs is
   type mycurs is ref cursor;
   function returnCursor(p_param IN NUMBER) return mycurs;
END;
/

create or replace package body ret_curs is
   function returnCursor(p_param IN NUMBER) return mycurs is
     lcurs mycurs;
   BEGIN
     open lcurs for
     select x from (select 1 as x from dual) where x = p_param;
     return lcurs;
   end; 
END;
/

declare
  lcurs ret_curs.mycurs;
  lparam number := 1;
  lres   number;
begin
  execute immediate
  'begin
     :lcurs := ret_curs.returnCursor(:lparam);
   end;'
   using OUT lcurs, IN lparam;
  fetch lcurs into lres;
  dbms_output.put_line(lres);
  close lcurs;
end; 
/

For Oracle-related work, contact me through Linked-In.

 

[carp]

I'm not sure why you would want a dynamic PL/SQL block when you could just invoke the function from your code - wouldn't that be a lot more efficient?
Leveraging Dagon's response, why not use something like:

declare
  lcurs ret_curs.mycurs;
  lparam number := 1;
  lres   number;
begin
  lcurs := ret_curs.returnCursor(lparam);
  fetch lcurs into lres;
  dbms_output.put_line(lres);
  close lcurs;
end; 
/

Or am I missing something?

 

[slicendice]

Dagon, thanks for the code - sadly, it causes my Oracle connection to crash, i.e. I get "ORA-03113: end-of-file on communication channel" every time I try and run it.

Any idea why that might be...?

 

[slicendice]

OK I've done it slightly differently and instead of returning a cursor, it's returning a TABLE data structure. It's working up to a point - but I can't execute it using dynamic SQL and successfully retrieve the data returned by the function.

CREATE OR REPLACE PACKAGE qi_test IS

    TYPE typ_qi_data IS RECORD(
                                    iQIFlag         NUMBER(1),
                                    iIPFlag         NUMBER(1),
                                    iRiskIndicator  NUMBER(1),
                                    iDenominator    NUMBER(8),
                                    iNumerator      NUMBER(8)
                              );
    TYPE typ_qi_data_tab IS TABLE OF typ_qi_data;

    FUNCTION get_f2_data RETURN typ_qi_data_tab;

END qi_test;

CREATE OR REPLACE PACKAGE BODY qi_test IS

    FUNCTION get_f2_data RETURN typ_qi_data_tab IS

        CURSOR f2_cur IS
            SELECT qa.qia_ass_id,
                   qa.qia_cli_id,
                   aaa.l01_a3      AS a3,
                   aaa.l01_aa8     AS aa8,
                   aaa.l01_aa1_uri AS uri,
                   j.l10_j4a       AS j4a
            FROM   qif_ltc_assessments qa,
                   vwe_ass_mdsltc_aaa  aaa,
                   vwe_ass_mdsltc_j    j
            WHERE  aaa.l01_ass_id = qa.qia_ass_id
            AND    j.l10_ass_id = qa.qia_ass_id;

        l_rec typ_qi_data;
        l_tab typ_qi_data_tab := typ_qi_data_tab();

    BEGIN

        FOR f2 IN f2_cur LOOP
            -- These are just test value being assigned here.
            -- In reality, the values assigned here would be
            -- calculated using the data returned by f2_cur
            l_rec.iQIFlag        := 1;
            l_rec.iIPFlag        := 2;
            l_rec.iRiskIndicator := 3;
            l_rec.iDenominator   := 4;
            l_rec.iNumerator     := 5;
        
            l_tab.extend;
            l_tab(l_tab.last) := l_rec;
        END LOOP;
    
        RETURN l_tab;
    
    END get_f2_data;

END qi_test;

So I want to be able to execute the above function using dynamic SQL. Initially tried:

DECLARE
    f2_data qi_test.typ_qi_data_tab;
BEGIN
    EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
        USING OUT f2_data;
    IF f2_data IS NOT NULL THEN
        FOR x IN f2_data.first .. f2_data.count LOOP
            dbms_output.put_line(x || ' ' || f2_data(1).iQIFlag);
        END LOOP;
    END IF;
END;

...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:

f2_data := qi_test.get_f2_data;

...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.

Is there a way that I can achieve this?

Thanks

 

[Dagon]

You've have to externalise the type to the database or the Oracle database engine won't know what it is.

create or replace  TYPE typ_qi_data IS object(
                                    iQIFlag         NUMBER(1),
                                    iIPFlag         NUMBER(1),
                                    iRiskIndicator  NUMBER(1),
                                    iDenominator    NUMBER(8),
                                    iNumerator      NUMBER(8)
                              );
create or replace TYPE typ_qi_data_tab IS TABLE OF typ_qi_data;


CREATE OR REPLACE PACKAGE qi_test IS

    FUNCTION get_f2_data RETURN typ_qi_data_tab;

END qi_test;

CREATE OR REPLACE PACKAGE BODY qi_test IS

    FUNCTION get_f2_data RETURN typ_qi_data_tab IS

        CURSOR f2_cur IS
            SELECT 1 ASqia_ass_id,
                   2 AS qia_cli_id,
                   3       AS a3,
                   4     AS aa8,
                   15 AS uri,
                   35       AS j4a
            FROM   DUAL
            union all
            SELECT 1 ASqia_ass_id,
                   3 AS qia_cli_id,
                   5       AS a3,
                   22     AS aa8,
                   15 AS uri,
                   35       AS j4a
            FROM   DUAL;

        l_rec typ_qi_data;
        l_tab typ_qi_data_tab := typ_qi_data_tab();

    BEGIN
        FOR f2 IN f2_cur LOOP
            -- These are just test value being assigned here.
            -- In reality, the values assigned here would be
            -- calculated using the data returned by f2_cur
            l_rec := typ_qi_data(9, 2,3,4,5);
            
            l_tab.extend;
            l_tab(l_tab.last) := l_rec;
        END LOOP;
    
        RETURN l_tab;
    
    END get_f2_data;

END qi_test;

DECLARE
    f2_data typ_qi_data_tab;
BEGIN
    EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
        USING IN OUT f2_data;
    IF f2_data IS NOT NULL THEN
        FOR x IN f2_data.first .. f2_data.count LOOP
            dbms_output.put_line(x || '--- ' || f2_data(x).iQIFlag);
        END LOOP;
    END IF;
END;

For Oracle-related work, contact me through Linked-In.