Oracle PL/SQL: Function Cursor

Question

I get Error:

PLS-00231: function 'GET_NUM' may not be used in SQL

when the following code is executed;

CREATE OR REPLACE PACKAGE BODY TESTJNSABC IS

  -- FUNCTION IMPLEMENTATIONS
  FUNCTION get_num(num IN NUMBER) 
    RETURN SYS_REFCURSOR AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    --
    OPEN my_cursor FOR 
    WITH ntable AS (
         SELECT 1 ID, 111 AGT, 'ABC' DESCRIP FROM DUAL
         UNION ALL
         SELECT 2 ID, 222 AGT, 'ABC' DESCRIP FROM DUAL
         UNION ALL
         SELECT 1 ID, 333 AGT, 'ABC' DESCRIP FROM DUAL
    )
    SELECT AGT FROM ntable WHERE ID = num;
    RETURN my_cursor;

  END;

  -- PROCEDURE IMPLEMENTATIONS 
  PROCEDURE testingabc AS

    BEGIN
      WITH xtable AS (
         SELECT 111 AGT, 'A' DESCRIP FROM DUAL
         UNION ALL
         SELECT 222 AGT, 'B' DESCRIP FROM DUAL
         UNION ALL
         SELECT 333 AGT, 'C' DESCRIP FROM DUAL
     )
      SELECT DESCRIP FROM xtable WHERE COD_AGT IN get_num(1);


    END testingabc;

END TESTJNSABC;

Even if I call the function as TESTJNSABC.get_num(1) I still get the same error. --UPDATE. So in real life scenario I would like to call a Function from a WHERE CLAUSE; the function should return a set of NUMBER values (that's why I use the IN clause).

Answer 1

So is it possible then to create a variable on the Procedure and assign the Function values to the variable? Let's say

It sould not be the question whether it is possible or not rather it should had been if this is the right way. Ofcourse you can do it in the way you are doing but as experts suggested, that's not the right and efficient way. See how you can do it. PS: Not tested.

CREATE OR REPLACE PACKAGE BODY TESTJNSABC IS

  -- FUNCTION IMPLEMENTATIONS
  FUNCTION get_num(num IN NUMBER) 
    RETURN SYS_REFCURSOR AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    --
    OPEN my_cursor FOR 
    WITH ntable AS (
         SELECT 1 ID, 111 AGT, 'ABC' DESCRIP FROM DUAL
         UNION ALL
         SELECT 2 ID, 222 AGT, 'ABC' DESCRIP FROM DUAL
         UNION ALL
         SELECT 1 ID, 333 AGT, 'ABC' DESCRIP FROM DUAL
    )
    SELECT AGT FROM ntable WHERE ID = num;
    RETURN my_cursor;

  END;

  -- PROCEDURE IMPLEMENTATIONS 
  PROCEDURE testingabc AS
    --Creating a collection to hold return of the function
    type y is table of varchar2(1000) index by pls_integer; 
    var_z  y; 
    var_1 varchar2(100);

    BEGIN

     Select get_num(1)
     bulk collect into  var_z
     from dual;

    For i in 1..var_z.count
    loop

     WITH xtable AS (
         SELECT 111 AGT, 'A' DESCRIP FROM DUAL
         UNION ALL
         SELECT 222 AGT, 'B' DESCRIP FROM DUAL
         UNION ALL
         SELECT 333 AGT, 'C' DESCRIP FROM DUAL
     )
      SELECT DESCRIP
      into var_1 
      FROM xtable 
      WHERE AGT = var_z(i) ; ---Check each record

      dbms_output.put_line(var_1);

     end loop;

    END testingabc;

END TESTJNSABC;

Answer 2

in () requires either a subquery or a comma-separated list of values, so no, you can't substitute a function that returns a collection.

Assuming the function is in scope for SQL queries (it's either a standalone function or declared in a package specification), you could use it in a table() construction (this needs a table function, i.e. it needs to return a collection, not a cursor):

where somecol in (select column_value from table(get_num(1)) )

(or the equivalent inner join etc.)

Demo at livesql.oracle.com/apex/livesql/file/content_EF2M0F1LV9LTP6PEII3BDFKAI.html

Edit: I've just noticed the example in the question tried to use a ref cursor. Note that the table() operator works on collections, not ref cursors. Therefore the function has to return a collection type (nested table or varray).

Answer 3

As per @William Robertson answer, I tried to implement the solution but getting issue :

[Error] ORA-22905 (18: 26): PL/SQL: ORA-22905: cannot access rows from a non-nested table item

 -- FUNCTION IMPLEMENTATIONS
 create or replace  FUNCTION get_num(num IN NUMBER) 
    RETURN SYS_REFCURSOR AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    --
    OPEN my_cursor FOR 
    SELECT AGT 
    FROM ntable 
    WHERE ID = num;

    RETURN my_cursor;

  END;

-- PROCEDURE IMPLEMENTATIONS

CREATE OR REPLACE PROCEDURE testingabc
AS
type var is table of xtable.DESCRIP%type;    
v_var var;    
BEGIN
    SELECT DESCRIP
    bulk collect into var
     FROM xtable
    WHERE AGT IN (SELECT COLUMN_VALUE
                    FROM TABLE (get_num (1)));
END testingabc;