I have two procedures: a java stored procedure JAVA_P() which is linked to a static method O.execute() and a pl/sql procedure SQL_P(in_param IN CUSTOM_TYPE_1, out_param OUT CUSTOM_TYPE_2).
JAVA_P calls SQL_P with CallableStatement.
And now a big WTF: When I run O.execute() outside oracle (from external jvm) received out_param is set as expected. When I run O.execute() as JAVA_P() (using oracle built-in jvm) then out_param is set to null (SQL_P is executed without exception and output parameter should be set).
Do you have any ideas why this happens?
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi JRE version used for tests 1.4.2_04. JDBC version 10.2.0.3.0
@Juergen Hartelt my english may not be good enought to explain this clearly but i will try this way:
1) i have write some java code using as usual my IDE:
public class P141_JAVABridge
{
public static void execute()
{
String databaseDriver = "oracle.jdbc.driver.OracleDriver";
String databaseUrl = "jdbc:oracle:thin:@xxx:1521:orcl";
String databaseUsername = "xxx";
String databasePassword = "xxx";
ods.setDriverType(databaseDriver);
ods.setURL(databaseUrl);
ods.setUser(databaseUsername);
ods.setPassword(databasePassword);
connection = ods.getConnection();
.... some code
map.put("custom_T",Custom_T_SQLData.class);
CallableStatement call = connection.prepareCall("call P141(?,?)");
call.setObject(1,inputObjectReference);
call.registerOutParameter(2,OracleTypes.STRUCT,"custom_T");
call.execute();
.... some code
}
}
2) i run this code - wooha! it works
3) i have changed
connection = ods.getConnection();
to
connection = DriverManager.getConnection("jdbc:default:connection:");
4) compile and load class into oracle
5) i have linked P141_JAVABridge.execute() with P141_JB
create or replace PROCEDURE P141_JB () IS LANGUAGE JAVA NAME 'x.y.z.P141_JAVABridge.execute()';
6) then i executed P141_JB
SET SERVEROUTPUT ON;
BEGIN
...some code
P141_JB();
...some code
END;
and got NullPointerException at
((Custom_T_SQLData)call.getObject(2)).responseStatus
I have done as Tolls adviced me and I have put "call P141(?,?)" into braces:
TADA! It works.