Oracle Cursor

[Thirumurugan Sivaji]

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

[Agador Paloi]

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:

  
   SELECT EMP_ID,EMP_NAME,EMP_ROLE 
         from EMPLOYEE 
       WHERE EMP_ROLE= empRole
                 AND   empId    is        null
                 AND   empRole is not null
    UNION
    SELECT EMP_ID,EMP_NAME,EMP_ROLE 
          from EMPLOYEE
          WHERE EMP_ID=empId 
                AND   empId   is not null
                AND   empRole is   null
     UNION
     -- the other two selects unioned  here
     
    

Hope this helps you out

[Thirumurugan Sivaji]

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

[Agador Paloi]

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

[Thirumurugan Sivaji]

Hi,

It's working fine with UNION.

create or replace PROCEDURE SPP_SELECT_EMPLOYEES
( empId IN VARCHAR2
, empRole IN VARCHAR2
, emp_cur OUT SYS_REFCURSOR
, resultOut OUT NUMBER
) IS
CURSOR emp_cursor IS SELECT EMP_ID,EMP_NAME,EMP_ROLE from ITS_M_USER WHERE EMP_ROLE= empRole  
                 AND empId is null AND empRole is not null  
     UNION  
     SELECT EMP_ID,EMP_NAME,EMP_ROLE from ITS_M_USER WHERE EMP_ID=empId   
                 AND empId is not null AND empRole is null
     UNION  
     SELECT EMP_ID,EMP_NAME,EMP_ROLE from ITS_M_USER WHERE EMP_ID=empId AND EMP_ROLE=empRole   
                 AND empId is not null AND empRole is not null  
     UNION  
     SELECT EMP_ID,EMP_NAME,EMP_ROLE from ITS_M_USER WHERE empId is null AND empRole is null;
      
  currId ITS_M_USER.EMP_ID%TYPE;
  currName ITS_M_USER.EMP_NAME%TYPE;
  currRole ITS_M_USER.EMP_ROLE%TYPE;
BEGIN
  open emp_cursor;
  loop
    FETCH emp_cursor INTO currId,currName,currRole;
    exit when emp_cursor%NOTFOUND;
    dbms_output.PUT_LINE(currId||' ' ||currName||' ' ||currRole);
  end loop;
  close emp_cursor;
  resultOut := 101;
END SPP_SELECT_EMPLOYEES;

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