aspose file tools*
The moose likes JDBC and the fly likes Calling Oracle stored procedures in JRun container Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Calling Oracle stored procedures in JRun container" Watch "Calling Oracle stored procedures in JRun container" New topic
Author

Calling Oracle stored procedures in JRun container

Bloo Barton
Ranch Hand

Joined: May 09, 2005
Posts: 63
I'm trying to call stored procedures that exist in an Oracle database. My app is a web-app running in the JRun 4 container.

When I try to call the stored procedure an exception is thrown saying the stored procedure does not exist.

CallableStatement cs = dbConnection.prepareCall("{call MY_PACKAGE.MY_PROC(?,?)}");
cs.setString(1, "foo");
cs.registerOutParameter(2,java.sql.Types.INTEGER);
dbResultSet = cs.executeQuery();

My Stored procedure's signature looks like this

PROCEDURE MY_PROC(userLogon varchar2,userid OUT NUMBER)

It exists within the package MY_PACKAGE.

What am I doing wrong?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Bloo,
It sounds like the user that owns the MY_PACKAGE package is not the same as the user that is trying to access it from your java code. So either change the user in your java code, or grant permission for other users to access MY_PACKAGE. Note that if you choose the second option -- granting permissions to other users to use MY_PACKAGE -- then in your java code you should prefix your call to MY_PACKAGE with the name of the user that owns the package, for example:


Good Luck,
Avi.
Bloo Barton
Ranch Hand

Joined: May 09, 2005
Posts: 63
I'm trying to call stored procedures that exist in an Oracle database. My app is a web-app running in the JRun 4 container.

When I try to call the stored procedure an exception is thrown saying the stored procedure does not exist.

CallableStatement cs = dbConnection.prepareCall("{call MY_PACKAGE.MY_PROC(?,?)}");
cs.setString(1, "foo");
cs.registerOutParameter(2,java.sql.Types.INTEGER);
dbResultSet = cs.executeQuery();

My Stored procedure's signature looks like this

PROCEDURE MY_PROC(userLogon varchar2,userid OUT NUMBER)

It exists within the package MY_PACKAGE.
**********************************************************


I am able to call procedures now that only return a single value or only take in values with out returning in values.

When I try to call a procedure that returns a cursor though it does not work correctly.

My call to the stored procedure is setup as follows.



The stored procedure signature is as like this.


My call to the stored procedure seems to just hang forever at cs.execute();
The stored procedure is tested and working correctly...

Also, this only appears to be capable of retrieving a single row of data only. How can I get back a true result set containing multiple rows?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling Oracle stored procedures in JRun container