I am doing the next however it does not work

select package_name.function_name(param,param) from dual

I am calling a function that returns a cursor so im speculating "from dual" may be the problem

can there be one other way to do it?

I presume you mean a Ref Cursor. This can be a PL/SQL construct which functions like a pointer to some records came back with a query. What this means is it needs to be construed through the client which runs the query. For example, we are able to map a Ref Cursor to some JDBC or ODBC ResultSet.

There's certainly no problem together with your fundamental statement. This is a function much like your personal:

SQL> desc get_emps
FUNCTION get_emps RETURNS REF CURSOR
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_DNO                          NUMBER(2)               IN
 P_SORT_COL                     VARCHAR2                IN     DEFAULT
 P_ASC_DESC                     VARCHAR2                IN     DEFAULT

SQL> 

I'm able to easily refer to this as inside a wider PL/SQL block:

SQL> declare
  2      rc sys_refcursor;
  3  begin
  4      rc := get_emps(50);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

However, SQL*PLus are designed for CURSOR constructs natively:

SQL> select get_emps(50) from dual
  2  /

GET_EMPS(50)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      8060 VERREYNNE  PLUMBER         8061 08-APR-08       4000                    50
      8061 FEUERSTEIN PLUMBER         7839 27-FEB-10       4500                    50
      8085 TRICHLER   PLUMBER         8061 08-APR-10       3500                    50
      8100 PODER      PLUMBER         8061                 3750                    50


SQL>

This statement also runs in SQL Developer, even though result set is organized within an ugly fashion.

So, if you're getting issues with your function, the questions are:

  1. What client atmosphere are you currently using?
  2. With what precise fashion will it "not work"? Please describe the observed behavior, including any error messages?
  3. Also provide us with atmosphere particulars like the version from the database, the OS, etc.

Getting read your other question about this subject I figured the issue may be because of using a User-Defined Ref Cursor (as opposed to the built-in). However, that does not make a difference. This packed function:

SQL> create or replace package emp_rc_utils as
  2
  3      type emp_rc is ref cursor return emp%rowtype;
  4
  5      function       get_emps
  6          ( p_dno in emp.deptno%type
  7      )
  8      return emp_rc;
  9  end;
 10  /

Package created.

SQL> create or replace package body emp_rc_utils as
  2
  3      function       get_emps
  4          ( p_dno in emp.deptno%type
  5      )
  6          return emp_rc
  7      is
  8          return_value emp_rc_utils.emp_rc;
  9      begin
 10
 11          open return_value for select * from emp where deptno = p_dno;
 12
 13          return return_value;
 14      end get_emps;
 15
 16  end emp_rc_utils;
 17  /

Package body created.

SQL>

Still works...

SQL> declare
  2      rc sys_refcursor;
  3  begin
  4      rc := emp_rc_utils.get_emps(50);
  5  end;
  6  /

PL/SQL procedure successfully completed.


SQL> select emp_rc_utils.get_emps(50) from dual
  2  /

EMP_RC_UTILS.GET_EMP
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      8085 TRICHLER   PLUMBER         8061 08-APR-10       3500                    50
      8060 VERREYNNE  PLUMBER         8061 08-APR-08       4000                    50
      8061 FEUERSTEIN PLUMBER         7839 27-FEB-10       4500                    50
      8100 PODER      PLUMBER         8061                 3750                    50


SQL>

perhaps you have attempted:

myCursor := package_name.function_name(param,param);

this would need to be from inside an evaluation block or perhaps a saved procedure.