Home » Other » Training & Certification » Issue to load data from Procedure using SYS_REFCURSOR
icon4.gif  Issue to load data from Procedure using SYS_REFCURSOR [message #273218] Tue, 09 October 2007 10:17 Go to next message
nikhil_thampi@yahoo.com
Messages: 1
Registered: October 2007
Junior Member
We have employee Table with following details
Emp_Nbr
Dept_Nbr
Emp_Type (M,D,T)
Joining_Date

we have to get the counts in the following format from the above Table.

Dept_Nbr .... Tot_Emp ....Tot_Emp_M .... Tot_Emp_D .... Tot_Emp_T ... Earliest_Joining_Date ....Corres_Emp_nbr
1234 ... 100 ... 50 ... 30 ... 20 ... 10/01/2003 ... 124567
2345 ... 200 ... 100 ... 75 ... 25 ... 09/17/2000 ... 345678
3456 ... 50 ... 40 ... 1 ... 9 ... 01/01/2000 ... 345634
4567 ... 300 ... 75 ... 175 ... 50 ... 01/12/1998 ... 534567
5678 ... 500 ... 100 ... 150 ... 250 ... 08/17/1996 ... 978956

Please refer the attachment for

Tot_Emp = Count of all the employees under a dept_nbr
Tot_Emp_M = Count of all the employees under a dept_nbr and emp_type as M
Tot_Emp_D = Count of all the employees under a dept_nbr and emp_type as D
Tot_Emp_T = Count of all the employees under a dept_nbr and emp_type as T
Earliest_joining_date = Earliest Joining Date of employee under a dept_nbr
Corres_Emp_nbr = Take the Employee Nbr having earliest Joining Date

The output has to be send from a Oracle Procedure to Java application. So we plan to use
SYS_REFCURSOR to get all the data. But for that all the records must be taken using single
select statement. Please help.
Re: Issue to load data from Procedure using SYS_REFCURSOR [message #273222 is a reply to message #273218] Tue, 09 October 2007 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com has many fine coding examples
Re: Issue to load data from Procedure using SYS_REFCURSOR [message #273227 is a reply to message #273218] Tue, 09 October 2007 10:51 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

We don't do your homework but we can help you.
Post what you already tried.

Regards
Michel
Previous Topic: Cost of OCAs for apps Financials
Next Topic: Creating Age Categories from Birth Date
Goto Forum:
  


Current Time: Fri Dec 09 13:42:44 CST 2016

Total time taken to generate the page: 0.14607 seconds