This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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?.
 
jQuery in Action, 2nd edition
 
subject: Oracle Cursor
 
Similar Threads
how to select only few coloum of table using Criteria class in hibernate
Mapping file for table that has two classes for maintained for attribute mapping
How to configure xxx.hbm.xml
Oracle Database SQL confusion
How to pass arrays using JDBC to database?