Results 1 to 11 of 11
  1. #1
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Question Call a PL/SQL function that returns a boolean type using callablestatement.

    Hi

    Anyone pls tell me , how to
    "I want to call a PL/SQL function that returns a boolean type, does anyone know how I can do this using a CallableStatement?"

    Java Code:
     // Call a function with two IN parameters; the function returns a boolean value true or false.
    
    		CallableStatement proc_stmt = null;
    		try {
    			[COLOR="Red"]proc_stmt = connection.prepareCall("{? = call PKG_WORK.F_VLD_VALUE(?,?)}");	[/COLOR]//			Register the type of the return value		
    			proc_stmt.registerOutParameter(1, java.sql.Types.BIT); // I HAVE TRIED EVERYTHING HERE 
    		   
    			proc_stmt.setInt(2,facilityVALUE);
    			proc_stmt.setInt(3,year);				
    //			Execute and retrieve the returned value
    			proc_stmt.execute();
    			boolean retValue = proc_stmt.getBoolean(1);	 
    			//print the results	
    			System.out.println("the boolean value is :"+retValue);
    			System.out.println(" ");		
    			proc_stmt.close();         
    		}catch (SQLException sqlException1) {
    			System.out.println("SQL Exception - Query Two ");
    			System.out.println(sqlException1.getMessage());
    		}
    
    
    
    ERROR :-
    Exception in thread "main" java.lang.NullPointerException
    	at test.test.main(test.java:50)
    50 th line is red color line . It points to that line .
    
    
    
    
    Can anyone tell me , how to get a boolean return type from a pl/sql function in java using callablestatement.
    
    Thank You .

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

    Default

    This has nothing to do with your boolean return value and everything to do with your connection being null.

  3. #3
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    I would suggest you wrap your PL/SQL function that returns a boolean value in a CASE statement that sets a number. You can then check your number return value and set your Java boolean variable accordingly.

    Try something like this (Note: not tested)

    Java Code:
    		CallableStatement proc_stmt = null;
    		try {
    			proc_stmt = connection.prepareCall(
    [B]			              "BEGIN " +
    			                  "  ? = CASE PKG_WORK.F_VLD_VALUE(?,?) "
    			               "         WHEN TRUE THEN 1 " +
    			               "         ELSE 0 " +
    			               "         END; " +
    			               "END; "
    			               );[/B]
    
    			// Register the type of the return value
    [B]			proc_stmt.registerOutParameter(1, java.sql.Types.INTEGER); [/B]
    		   
    			proc_stmt.setInt(2,facilityVALUE);
    			proc_stmt.setInt(3,year);				
    
    //			Execute and retrieve the returned value
    			proc_stmt.execute();
    
    [B]			int funcRetValue = proc_stmt.getInt(1);
    			if (funcRetValue == 1) {
    			   retValue = true;
    			} else {
    			   retValue = false;
    			}[/B]
    			
    			//print the results	
    			System.out.println("the boolean value is :"+retValue);
    			System.out.println(" ");		
    			proc_stmt.close();         
    		}catch (SQLException sqlException1) {
    			System.out.println("SQL Exception - Query Two ");
    			System.out.println(sqlException1.getMessage());
    		}

  4. #4
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Default

    Thank You Sir ,

    I will surely do .

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

    Default

    Why?
    Why on earth do that?
    There is a getBoolean() method on CallableStatement for a reason, and it is to handle BIT and BOOLEAN fields.

    Wrapping like that is utterly pointless in this case.

  6. #6
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Question

    Hi Sir,

    Java Code:
    proc_stmt.registerOutParameter(1, java.sql.Types.BIT); // I HAVE TRIED EVERYTHING HERE 
    			proc_stmt.setInt(2,facilityValue);
    			proc_stmt.setInt(3,year);				
    //			Execute and retrieve the returned value
    			[COLOR="Red"]proc_stmt.execute();[/COLOR]			boolean retValue = proc_stmt.getBoolean(1);
    I get an error :-
    In debug mode ...it reaches the red line above and stops execution .

    with the following errors :
    SQL Exception - Query Two
    ORA-06550: line 1, column 13:
    PLS-00382: expression is of wrong type
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Note: The function works in SQL PLUS ...their is no error in function code .
    Something wrong in calling a boolean function in java

    Anyhelp is appreciated .

    ------------------------------------

  7. #7
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Default

    Sir,

    I tried with
    case statement ..it points the error to the

    proc_stmt = connection.prepareCall(
    "BEGIN " +
    " ? = CASE PKG_WORK.F_VLD_VALUE(?,?) "
    " WHEN TRUE THEN 1 " + " ELSE 0 " +
    " END; " +
    "END; "
    );


    To the red line ..not knowing how to correct the case syntax...

    Any help is appreciated.

  8. #8
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    Due to a restriction in the OCI layer, the JDBC drivers do not support the
    passing of BOOLEAN parameters to PL/SQL stored procedures.
    The java boolean type does not match with the PL/SQl boolean type. The Oracle documentation indicates that BOOLEAN is a PL/SQL
    type only, and there is no mapping between the "java.lang.Boolean"
    class and the PL/SQL BOOLEAN data type.

    From Oracle:
    Because there is no BOOLEAN database type, when you use getBoolean() a datatype conversion always occurs. The getBoolean() method is supported only for numeric columns (BIT, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, CHAR, VARCHAR, or LONGVARCHAR). When applied to these columns, getBoolean() interprets any zero (0) value as false, and any other value as true. When applied to any other sort of column, getBoolean() raises the exception java.lang.NumberFormatExceptionCheck with Longxing!.

  9. #9
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    Try this:

    Java Code:
    " ? [B]:[/B]= CASE PKG_WORK.F_VLD_VALUE(?,?) "

  10. #10
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Smile

    Thank You Sir ,

    It works.

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

    Default

    Oh crap.
    When I saw the boolean thing I thought we were talking about SQLServer.

    Simply didn't register the PL/SQL stuff in the OP.
    Any PL/SQL defined type is simply not valid when it comes to JDBC. This is usually seen when types declared in packages are returned...and there's no way to map them at all.

    This is all going to get a bit nasty, but would:
    Java Code:
    SELECT CASE PKG_WORK.F_VLD_VALUE(?,?)
     WHEN TRUE THEN 1  ELSE 0 
     END FROM dual;
    work? Not got an Oracle instance here so can't check.

    I wouldn't be surprised if there's problems gettig JDBC to map to the function parameters.

Similar Threads

  1. Replies: 4
    Last Post: 02-16-2011, 04:31 AM
  2. Replies: 2
    Last Post: 10-15-2010, 05:09 AM
  3. function call error
    By peter_thm in forum New To Java
    Replies: 2
    Last Post: 01-13-2010, 12:57 PM
  4. Rewrite as a function so it can call from main.
    By thangli in forum New To Java
    Replies: 2
    Last Post: 11-30-2008, 06:26 AM
  5. help with System.exit (1) function call
    By katie in forum Advanced Java
    Replies: 2
    Last Post: 08-06-2007, 08:03 PM

Posting Permissions

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