Retrieving unexpected null from a callable statement OUT parameter

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

"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.

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:

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

Code:

---

connection = ods.getConnection();

---

to

Code:

---

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

---

4) compile and load class into oracle

5) i have linked P141_JAVABridge with P141_JB

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

Code:

---

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

---

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

From your IM to me:

Quote:

---

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:

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

Code:

---

connection = ods.getConnection();

---

to

Code:

---

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

---

4) compile and load class into oracle

5) i have linked P141_JAVABridge with P141_JB

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

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:

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.

you were right, thanks a lot

Quote:

---

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

Moved here from 'Java Programming'

db