JDBC- Calling Oracle database FUNCTION - CallableStatement example in java



In this tutorial we will learn how to call Oracle database FUNCTION and passing parameter - CallableStatement program/example in java JDBC.


The java.sql.CallableStatement is an interface which can be used to execute SQL FUNCTIONS in java.

JDBC tutorial - What is java.sql.CallableStatement in java




--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22), SALARY number(8), CREATION_DATE date);

insert into EMPLOYEE values(11, 'ankit', 12000, sysdate);

commit;

--Function accepts id as IN parameter and returns salary
create or replace
FUNCTION MYFUNCTION(
     p_id IN EMPLOYEE.ID%TYPE)
RETURN NUMBER
IS
 p_salary EMPLOYEE.SALARY%TYPE := 0;
BEGIN
 SELECT SALARY
 INTO p_salary
 FROM EMPLOYEE where ID = p_id;
 RETURN p_salary;
END;


--If table already exists then execute the DROP command >
drop table EMPLOYEE;




Must read : Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter



Example/ Full Programs JDBC- Calling Oracle database FUNCTION - CallableStatement example in java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class CallableStatementExampleFunction {
public static void main(String... arg) {
     Connection con = null;
     CallableStatement callableStmt = null;
     try {
          // registering Oracle driver class
          Class.forName("oracle.jdbc.driver.OracleDriver");
          // getting connection
          con = DriverManager.getConnection(
                   "jdbc:oracle:thin:@localhost:1521:orcl",
                   "ankit", "Oracle123");
          System.out.println("Connection established successfully!");
         
          callableStmt = con.prepareCall("{ ? = call MYFUNCTION(?)}");
          //Database function will return value as OUT parameter
          callableStmt.registerOutParameter(1, java.sql.Types.NUMERIC);
         
          //IN parameter -
          //set methods are used for setting IN parameter values of Stored procedure
          callableStmt.setInt(2, 11);
         
          //Execute database Function,
          callableStmt.executeUpdate();
          // Then retrieve values returned by method using using get methods.
          System.out.println("salary = " + callableStmt.getInt(1));
         
          System.out.println("Function executed successfully, "
                   + "salary has been fetched from Employee table");
     } catch (ClassNotFoundException e) {
          e.printStackTrace();
     } catch (SQLException e) {
          e.printStackTrace();
     }
     finally{
          try {
              if(callableStmt!=null) callableStmt.close(); //close CallableStatement
              if(con!=null) con.close(); // close connection
          } catch (SQLException e) {
              e.printStackTrace();
          }
     }
}
}
/*OUTPUT
Connection established successfully!
salary = 12000
Function executed successfully, salary has been fetched from Employee table
*/


Additionally, you may use below script for executing above Function as a standalone function from oracle SQL developer.
--call function
DECLARE
     p_salary EMPLOYEE.SALARY%TYPE;
BEGIN
 p_salary  := MYFUNC_EMPLOYEE_FETCH_IN(11);
 DBMS_OUTPUT.PUT_LINE('p_salary = '  || p_salary);
END;


In this tutorial we learned how to call Oracle database FUNCTION and passing parameter - CallableStatement program/example in java JDBC.


RELATED LINKS>

JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


12 Differences between Statement, PreparedStatement and CallableStatement in java


JDBC tutorial- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC tutorial program- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC tutorial program- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


No comments:

Post a Comment