Calling Oracle stored procedures in JRun container

[Bloo Barton]

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]

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:

CallableStatement cs = dbConnection.prepareCall("{call OWNER.MY_PACKAGE.MY_PROC(?,?)}");

Good Luck,
Avi.

[Bloo Barton]

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.

  
CallableStatement cs = dbConnection.prepareCall("{call MY_PACKAGE.GET_USER(?,?)}");
cs.registerOutParameter(2,OracleTypes.CURSOR);
cs.setString(1, "username");
cs.execute();
ResultSet rset = (ResultSet)cs.getObject(2);

The stored procedure signature is as like this.

PROCEDURE GET_USER(userName varchar2, data OUT sys_refcursor)

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?