Call pl/sql function in java?

Question

So I've got a function that checks how many cancellations are in my booking table:

CREATE OR REPLACE FUNCTION total_cancellations
RETURN number IS
   t_canc number := 0;
BEGIN
   SELECT count(*) into t_canc
   FROM booking where status = 'CANCELLED';
   RETURN t_canc;
END;
/

To execute his in sql I use:

set serveroutput on
DECLARE
   c number;
BEGIN
   c := total_cancellations();
   dbms_output.put_line('Total no. of Cancellations: ' || c);
END;
/

My result is:

anonymous block completed
Total no. of Cancellations: 1

My question is can someone help me call the function in JAVA, I have tried but with no luck.

Answer 1

Java provides CallableStatements for such purposes .

CallableStatement cstmt = conn.prepareCall("{? = CALL total_cancellations()}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setInt(2, acctNo);
cstmt.executeUpdate();
int cancel= cstmt.getInt(1);
System.out.print("Cancellation is "+cancel);

will print the same as you do in the pl/sql. As per docs Connection#prepareCall(),

Creates a CallableStatement object for calling database stored procedures. The CallableStatement object provides methods for setting up its IN and OUT parameters, and methods for executing the call to a stored procedure.

You can also pass parameters for the function . for ex ,

conn.prepareCall("{? = CALL total_cancellations(?)}");
cstmt.setInt(2, value);

will pass the values to the function as input parameter.

Hope this helps !

Answer 2

Prepare a Callable Statement

There are two formats available, the familiar block syntax used by Oracle and the ANSI 92 standard syntax. In the case of our sample program, the block syntax has the form: CallableStatement vStatement = vDatabaseConnection.prepareCall( "begin ? := javatest( ?, ? ); end;" );

The ANSI 92 syntax has the form:

   CallableStatement vStatement = 
           vDatabaseConnection.prepareCall( "{ ? = call javatest( ?, ? )}");

source

If you receive the below error, you might want to use the first format.

total_cancellations is not a procedure or is undefined error.

Sample code.

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@xx.xxx.xx.xxx:1521:xxx", "user","pass");
CallableStatement cstmt = conn.prepareCall("begin ? := TEST_FUNC(?,?); end;");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "Test");
cstmt.setInt(3, 1001);
cstmt.execute();
int result = cstmt.getInt(1);
System.out.print("Result: " + result);
cstmt.close();
conn.close();