java stored procedure problems

[Chris Staten]

Hey all, at this moment I�m so confused about creating/using java stored procedures in an Oracle database (9i) that I may not even know how to ask a logical question�so please bear with me.

I�m coding a sample JSP to try to understand how java stored procedures work, I�m using a basic MVC architecture to do this. I start with an index.jsp view, it gets needed SQL parameters. I forward the parameters to my controller FunctionTest.java. The controller makes the call to the function (select_address) in the database (it also gets a database connection from the model), and forwards the result on to the result.jsp view.

Simple as this seems when I picture it, I can�t seem to make it work. Here is the code and the error from the database:

FunctionTest.java (the controller), this is a fragment:

 
public void doPost(HttpServletRequest request,
                      HttpServletResponse response)
          throws IOException, ServletException {
 
String addrId = request.getParameter("ID");
String type = request.getParameter("type");
String entity = request.getParameter("ent");
 

OraConn oracleConn = new OraConn();
Connection conn = oracleConn.getConn();

try{
 
java.sql.CallableStatement cstmt = conn.prepareCall("{call SELECT_TEST.SELECT_ADDRESS(?,?,?)}");
cstmt.setString(1, addrId);
cstmt.setString(2, type);
cstmt.setString(3, entity);
 
ResultSet query = cstmt.executeQuery();


request.setAttribute("resultSet", query);
RequestDispatcher view = request.getRequestDispatcher("result.jsp");
view.forward(request, response);

}catch (SQLException SQLE){
System.out.println("Threw the following exception in the FunctionTest class: ");
System.out.println(SQLE);
}
}
 

TestSelectAddress.java (the stored procedure):

 
import java.sql.*;                  
 
public class TestSelectAddress
{
    public static ResultSet sELECT_ADDRESS ( java.lang.String addrId,
                                        java.lang.String myType,
                                        java.lang.String entity) throws SQLException, Exception
    {
        // Get connection to the database
        Connection con = DriverManager.getConnection("jdbc :mrgreen: efault:connection");
        PreparedStatement stmt = null;
        String sql;
ResultSet rs = null;
 
        sql = "SELECT"
            + "    SCHEMA.TABLE_NAME.ADDR_1 AS ADDR_1,"
            + "    SCHEMA.TABLE_NAME.ADDR_2 AS ADDR_2,"
            + "    SCHEMA.TABLE_NAME.ADDR_3 AS ADDR_3,"
            + "    SCHEMA.TABLE_NAME.CITY AS CITY,"
            + "    SCHEMA.TABLE_NAME.STATE_ID AS STATE_ID,"
            + "    SCHEMA.TABLE_NAME.ZIP AS ZIP,"
            + "    SCHEMA.TABLE_NAME.ZIP_PLUS AS ZIP_PLUS"
            + " FROM"
            + "    SCHEMA.TABLE_NAME"
            + " WHERE"
            + "    ("
            + "      ( SCHEMA.TABLE_NAME.ADDR_ID =  ?  )"
            + "  AND ( SCHEMA.TABLE_NAME.ADDR_TYPE =  ?  )"
            + "  AND ( SCHEMA.TABLE_NAME.ADDR_ENTITY =  ?  )"
            + "    )";
        stmt = con.prepareStatement( sql );
        stmt.setString( 1, addrId );
        stmt.setString( 2, myType );
        stmt.setString( 3, entity );
        rs = stmt.executeQuery();
return rs;
    }
}
/

The PL/SQL wrapper code

 
CREATE OR REPLACE PACKAGE SELECT_TEST AS
TYPE myCursor IS REF CURSOR;
FUNCTION SELECT_ADDRESS(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2) 
  return myCursor 
AS
  LANGUAGE java 
    NAME 'TestSelectAddress.sELECT_ADDRESS(java.lang.String, java.lang.String, java.lang.String) return java.sql.ResultSet';
 
end;
 

Error returned from the database

 
 java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'SELECT_ADDRESS' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 

The view pages and model code don�t seem to have any problems so I�m not posting that code unless someone requests it.

[Jeremy Wilson]

Not sure if this will help but, can you run the procedure in sql plus and get the result. If so I would try prefixing the call with the user name

user.package.procedure

I don't see where you are logging in with a user name and password for the connection so you may need the user to identify the schema which has the applicable package and procedure.

[Avi Abrami]

Chris,
Pardon me if I am not telling you something you don't already know, but I couldn't find the information in your post.

Have you loaded the "TestSelectAddress" class into the database?

Good Luck,
Avi.

[Chris Staten]

Thanks for the replies!

Jeremy, I cannot run the stored procedure through my IDE (Toad). I�m not an expert with Toad by any stretch, but I think I�m attempting to test the stored procedure the correct way. I haven�t touched this problem since Friday so I�ll try to test the stored procedure in Toad again and I�ll post the error. Also, my username and password are provided in the datasource settings in my container (WebSphere). The connection settings are made as part of the code in the MVC model (OraConn.java).


Avi, I created TestSelectAddress.java in an IDE and then compiled it with the javac utility. After it compiled without errors I used the loadjava utility provided by Oracle to load the class into the database as an object.

[Chris Staten]

I still don�t have this figured out, but I think I�m moving in the right direction. Here is a quick overview on my understanding of java stored procedures: in Oracle (I don�t know about other DBs) a java stored procedure is an object in the database. You need to publish/make it accessible by using PL/SQL to call the java class.

OK, some of my problem may be coming from the fact that I�m thinking in terms of pass-by-value when in fact PL/SQL uses pass-by-reference. The reason that I�m thinking that this may be a problem is because of what I read on Oracle's site:

 /**
  * This method finds out the Room Details namely 'Number of Rooms Available'
  * and 'Standard Room Rates' for a given Hotel and Room Type
  *
  * Note: 1) Input Parameters to this method are hotelId and roomType where as
  *          numRoomsAvailable and standardRoomRate are output parameters.
  *       2) Corresponding to IN OUT or OUT parameters of PL/SQL procedures,
  *          the Java Methods must have parameters that are a one-element array.
  */
 
  public static void getRoomDetails(String hotelId, String roomType,
                    int[] numRoomsAvailable, float[] standardRoomRate) {

This code fragment�s comments state that the parameters numRoomsAvailable and standardRoomRate are �OUT� parameters. This makes me think that the PL/SQL code still has a variable referencing the actual object that is sent in as an arg to the java method. With this in mind I�ve changed my code to the following:

public class SELECT_ADDRESS
{
    public static void sELECT_ADDRESS ( String addrId,
                                        String type,
                                        String entity,
                                        ResultSet[] rs ) throws SQLException, Exception
    {
        // Get connection to the database
        Connection con = DriverManager.getConnection("jdbc :mrgreen: efault:connection");
        PreparedStatement stmt = null; 
        String sql;
 
        sql = "SELECT"
            + "    SCHEMA.TABLE_NAME.ADDR_1 AS ADDR_1,"
            + "    SCHEMA.TABLE_NAME.ADDR_2 AS ADDR_2,"
            + "    SCHEMA.TABLE_NAME.ADDR_3 AS ADDR_3,"
            + "    SCHEMA.TABLE_NAME.CITY AS CITY,"
            + "    SCHEMA.TABLE_NAME.STATE_ID AS STATE_ID,"
            + "    SCHEMA.TABLE_NAME.ZIP AS ZIP,"
            + "    SCHEMA.TABLE_NAME.ZIP_PLUS AS ZIP_PLUS"
            + " FROM"
            + "    SCHEMA.TABLE_NAME"
            + " WHERE"
            + "    ("
            + "      ( SCHEMA.TABLE_NAME.ADDR_ID =  ?  )"
            + "  AND ( SCHEMA.TABLE_NAME.ADDR_TYPE =  ?  )"
            + "  AND ( SCHEMA.TABLE_NAME.ADDR_ENTITY =  ?  )"
            + "    )";
        stmt = con.prepareStatement( sql );
        stmt.setString( 1, addrId );
        stmt.setString( 2, type );
        stmt.setString( 3, entity );
        rs[0] = stmt.executeQuery();
        if (con != null) con.close();
    }
}
 

The changes that I made are accepting a ResultSet array as a param, and not returning one. I now just place the result of the query into the first index of the array rs[]. I am hoping that because the PL/SQL code still has a reference to rs[] that it will not simply go away when the method ends and my local object falls out of scope.

I�ll post my results.

[Chris Staten]

I got it figured out! Unfourtunatly I�m headed away from the computer for the next week or so (and I�m late for an appointment right now). I�ll post my working code when I get back around the second week of May.

The main reason that I want to post my working code is because every forum post (not java ranch) that I found when searching about this only had the problem. No one posted the answer! I know those people must have found a solution.

I�m going to try to post an answer that makes sense to other people (not just me) so that people searching for answers about returning a ResultSet from an Oracle stored procedure/function using JDBC may not have to go through what I did.

[Chris Staten]

OK, here comes my solution to why I was having problems with Java stored procedures/functions (sorry that it�s taken me a few months to get back to this). But first let me say two things.

I hope this helps anyone who is searching for how to create a java stored procedure in Oracle. When I googled this out I found (multiple times) other people with exact same question that I had, but no answers.

If I am mistaken in my solution PLEASE point that out so that neither I nor anyone else who is new at stored procedures is doing this wrong.

One of my major problems was that I was thinking in java terms while writing my java stored procedure and not in Oracle/java terms. What I mean is that when writing a java stored procedure to be placed as an object in an Oracle database you aren�t necessarily dealing with java in the same way that you would be if you were in a standard JVM.

For example, when I converted the stored procedure above (TestSelectAddress) I changed the method signature of the sELECT_ADDRESS method from:

public static void sELECT_ADDRESS(java.lang.String addrId, 
java.lang.String myType,
java.lang.String entity,
java.sql.ResultSet rs)

to:

public static ResultSet sELECT_ADDRESS(java.lang.String addrId, 
java.lang.String myType,
java.lang.String entity)

This was due to the fact that I wanted to return a ResultSet, and as we all know (normally) in java if you want to return something you declare the return type in the method signature the way I did in the first of the two methods above. BUT, in Oracle, using a java stored procedure, you would declare it as an incoming parameter and simply set that parameter to whatever value you want returned and be done with it. As a matter of fact, if you wanted to do any clean up of things such as possible open connections to a database you could do it after setting the parameter (rs in this case) to the value you wanted to return. This is opposed to the normal thinking of returning something in java where the method ends when you hit the return statement. To use an incoming arg to return a value in a java stored procedure in Oracle you need to declare it as an �out� parameter in your PL/SQL, then set the variable of that parameter in your stored procedure to the value that you want to return. Because this didn�t feel natural to me I converted all of my java stored procedures over to what I think of as java stored functions . All this really means is that my PL/SQL wrapper is declared as a function and not a procedure. By using a function to call my java stored procedure I can return a value from my java code and the database can handle it. My working version of the java stored procedure is in the post below. I have read in a few places that if you plan on returning something from the database you would normally want to use a function as opposed to a procedure (as your PL/SQL wrapper).

I hope this helps and doesn�t confuse people further. If anyone ever comes across this post and is just as confused about java stored procedures in Oracle as I was please don�t hesitate to post to this thread and use my listed email address to ask me to come read it.

[Chris Staten]

Here is the working PL/SQL wrapper, java stored procedure, and calling Servlet code:

PL/SQL wrapper:

CREATE OR REPLACE FUNCTION wrapper2_select_test_address (param1 in VARCHAR2, param2 in VARCHAR2, param3 in VARCHAR2)
   RETURN cur_test.cur
IS
   LANGUAGE JAVA
   NAME 'TEST_SELECT_ADDRESS.test_sELECT_ADDRESS(java.lang.String, java.lang.String, java.lang.String) return java.sql.ResultSet';
/

NOTE: you need to create a REF CURSOR before you can use the above function.

CREATE OR REPLACE PACKAGE cur_test
IS
   TYPE cur IS REF CURSOR;
END cur_test;
/

Java stored procedure:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "TEST_SELECT_ADDRESS" AS
 
import java.sql.*;                   // JDBC classes
import oracle.jdbc.driver.*;
 
public class TEST_SELECT_ADDRESS
{
    public static ResultSet test_sELECT_ADDRESS ( String addrId,
                                        String myType,
                                        String entity ) throws SQLException, Exception
    {
        //declare variables
        Connection con = null;
ResultSet rs = null;
try {
 // Register Oracle JDBC driver
               DriverManager.registerDriver(new OracleDriver());
      
      //obtain the default connection
con = new OracleDriver().defaultConnection();

// Create any subsequent statements as a REF CURSOR
      ((OracleConnection)con).setCreateStatementAsRefCursor(true);
      
      
        PreparedStatement stmt = null;
        String sql;
 
  sql = "SELECT"
               + "    SCHEMA.TABLE_NAME.ADDR_1 AS ADDR_1,"
               + "    SCHEMA.TABLE_NAME.ADDR_2 AS ADDR_2,"
         + "    SCHEMA.TABLE_NAME.ADDR_3 AS ADDR_3,"
               + "    SCHEMA.TABLE_NAME.CITY AS CITY,"
               + "    SCHEMA.TABLE_NAME.STATE_ID AS STATE_ID,"
               + "    SCHEMA.TABLE_NAME.ZIP AS ZIP,"
               + "    SCHEMA.TABLE_NAME.ZIP_PLUS AS ZIP_PLUS"
               + " FROM"
               + "    SCHEMA.TABLE_NAME "
               + " WHERE"
               + "    ("
               + "      ( SCHEMA.TABLE_NAME.ADDR_ID =  ?  )"
               + "  AND ( SCHEMA.TABLE_NAME.ADDR_TYPE =  ?  )"
               + "  AND ( SCHEMA.TABLE_NAME.ADDR_ENTITY =  ?  )"
               + "    )";
 
           stmt = con.prepareStatement( sql );
           stmt.setString( 1, addrId );
   stmt.setString( 2, myType );
           stmt.setString( 3, entity );
 rs = stmt.executeQuery();
           return rs;
}catch (SQLException e) {
   e.printStackTrace();
}
return null;
    }
}
/

Servlet code (calling fragment):

CallableStatement cstmt = conn.prepareCall(
"begin ? := WRAPPER2_SELECT_TEST_ADDRESS(?,?,?); end;");
 
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setString(2, addrId);
cstmt.setString(3, type);
cstmt.setString(4, entity);

[ July 07, 2005: Message edited by: Chris Staten ]

[Chris Staten]

I'm back!

It seems that there were some other lurking issues that I didn't think about when setting up my Java Stored Procedures this way. Of course I found out about these issues in production rather than testing.

The problem that I ran into is

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "TEST_SELECT_ADDRESS" AS
 
import java.sql.*;                   // JDBC classes
import oracle.jdbc.driver.*;
 
public class TEST_SELECT_ADDRESS
{
    public static ResultSet test_sELECT_ADDRESS ( String addrId,
                                        String myType,
                                        String entity ) throws SQLException, Exception
    {
        //declare variables
        Connection con = null;
ResultSet rs = null;
try {
 // Register Oracle JDBC driver
               DriverManager.registerDriver(new OracleDriver());
      
      //obtain the default connection
con = new OracleDriver().defaultConnection();

// Create any subsequent statements as a REF CURSOR
      ((OracleConnection)con).setCreateStatementAsRefCursor(true);
      
      
        PreparedStatement stmt = null;
        String sql;
 
  sql = "SELECT"
               + "    SCHEMA.TABLE_NAME.ADDR_1 AS ADDR_1,"
               + "    SCHEMA.TABLE_NAME.ADDR_2 AS ADDR_2,"
         + "    SCHEMA.TABLE_NAME.ADDR_3 AS ADDR_3,"
               + "    SCHEMA.TABLE_NAME.CITY AS CITY,"
               + "    SCHEMA.TABLE_NAME.STATE_ID AS STATE_ID,"
               + "    SCHEMA.TABLE_NAME.ZIP AS ZIP,"
               + "    SCHEMA.TABLE_NAME.ZIP_PLUS AS ZIP_PLUS"
               + " FROM"
               + "    SCHEMA.TABLE_NAME "
               + " WHERE"
               + "    ("
               + "      ( SCHEMA.TABLE_NAME.ADDR_ID =  ?  )"
               + "  AND ( SCHEMA.TABLE_NAME.ADDR_TYPE =  ?  )"
               + "  AND ( SCHEMA.TABLE_NAME.ADDR_ENTITY =  ?  )"
               + "    )";
 
           stmt = con.prepareStatement( sql );
           stmt.setString( 1, addrId );
   stmt.setString( 2, myType );
           stmt.setString( 3, entity );
 rs = stmt.executeQuery();
          [B]
}catch (SQLException e) {
/*
*I'm not sure how to get a message out of the Oracle JVM
*so I don't know what to do here.
**/
}finally{
try{
if(rs != null) rs.close();
}catch (SQLException sqlEx)
/*
*I'm not sure how to get a message out of the Oracle JVM
*so I don't know what to do here.
**/
}
}

return rs;
[/B]
    }
}

I'd love some feed back if anyone sees a problem with this change.

Thanks!

EDIT: It looks as if bold text doesn't work well inside the code block text. The changes start after the

rs = stmt.executeQuery();

line.
[ August 02, 2006: Message edited by: Chris Staten ]