Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Calling Oracle stored procedures in JRun container

 
Bloo Barton
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic