Raising errors from Java stored procedures without "ORA-29532 Java call terminated by uncaught Java exception"
As I know, Oracle can't catch the Java errors directly. And the Oracle docs note of the ORA-29532 validate this idea:
ORA-29532: Java call terminated by uncaught Java exception: string
Cause: A Java exception or error was signaled and could not be resolved by the Java code.
Action: Modify Java code, if this behavior is not intended.
According to this text, I think you should handle the exception in the Java code.
You can solve that with these things:
- The return value of the Java function may be String - not void, and you may send the error description in the return variable.
- You may save the error to an Oracle table in the Java code and read it in the PL/SQL code.
- You just send the error to the Oracle user dump file with the
System.out.println();
.
Edit: Adam's final solution
This is roughly what I implemented based on the answer:
package mypackage;
public class MyClass {
public static final int SUCCESS = 1;
public static final int FAILURE = 0;
/**
* This method actually performs the business logic.
*/
public static void doSomething(String arg1, String arg2) throws SQLException {
// Actually do something...
}
/**
* This method is called from PL/SQL.
*/
public static int doSomething(String arg1, String arg2, int[] errorCode, String[] errorMessage) {
try {
doSomething(arg1, arg2);
return success();
} catch (SQLException e) {
return failure(e, errorCode, errorMessage);
}
}
private static int success() {
return SUCCESS;
}
private static int failure(SQLException e, int[] errorCode, String[] errorMessage) {
errorCode[0] = e.getErrorCode();
errorMessage[0] = e.getMessage();
return FAILURE;
}
}
Then in PL/SQL:
SUCCESS CONSTANT BINARY_INTEGER := 1;
FAILURE CONSTANT BINARY_INTEGER := 0;
SUBTYPE error_code_type IS BINARY_INTEGER;
SUBTYPE error_message_type IS VARCHAR2(1000);
PROCEDURE
raise_error_if_failure
(
status BINARY_INTEGER,
error_code ERROR_CODE_TYPE,
error_message ERROR_MESSAGE_TYPE
)
IS
BEGIN
IF status = FAILURE THEN
raise_application_error(error_code, error_message);
END IF;
END;
FUNCTION
do_something_in_java
(
arg1 VARCHAR2,
arg2 VARCHAR2,
error_code OUT ERROR_CODE_TYPE,
error_message OUT ERROR_MESSAGE_TYPE
)
RETURN BINARY_INTEGER
AS LANGUAGE JAVA
NAME 'mypackage.MyClass.doSomething(java.lang.String, java.lang.String, int[], java.lang.String[]) return int';
PROCEDURE
do_something
(
arg1 VARCHAR2,
arg2 VARCHAR2
)
IS
error_code ERROR_CODE_TYPE;
error_message ERROR_MESSAGE_TYPE;
BEGIN
raise_error_if_failure(
do_something_in_java(arg1, arg2, error_code, error_message),
error_code,
error_message
);
END;
Adam Paynter
I was born in Prince Edward Island, Canada and have been experimenting with programming since grade five. In 2007, I graduated with first class honors from the Bachelor of Computer Science program at the University of New Brunswick, Canada. I currently live in Plano, Texas with my wife and two daughters. I work as a software developer for Capital One. I still pursue a handful of personal projects during lunch hours and weekends.
Updated on June 14, 2020Comments
-
Adam Paynter almost 4 years
Suppose you have a Java class which defines a
copyFile(String, String)
method:public class FileSystem { public static void copyFile(String sourcePath, String destinationPath) throws IOException { // ignore the fact that I'm not properly managing resources... FileInputStream source = new FileInputStream(sourcePath); FileOutputStream destination = new FileOutputStream(destinationPath); copyStream(source, destination); source.close(); destination.close(); } private static void copyStream(InputStream source, OutputStream destination) throws IOException { byte[] buffer = new byte[1024]; int length; while ( (length = source.read(buffer)) != -1 ) { destination.write(buffer, 0, length); } } }
And suppose you wrap this in a Java stored procedure:
CREATE PROCEDURE copy_file (source_path VARCHAR2, destination_path VARCHAR2) AS LANGUAGE JAVA NAME 'FileSystem.copyFile(String, String)';
Now suppose you call the
copy_file
stored procedure and a JavaIOException
is thrown:BEGIN copy_file( '/some/file/that/does/not/exist.txt', '/path/to/destination.txt' ); END;
In the PL/SQL block, the error raised is:
ORA-29532 Java call terminated by uncaught Java exception
The error message also contains a description of the uncaught
Exception
, but it is still anORA-29532
. Is there a way to throw anException
from Java that dictates both the error code and the error message raised in PL/SQL?