How to call a function in a package

I presume you mean a Ref Cursor. This is a PL/SQL construct which acts as a pointer to a set of records returned by a query. This means it has to be interpreted by the client which runs the query. For instance, we can map a Ref Cursor to a JDBC or ODBC ResultSet.

There is certainly nothing wrong with your basic statement. Here is a function similar to your own:

SQL> desc get_empsFUNCTION 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     DEFAULTSQL> 

I can easily call this in 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 can handle CURSOR constructs natively:

SQL> select get_emps(50) from dual  2  /GET_EMPS(50)--------------------CURSOR STATEMENT : 1CURSOR 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                    50SQL>

This statement also runs in SQL Developer, although the result set is laid out in an ugly fashion.

So, if you are having problems with your function, the questions are:

1. What client environment are you using?
2. In what precise fashion does it "not work"? Please describe the observed behaviour, including any error messages?
3. Also give us environment details such as the version of the database, the OS, etc.

Having read your other question on this topic I thought the problem might be due to the use of a User-Defined Ref Cursor (rather than the built-in). However, that doesn't make any difference. This packaged 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 : 1CURSOR 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                    50SQL>

You can do this via a refcursor call or populate a user defined table and return it as follows:

create or replacefunction getRef return sys_refcursorisl_ref  sys_refcursor;begin    open l_ref for    select 1 a, 'a' c from dual    union all    select 2 a, 'b' c from dual    union all    select 3 a, 'c' c from dual    union all    select 4 a, 'd' c from dual;    return l_ref;end getRef;/select getref() from dual;GETREF() -------- A                      C  ---------------------- -  1                      a  2                      b  3                      c  4                      d  --you'll notice this isn't the most user-friendly result set if you look at it in SQL Developer or whatno--drop function getRef;

you can also use the 'table' if you are passing back a table collection as such

create or replace type lookup_row as   object ( a number, c varchar2(20) );  /create or replace type lookups_tab as   table of lookup_row;/create or replacefunction getUserDefinedTableType return lookups_tabislTestTypeTable  lookups_tab;begin     SELECT lookup_row(a,c)               bulk collect INTO lTestTypeTable               from    (select 1 a, 'a' c from dual    union all    select 2 a, 'b' c from dual    union all    select 3 a, 'c' c from dual    union all    select 4 a, 'd' c from dual);    return lTestTypeTable;end getUserDefinedTableType;/select * from table(getUserDefinedTableType());--this returns it in a more user friendly manner--http://www.oreillynet.com/pub/a/network/2003/01/22/feuerstein.html?page=2--http://stackoverflow.com/questions/3150137/converting-oracle-query-into-user-defined-types-in-pl-sql/3152885#3152885A                      C                    ---------------------- -------------------- 1                      a                    2                      b                    3                      c                    4                      d  

have you tried:

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

this would have to be from within a test block or a stored procedure.