CODEWITHSUNDEEP
javaoracle-databaseservletsplsql
mndeveci
mndeveci

Reputation: 301

Procedure call from java throws exception

I have a PL/SQL procedure in Oracle database. And that procedure covers exception case at the end of the script with;

EXCEPTION WHEN OTHERS THEN
  v_error_text :=  'Error case';

The v_error_text variable is an out parameter to log the exception at java side. I call this script from a java web application. Sometimes, it throws exception, not from Oracle side but from java. 

java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

As it says, it throws an exception possibly converting a character to number. But since procedure has an exception handling, it must not throws this error from java side, it must throw it from Oracle side, and I will be able to get the error text that I send as out parameter (v_error_text).

How this could be possible even I have declared EXCEPTION case, any ideas?

Thanks a lot.

Edit:

I had not added the java code, here we can see it:

package oracle;

import java.sql.Connection;
import java.sql.DriverManager;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleDriver;

public class MyTestClass {

    static String URL = "jdbc:oracle:thin:@//127.0.0.1:1521/test";
    static String USER = "myuser";
    static String PASS = "mspass";

    public static void main(String[] args) throws Exception {
        DriverManager.registerDriver(new OracleDriver());

        Connection jConn = null;
        OracleCallableStatement jStmt = null;

        jConn = (OracleConnection) DriverManager
                .getConnection(URL, USER, PASS);

        jStmt = (OracleCallableStatement) jConn
                .prepareCall("{ call MYPACKET.MYPROCEDURE(?, ?, ?) }");
        jStmt.setString(1, "Test 1");
        jStmt.setString(2, "Test 2");

        jStmt.execute();

        jConn.close();
    }

}

Upvotes: 0

Views: 2532

Answers (2)

Arne Burmeister
Arne Burmeister

Reputation: 20594

The error seems to occur before the procedure runs so the EXCEPTION WHEN has no effect. You have not show the PL/SQL code of the procedure so I am not sure, but one or more of the three parameters need to be a number and you pass a not convertible value as parameter.

The JDBC driver does not know anything about the procedure (like me ;-) and passes the value to the database. The database knows the definition of the procedure and tries to convert the parameters and fails. The error occurs outside the procedure and is passed back as an exception to the calling java code.

Upvotes: 2

Sam Nunnally
Sam Nunnally

Reputation: 2321

Sounds like the Java code is not correctly validating inputs on the stored proc. You will want to validate that the input types match what is defined in the stored proc. The exception you are seeing is not occurring in the stored proc, it is happening in Java before the stored proc is being executed.

Upvotes: 1

Related Questions