Oracle Cursor

Hi,
I have the following table

Table Name : EMPLOYEE
Column : EMP_ID varchar2,EMP_NAME varchar2,EMP_ROLE varchar2
Procedure : create or replace PROCEDURE SP_SELECT_EMPLOYEES( empId IN VARCHAR2, empRole IN VARCHAR2, curOut OUT TYPES.ref_cursor
, resultOut OUT NUMBER)

I have the following conditions
1. If the empId is empty and empRole is not empty
SELECT EMP_ID,EMP_NAME,EMP_ROLE from EMPLOYEE WHERE EMP_ROLE=empRole;
2. If the empRole is empty and empId is not empty
SELECT EMP_ID,EMP_NAME,EMP_ROLE from EMPLOYEE WHERE EMP_ID=empId;
3. If the empId and empRole are not empty
SELECT EMP_ID,EMP_NAME,EMP_ROLE from EMPLOYEE WHERE EMP_ROLE=empRole and EMP_ID=empId;
4. If the empId and empRole are empty
SELECT EMP_ID,EMP_NAME,EMP_ROLE from EMPLOYEE;

How to get Employee details from table EMPLOYEE with the above mentioned conditions using Cursor in Oracle PL/SQL?.

the easiest way maybe to use a union in your cursor for each of the selects testing the input parameters for null
or not null values in the where clause:


Hope this helps you out

Hi,

I tried with UNION but the Procedure did not compile and it gives the following error

Error(10,6): Encountered the symbol "UNION" when expecting one of the following: begin function package procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor

Shouldnt have any problem with a union in a cursor. Could you post some code.

Hi,

It's working fine with UNION.


The above procedure is working fine and printing the values.
How to store the value of emp_cursor(CURSOR) into emp_cur(REFCURSOR which is an OUT parameter) or how to return emp_cursor?.