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

[renu]

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

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

[Tolls]

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

[SFNYC]

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)

		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());
		}

[renu]

Thank You Sir ,

I will surely do .

[Tolls]

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.

[renu]

Hi Sir,

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 .

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

[renu]

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.

[SFNYC]

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

[SFNYC]

Try this:

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

[renu]

Thank You Sir ,

It works.

[Tolls]

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:

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.