Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to call oracle procedure and fetch returned data using Java?

 
Azrael Noor
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
HI

I am trying to execute procedure using following statements
The procedure in
schema: apps,
Package: XXCU_PKG,
Procedure name: get_return (pid in number, result out nocopy clob)

For calling above i used following code:


but i find it is returning:




After that i changed code and removed result set statement and just put



it is also returning false.

Could anyone please help how to get value from procedure

Regards
 
T Mishra
Ranch Hand
Posts: 108
Eclipse IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. you did not register out parameter.

2. mismatching package name. Not sure which of the two are correct - XXCU_PKG or XXCU_RETURN_PKG

3. if it doesn't work try calling without package name



 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
T Mishra wrote:2. mismatching package name. Not sure which of the two are correct - XXCU_PKG or apps.XXCU_RETURN_PKG

I don't think so. Firstly, apps.XXCU_RETURN_PKG.get_return is a perfectly valid name (apps is probably a schema name, XXCU_RETURN_PKG is a package name and get_return is the procedure or function name). Secondly if the package name was not correct, it would produce a different error.

3. if it doesn't work try calling without package name

Calling a package procedure without specifying the package name will fail.


I suppose the get_return is a function, in which case it is needed to call it like this:and register the first parameter as an OUT parameter. I also assume the return type is a ref cursor, but I don't have experience with handling ref cursors in Java, so cannot help here

I was mostly wrong here. Sorry for the confusion.
 
T Mishra
Ranch Hand
Posts: 108
Eclipse IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The procedure details mentioned is
Azrael Noor wrote;

The procedure in
schema: apps,
Package: XXCU_PKG,
Procedure name: get_return (pid in number, result out nocopy clob)

while the package name called via jdbc is apps.XXCU_RETURN_PKG.get_return . I was referring to this mismatch between the package names.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, T Mishra, you were right and your advice is correct. I somehow missed the specification.

(I still guess the package name in the code is right, otherwise it should give another error.)
 
Azrael Noor
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i mistakenly wrote wrong package name treat both are same
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you try what T Mishra suggested?

(That is, register the second parameter as an OUT parameter using CallableStatement.registerOutParameter and then retrieve the value by calling CallableStatement.getBlob(int parameterIndex). Obtaining parameters that are not cursors does not involve ResultSets.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic