Home » SQL & PL/SQL » SQL & PL/SQL » Passing Resultsets to Java - Urgent
icon5.gif  Passing Resultsets to Java - Urgent [message #220165] Mon, 19 February 2007 05:18 Go to next message
Ramya_Vijaykumar
Messages: 3
Registered: February 2007
Junior Member
Hi, I am involved in writing SP's and functions for my project. Back end being Oracle and Front End - Java. Since this application involves a lotta reports, I am using Cursors to write loop thru the data and perform some calculation etc and assign them to the PL/SQL Collection type -(PL/SQL - Index by tables).

But my front end (Java) guys, say that PL/SQL tables are not recognized in Java. I would like to know how this could be resolved and request a piece of code for more clarity.

Thanks in Advance.

[Updated on: Mon, 19 February 2007 05:51]

Report message to a moderator

Re: Passing Resultsets to Java - Urgent [message #220181 is a reply to message #220165] Mon, 19 February 2007 06:14 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Look at http://asktom.oracle.com/tkyte/ResultSets/
Re: Passing Resultsets to Java - Urgent [message #220185 is a reply to message #220165] Mon, 19 February 2007 06:32 Go to previous messageGo to next message
Ramya_Vijaykumar
Messages: 3
Registered: February 2007
Junior Member
I understand that REF CURSOR's are used for passing the result set from ORACLE to JAVA.

I have a piece of code like this:

CREATE OR REPLACE FUNCTION cust_group(p_cust_id IN number)
RETURN types.ref_cursor AS

/* Cursor Declaration - the REF Cursor is declared in the 'types' Package*/
c_cust_class types.ref_cursor;

/* Variable Declaration*/
v_cust_id amd_group_corelation.gcr_cust_id%TYPE;

/* Program Begin*/
BEGIN

/* Assigning the input parameter to a variable */

v_cust_id := p_cust_id;

OPEN c_cust_class FOR
SELECT amc.mct_cust_name
,agc.gcr_type
,acg.cgp_group_name
,agc.gcr_cust_id
,(SELECT 'PRIMARY'
FROM amd_group_corelation agc1
WHERE agc1.gcr_cust_id = amc.mct_cust_id
AND upper(agc1.gcr_type_desc) = 'PRIMARY'
AND agc1.gcr_corelation_cmpy = 'Y') Primary
,(SELECT 'PARENT'
FROM amd_group_corelation agc1
WHERE agc1.gcr_cust_id = amc.mct_cust_id
AND upper(agc1.gcr_type_desc) = 'PARENT'
AND agc1.gcr_corelation_cmpy = 'Y') Parent
,(SELECT upper(agc1.gcr_type_desc)
FROM amd_group_corelation agc1
WHERE agc1.gcr_cust_id = amc.mct_cust_id
AND upper(agc1.gcr_type_desc) NOT IN
('PARENT','PRIMARY')) Description
FROM amd_mst_customer amc,
amd_group_corelation agc,
amd_cust_group acg
WHERE amc.mct_cust_id = agc.gcr_cust_id
AND acg.cgp_group_id = (SELECT agr.gcr_group_id
FROM amd_group_corelation agr
WHERE agr.gcr_cust_id = v_cust_id) AND acg.cgp_group_id = agc.gcr_group_id
ORDER BY agc.gcr_type;

RETURN c_cust_class;
/* Returning a REF Cursor type*/

END cust_group;
/* Program End*/
/

Here I do not do any calculation on the fly but just return the data obtained from the cursor.

What if the data from one cursor is passed on to the next and then some calculations are performed with in the inner cursor... In that case will I be able to use a REF CURSOR or is there any alternate to achieve this?
Re: Passing Resultsets to Java - Urgent [message #220191 is a reply to message #220165] Mon, 19 February 2007 07:20 Go to previous messageGo to next message
Ramya_Vijaykumar
Messages: 3
Registered: February 2007
Junior Member
Pls refer to the below link for the question that I had put forth...

The author has alo suggested a book "Effective oracle by design"

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30732069210515

Thanks a lot all of you.
Re: Passing Resultsets to Java - Urgent [message #220386 is a reply to message #220165] Tue, 20 February 2007 08:50 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
I don't see any problems with that (you will have to tune both your queries).

HTH
Re: Passing Resultsets to Java - Urgent [message #221598 is a reply to message #220165] Tue, 27 February 2007 10:27 Go to previous message
webcomsystems
Messages: 4
Registered: February 2007
Location: UK
Junior Member
Hi,

you can use an Oracle Type to pass data to Java.

Try my site at http://www.oracleunittesting.com

Hope this helps.

David Drewette
www.oracleunittesting.com
Best Practices in Unit Testing on Oracle Databases
Previous Topic: Refresh time for Materialized View
Next Topic: 8i Organization external error when creating table from unix script
Goto Forum:
  


Current Time: Sat Dec 03 07:54:01 CST 2016

Total time taken to generate the page: 0.10808 seconds