aspose file tools*
The moose likes Oracle/OAS and the fly likes Oracle Cursor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle Cursor" Watch "Oracle Cursor" New topic
Author

Oracle Cursor

Thirumurugan Sivaji
Greenhorn

Joined: Mar 03, 2009
Posts: 20
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?.


Warm Regards,

Thirumurugan Sivaji

Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
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
Thirumurugan Sivaji
Greenhorn

Joined: Mar 03, 2009
Posts: 20
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
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
Shouldnt have any problem with a union in a cursor. Could you post some code.
Thirumurugan Sivaji
Greenhorn

Joined: Mar 03, 2009
Posts: 20
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?.
 
 
subject: Oracle Cursor