Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    2

    Thumbs up Trouble Calling Stored Procedures in Java

    Hello All!

    I am working withe Java/Oracle 9i and I am trying to call a Stored Procedue from Oracle. I have all the components set up , however when I register my output var, I keep getting this complie error

    FP21M.java:7550: cannot resolve symbol
    symbol : variable BOOLEAN
    location: class java.sql.Types cs.registerOutParameter(1,java.sql.Types.BOOLEAN);


    Here is the code that I am trying to run

    try
    {
    // Call a procedure with one OUT parameter
    Statement stmt = conn.createStatement();
    CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(" + rc_14 + ")}");

    // Register the type of the OUT parameter
    cs.registerOutParameter(1, java.sql.Types.BOOLEAN);
    // Execute the stored procedure and retrieve the OUT value
    cs.execute();

    String outParam = cs.getString(1); // OUT parameter

    System.out.println("outParam value >" + outParam + "<");

    conn.close();
    }
    catch (java.lang.Exception ex)
    {
    ex.printStackTrace ();
    }

    Anyone out there run into this error before??

    I am running out of ideas...


    Thanks!

    Craig Butler

  2. #2
    Join Date
    Feb 2004
    Posts
    18

    Re: Trouble Calling Stored Procedures in Java

    Originally posted by scbutler1
    Hello All!

    I am working withe Java/Oracle 9i and I am trying to call a Stored Procedue from Oracle. I have all the components set up , however when I register my output var, I keep getting this complie error

    FP21M.java:7550: cannot resolve symbol
    symbol : variable BOOLEAN
    location: class java.sql.Types cs.registerOutParameter(1,java.sql.Types.BOOLEAN);


    Here is the code that I am trying to run

    try
    {
    // Call a procedure with one OUT parameter
    Statement stmt = conn.createStatement();
    CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(" + rc_14 + ")}");

    // Register the type of the OUT parameter
    cs.registerOutParameter(1, java.sql.Types.BOOLEAN);
    // Execute the stored procedure and retrieve the OUT value
    cs.execute();

    String outParam = cs.getString(1); // OUT parameter

    System.out.println("outParam value >" + outParam + "<");

    conn.close();
    }
    catch (java.lang.Exception ex)
    {
    ex.printStackTrace ();
    }

    Anyone out there run into this error before??

    I am running out of ideas...


    Thanks!

    Craig Butler
    When you are calling the procedure you should call it with a bind variable as follows:
    CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(?)}") ;

  3. #3
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    2

    Re: Trouble Calling Stored Procedures in Java

    Ok, I can do that.

    But here is my next question, how do you pass the needed parameters to it? The rc_14 used in the call is a string with all the parameters in it.

    This is the part that confuses me.

    Thanks for the reply!

  4. #4
    Join Date
    Feb 2004
    Posts
    18

    Re: Trouble Calling Stored Procedures in Java

    Originally posted by muffi
    When you are calling the procedure you should call it with a bind variable as follows:
    CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(?)}") ;
    I think a more suitale forum for your pisr would be the Java Forum. While calling a stored proceudure you pass the input parameters just as in a Prepared Statement. You can refer to the java.sun.com site for the api reference. If you are calling the statement in the following manner:
    conn.prepareCall("{? = call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(?)}") ; then you need to register the out parameter which is the first ?. Is it a stored procedure or a stored function that you are calling.

  5. #5
    Join Date
    Jan 2003
    Posts
    10

    Calling StoredProcedure in Java Code

    hi,

    You can call the stored procedure with the CallableStatement class. This class is the subclass of the PreparedStatement.

    Step 1: Call the stored procedure by Callable Statement.

    CallableStatement proc =
    connection.prepareCall("{ call empNotification(?, ?) }");

    The string passed to the prepareCall method is the procedure call specification. It specifies the name of the procedure to call and a ? for each parameter you need to specify.

    Step 2: Set the input parameters

    proc.setString(1, empName);
    proc.setInt(2, age);

    The empName and age is declared in the class.

    Step 3: call the execute statement
    proc.execute();

    Here is the complete snippet.

    Connection con = null;
    CallableStatement proc = null;

    try
    {
    con = connectionPool.getConnection();
    proc = con.prepareCall("{ call empNotification(?, ?) }");
    proc.setString(1, empName);
    proc.setInt(2, age);
    proc.execute();
    }
    finally
    {
    try
    {
    proc.close();
    }
    catch (SQLException e) {}
    con.close();
    }


    If your stored procedure returns a value then it must be registered with the registerOutParameter method.

    Hope this helps!

    Regards
    Babitha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •