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).

You have some issues here. It would be easier to help you if you could explain what do you want these procedures to do. — Aleksej
– Aleksej, Commented Jan 16, 2017 at 15:49
The error seems fairly clear? You can't use your function in a SQL statement, you can only call it from other PL/SQL contexts (at least until 12c). — Alex Poole
– Alex Poole, Commented Jan 16, 2017 at 15:49
You can't use a function returning a ref cursor in that way. It would usually be used in the context of passing a result set pointer around in PL/SQL. You can get close but you will need to use a pipelined function and without knowing exactly what you want an answer including that would be pointless. — BriteSponge
– BriteSponge, Commented Jan 16, 2017 at 15:53
Hint: if the function get_num can return more than one value, you shoud better check how to use collections instead of cursors; if it only can return one value, simply use a number as OUT parameter — Aleksej
– Aleksej, Commented Jan 16, 2017 at 15:54
If I were you, I wouldn't bother with a function for this example; I'd create the ref cursor as a standalone view instead and then use that as a predicate in my main query. Doing it this way would negate the need to pull a load of information into a collection in memory and then use that in your main query. — Boneist
– Boneist, Commented Jan 16, 2017 at 16:00

XING · Accepted Answer · 2017-01-17 12:22:25Z

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;

Thanks @XING for your help. And what is the best practice (more efficient way) of performing this?

William Robertson · Accepted Answer · 2017-01-17 11:27:41Z

0

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).

edited Jan 17, 2017 at 11:27

answered Jan 16, 2017 at 16:42

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

6 Comments

XING Over a year ago

This is not working. I exactly did the same how you mentioned. Can you show a demo once

William Robertson Over a year ago

Added link to LiveSQL demo.

XING Over a year ago

Its not working inside a plsql block in the above example when I replace SELECT DESCRIP FROM xtable WHERE COD_AGT IN get_num(1); with SELECT DESCRIP FROM xtable WHERE AGT IN (select column_value from table(get_num(1)) )

William Robertson Over a year ago

Define 'not working'. My example will work within PL/SQL. Probably something else is different in your test case.

XING Over a year ago

I gave you the statement which is not working. You can try that. i created on Function and then used it in a Procedure the way you used it. It throws error: ORA-22905 : Cannot access rows from a nested table item

|

XING · Accepted Answer · 2017-01-17 10:45:07Z

0

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;

answered Jan 17, 2017 at 10:45

XING

9,9164 gold badges25 silver badges40 bronze badges

2 Comments

William Robertson Over a year ago

The table() operator works on collections not ref cursors. I missed that the original question tried to do this with a ref cursor. Added a note to my answer.

XING Over a year ago

That is what I was thinking off. Coz I had never seen such things and was more interested to learn if that can happen..

Collectives™ on Stack Overflow

Oracle PL/SQL: Function Cursor

3 Answers 3

1 Comment

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related