Results 1 to 8 of 8
  1. #1
    czajah is offline Member
    Join Date
    Jan 2012
    Posts
    7
    Rep Power
    0

    Default 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

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,016
    Rep Power
    20

    Default Re: Retrieving unexpected null from a callable statement OUT parameter

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

  3. #3
    czajah is offline Member
    Join Date
    Jan 2012
    Posts
    7
    Rep Power
    0

    Default Re: Retrieving unexpected null from a callable statement OUT parameter

    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

  4. #4
    czajah is offline Member
    Join Date
    Jan 2012
    Posts
    7
    Rep Power
    0

    Default Re: Retrieving unexpected null from a callable statement OUT parameter

    i wish i could answer here but i can't because of censorship!?
    yo, moderator, where is my answer?
    Last edited by czajah; 01-06-2012 at 01:53 PM.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,016
    Rep Power
    20

    Default Re: Retrieving unexpected null from a callable statement OUT parameter

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

  6. #6
    czajah is offline Member
    Join Date
    Jan 2012
    Posts
    7
    Rep Power
    0

    Default Re: Retrieving unexpected null from a callable statement OUT parameter

    you were right, thanks a lot

  7. #7
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,252
    Rep Power
    19

    Default Re: Retrieving unexpected null from a callable statement OUT parameter

    Quote Originally Posted by czajah View Post
    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
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  8. #8
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,252
    Rep Power
    19

    Default Re: Retrieving unexpected null from a callable statement OUT parameter

    Moved here from 'Java Programming'

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

Similar Threads

  1. Conditional Statement with null
    By Bowsan22 in forum New To Java
    Replies: 17
    Last Post: 09-19-2011, 03:42 AM
  2. Switch Statement/Parameter Passing
    By spmooney@hotmail.co.uk in forum NetBeans
    Replies: 1
    Last Post: 01-06-2010, 01:50 PM
  3. Sql string with callable statement..
    By nathan in forum JDBC
    Replies: 1
    Last Post: 09-24-2008, 01:41 AM
  4. Retrieving a parameter through URL.
    By hisouka in forum Java Servlet
    Replies: 1
    Last Post: 09-06-2008, 12:45 PM
  5. statement null pointer exception
    By bbq in forum JDBC
    Replies: 1
    Last Post: 07-05-2007, 04:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •