Retrieving unexpected null from a callable statement OUT parameter

**czajah** · 01-06-2012, 11:43 AM

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 Release 10.2.0.5.0 - 64bi (the same behaviour on 11g was observed)
JRE version used for tests 1.4.2_04.
JDBC version 10.2.0.3.0

**Tolls** · 01-06-2012, 12:03 PM

"which is linked to a static method"
What does that mean?

Can you show some of the actual code, as I can't visualise what it is you are doing in the two cases.

**czajah** · 01-06-2012, 01:34 PM

ok, my english may not be good enought to explain this clearly but i will try

1) i have write some java code using as usual my IDE:

Java Code:

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

Java Code:

 connection = ods.getConnection();

to

Java Code:

connection = DriverManager.getConnection("jdbc:default:connection:");

4) compile and load class into oracle

5) i have linked P141_JAVABridge with P141_JB

Java Code:

create or replace PROCEDURE P141_JB () IS LANGUAGE JAVA NAME 'x.y.z.P141_JAVABridge.execute()';

6) i executed P141_JB and got NullPointerException at

Java Code:

((Custom_T_SQLData)call.getObject(2)).responseStatus

**czajah** · 01-06-2012, 01:46 PM

i wish i could answer here but i can't because of censorship!?
yo, moderator, where is my answer?

**Tolls** · 01-06-2012, 02:36 PM

From your IM to me:

Originally Posted by czajah

ok, my english may not be good enought to explain this clearly but i will try

1) i have write some java code using as usual my IDE:

Java Code:

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

Java Code:

connection = ods.getConnection();

to

Java Code:

connection = DriverManager.getConnection("jdbc:default:connection:");

4) compile and load class into oracle

5) i have linked P141_JAVABridge with P141_JB

Java Code:

create or replace PROCEDURE P141_JB () IS LANGUAGE JAVA NAME 'x.y.z.P141_JAVABridge.execute()';

6) i executed P141_JB and got NullPointerException at

Java Code:

((Custom_T_SQLData)call.getObject(2)).responseStatus

From the Oracle docs:
"
A CallableStatement object lets you call stored procedures. It contains the call text, which can include a return parameter and any number of IN, OUT, and IN OUT parameters. The call is written using an escape clause, which is delimited by braces ({}).
"
WHich implies that, when used against the internal JDBC driver, you should have a statement like:

Java Code:

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

Of course if that doesn't do the trick, then I'm all out of ideas...not had to do much work on Java SPs.

**czajah** · 01-06-2012, 10:38 PM

you were right, thanks a lot

**DarrylBurke** · 01-08-2012, 08:54 AM

Originally Posted by czajah

i wish i could answer here but i can't because of censorship!?
yo, moderator, where is my answer?

Since you're new here, the forum software may mark any of your posts to be approved by a moderator before it shows in the forum. That's not censorship.

But then you went into a rapid-fire spree of adding new posts, each one a virtual duplicate of the others, which matches the typical behavior pattern of a spammer. Actually, I'm surprised the forum software didn't block the post I'm now replying to.

In future, at least until you become a 'Senior Member' please be patient. There are moderators around the world in various time zones and it shouldn't take too long for your post to be approved.

db