In this tutorial we will learn how to call Oracle database FUNCTION and passing parameter - CallableStatement program/example in java JDBC.
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;
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>