• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Oracle Cursor

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?.
 
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shouldnt have any problem with a union in a cursor. Could you post some code.
 
Thirumurugan Sivaji
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?.
 
Consider Paul's rocket mass heater.
    Bookmark Topic Watch Topic
  • New Topic