aspose file tools*
The moose likes JDBC and the fly likes How to call oracle procedure and fetch returned data using Java? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to call oracle procedure and fetch returned data using Java?" Watch "How to call oracle procedure and fetch returned data using Java?" New topic
Author

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

Azrael Noor
Ranch Hand

Joined: Jul 29, 2010
Posts: 378
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


Regards
Azrael Noor
T Mishra
Ranch Hand

Joined: Apr 04, 2006
Posts: 107

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




Thanks,
Tushar (SCJP 1.5)
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3435
    
  47

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

Joined: Apr 04, 2006
Posts: 107

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

Joined: Aug 22, 2010
Posts: 3435
    
  47

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

Joined: Jul 29, 2010
Posts: 378
i mistakenly wrote wrong package name treat both are same
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3435
    
  47

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.)
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: How to call oracle procedure and fetch returned data using Java?
 
Similar Threads
what does the following error mean
Clobs, Stored procedures and Oracle 8i
calling pl/sql in Java
Calling Oracle Stored Procedure
best programming practises of JDBC