Call pl / sql function in java?

Question

So, I have a function that checks how many are canceled in my reservation 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 it 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:

 anonymous block completed Total no. of Cancellations: 1 

My question is: can someone help me call a function in JAVA, I tried, but 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); 

prints the same as in pl / sql. According to the docs of Connection#prepareCall() ,

Creates a CallableStatement object to invoke database stored procedures. The CallableStatement object provides methods for setting its IN and OUT parameters and methods for making a call to a stored procedure.

You can also pass parameters to a function. for ex,

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

will pass the values ​​of the function as an input parameter.

Hope this helps!

Answer 2

Prepare the called expression

Two formats are available: the familiar block syntax used by Oracle and the standard ANSI 92 syntax. For our CallableStatement vStatement = vDatabaseConnection.prepareCall( "begin ? := javatest( ?, ? ); end;" ); program, the block syntax is of the form: CallableStatement vStatement = vDatabaseConnection.prepareCall( "begin ? := javatest( ?, ? ); end;" );

The syntax of ANSI 92 is of the form:

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

a source

If you get an error below, you can use the first format.

total_cancellations is not a procedure or is an 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();