Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

calling stored procedure which returns SYS_REFCURSOR

 
Darvesh Niz
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Darvesh Niz
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thats ok
i will fall back to my original way of doing it.

Thanks

Rashid
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic