CODEWITHSUNDEEP
sqloracle-databaseplsqlcursor
resla95
resla95

Reputation: 1257

How to make a select from a cursor that returns from a plsql function ORACLE

i have a function that return a cursor my package is like this

FUNCTION SEDIRUNTIME (sede varchar2) return SYS_REFCURSOR

this cursor return x number of row with only one value, for example :

 ROW1 - 34
 ROW2 - 55
 ROW3 - 56  ecc. ecc.

now i have i select like this 

.. AND field in  (select DBK_ENIN_REPORT.*SEDIRUNTIME*(sede) from dual)

this will simulate a clause IN which we can know the values at run time.

for example, based on the location parameter, the cursor can give me 22 and 34 rather than just 56 or 78 98 09.

written so i do not work from error number 00932 incoherent data types.

solutions?

Upvotes: 1

Views: 12008

Answers (2)

XING
XING

Reputation: 9886

Well I would say you CAN do it but you need to twist little bit. You can see how i have done it as below. I take employee table as and example.

Create function:

CREATE OR REPLACE FUNCTION SEDIRUNTIME (sede VARCHAR2)
   RETURN SYS_REFCURSOR
AS
   cur   SYS_REFCURSOR;
BEGIN
   OPEN cur FOR SELECT employee_id FROM employee;

   RETURN cur;
END;
/

Anonymous block which you can implement in your package as Procedure;

DECLARE
   x       SYS_REFCURSOR;
   y       NUMBER;
   v       VARCHAR2 (100);
   v_sql   VARCHAR2 (200);

   TYPE var_emp IS TABLE OF employee%ROWTYPE
      INDEX BY PLS_INTEGER;

   v_emp   var_emp;
BEGIN
   x := SEDIRUNTIME ('sede');

   LOOP
      FETCH x INTO y;

      v := v || ',' || y;

      EXIT WHEN x%NOTFOUND;
   END LOOP;
   --Created the IN clause list
   v := LTRIM (v, ',');

   v_sql := 'Select * from employee where employee_id in (' || v || ')';

   EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_emp;

   FOR i IN 1 .. v_emp.COUNT
   LOOP
      DBMS_OUTPUT.put_line ( v_emp (i).employee_id || '--' || v_emp (i).first_name);
   END LOOP;
END;

OUTPUT:

SQL> /
1--XXX
2--YYY

PL/SQL procedure successfully completed.

SQL>

PS: Ofcourse the solution provided by MTO is going to be much faster that this.

Upvotes: 1

MT0
MT0

Reputation: 168740

You cannot use a CURSOR like that.

But you can change the function to return a collection:

CREATE TYPE Numberlist IS TABLE OF NUMBER;
/

CREATE FUNCTION DBK_ENIN_REPORT.SEDIRUNTIME (
  sede varchar2
) return NumberList
IS
  out_numbers NumberList;
BEGIN
  SELECT id
  BULK COLLECT INTO out_numbers
  FROM   your_table;

  RETURN out_numbers;
END;
/

Then you can do:

.. AND field MEMBER OF DBK_ENIN_REPORT.SEDIRUNTIME(sede)

or

.. AND field IN ( SELECT COLUMN_VALUE FROM TABLE( DBK_ENIN_REPORT.SEDIRUNTIME(sede) ) )

Upvotes: 2

Related Questions