aspose file tools*
The moose likes Other Application Frameworks and the fly likes calling stored procedure which returns SYS_REFCURSOR Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Other Application Frameworks
Bookmark "calling stored procedure which returns SYS_REFCURSOR" Watch "calling stored procedure which returns SYS_REFCURSOR" New topic
Author

calling stored procedure which returns SYS_REFCURSOR

Darvesh Niz
Ranch Hand

Joined: May 12, 2008
Posts: 119
Hi
i have a stored procedure which returns a SYS_REFCURSOR, i am trying to find a way how to retrieve the rows. here is the stored proc


and here is my java code



Can some one please let me know what i am doing wrong
I was able to call a stored produre which return a cursor of a specific type before but i dont want to do it again. Is it true that if you pass a ref cursor it has to be of a specific type declared in oracle.

Please let me know
Thanks
Rashid
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

Isn't there another method you need to override in StoredProcedure to get it to work. I thought like an execute method, and you have to get the out parameters values in that method and pass it to something to convert it into your domain objects. Check out the docs on StoredProcedure at www.springframework.org

Good Luck

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Darvesh Niz
Ranch Hand

Joined: May 12, 2008
Posts: 119
Thanks for the reply
Yes you can do that and thats what i am have been doing till now, if you see the below decleration i have get_dma_list and the out parameter is of type
rc_dma and rc_dma is a ref cursor of type r_dma type. and in the java code i have a class which extends rowmapper and it maps a row and i can get a result.
PROCEDURE get_dma_list (p_rc_dma OUT rc_dma);

TYPE r_dma IS RECORD (
dma_id INTEGER,
dma_code VARCHAR2 (25),
dma_name VARCHAR2 (255)
);

TYPE rc_dma IS REF CURSOR RETURN r_dma;

What i wanted was is there a way i just get hold of SYS_REFCURSOR and i iterate over it, like when you get a cursor back in a conventional perpared statement call
then we can loop through the cursor and use rs.getString(1) etc... to get values out of it.

I dont want to spend a lot of time researching on this, i might stick with the original plan of doing the way i have been doing, the only think is everytime you have to creeate a type object and map it to a ref cursor etc....

Please advice

Thanks
Rashid
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

Sorry, I can't be of much more help. And personally, when things get a little more complicated, I find that I look for a different solution. Things like not using Stored Procedures anymore, because I can get better performance via other simpler means. But I also understand, that sometimes you don't have that choice.

Mark
Darvesh Niz
Ranch Hand

Joined: May 12, 2008
Posts: 119
thats ok
i will fall back to my original way of doing it.

Thanks

Rashid
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: calling stored procedure which returns SYS_REFCURSOR