Oracle Ref cursor along with out parameters

Question

I have a requirement to have a IN OUT parameter along with a reference cursor as a return from a stored procedure. Currently I am doing following.

create table dept
( dept_id number,
  name varchar2(40),
  location varchar2(200)
);

CREATE OR REPLACE PACKAGE HR.SP_PACKAGE AS 

  TYPE dept_type IS REF CURSOR RETURN HR.dept%ROWTYPE;

END SP_PACKAGE;

CREATE OR REPLACE PROCEDURE HR.MIXED_IN_INOUT_REF_PARAM 
(
  P_ID IN NUMBER  
, P_NAME_TO_LOCATION IN OUT VARCHAR2  
, P_RCURSOR OUT SP_PACKAGE.dept_type
) AS 
BEGIN
  SELECT name INTO P_NAME_TO_LOCATION FROM HR.dept WHERE dept_id = p_id AND name =  P_NAME_TO_LOCATION;
  OPEN P_RCURSOR FOR
      select *
      from HR.dept;
END MIXED_IN_INOUT_REF_PARAM;

I am getting hit with some errors at run time even though the compilation is successful.

ORA-06550: line 4, column 17:
PLS-00201: identifier 'CURSOR' must be declared
ORA-06550: line 4, column 13:
PL/SQL: Item ignored
ORA-06550: line 12, column 18:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
ORA-06550: line 21, column 17:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 21, column 3:
PL/SQL: Statement ignored

I am using Sql Developer. Any help is appreciated.

Answer 1

change like this remove the return

CREATE OR REPLACE PACKAGE HR.SP_PACKAGE AS 

  TYPE dept_type IS REF CURSOR ;

END SP_PACKAGE;

you can make it more dynamic thought like this

open p_cursor FOR  'SELECT *  FROM DEPT where ' ||  V_WHERE;

Answer 2

There is no problem with your proc chamibuddhika ,i think there is some problem with how you called it .I tried creating the same procedure and it works fine .try to run you procedure as shown below:

declare
v_temp varchar2(200):='ACCOUNTING';
rec SP_PACKAGE.dept_type;
v_rec rec%ROWTYPE;

begin
MIXED_IN_INOUT_REF_PARAM(10,v_temp,rec);

LOOP
FETCH rec INTO v_rec;
EXIT WHEN rec%NOTFOUND;
 dbms_output.put_line(v_rec.name);

END LOOP;

end;

OUTPUT

 ACCOUNTING
 RESEARCH
 SALES
 OPERATIONS

There is one issue with your proc ,when the query inside proc does not return anything ,it will give you no_data_found exception.So you need to handle that in your proc.