Create an oracle function:

Suppose you have created an oracle function by executing the following code in oracle

create or replace FUNCTION "FUN_GET_EMPLOYEE_NAME "( P_EMPLOYEE_ID           VARCHAR2
)
RETURN VARCHAR2
AS PRAGMA AUTONOMOUS_TRANSACTION;
…………………………………………
Write rest of code to return data.
…………………………………………

Function description:

This pl/sql code will simply create a function named “FUN_GET_EMPLOYEE_NAME” which will take a VARCHAR2 input parameter as P_EMPLOYEE_ID and return a VARCHAR2 data i.e. Employee name.

Java program to access this oracle function and get the return data from this oracle function:

Connect to oracle data base by modifying the following code:

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.Connection connection = java.sql.DriverManager DriverManager.getConnection("jdbc:oracle:thin:@"+ "DBServerIP/localhost"+":1521:DBName","DBUser", "DBPassword");
} catch (Exception e) {
e.printStackTrace();
}

Now create a java.sql.CallableStatement by using this java.sql.Connection to call the oracle function as follows:

java.sql.CallableStatement cstmt = connection.prepareCall("{?=call FUN_GET_EMPLOYEE_NAME (?)}");

As our function needs one input parameter to call the oracle function so here I gave (?) to indicate it, if you need to pass more parameters just adjusts it with your parameters number.

Now register the out parameter of the oracle function if any as follows:

cstmt.registerOutParameter(1,  java.sql.Types.VARCHAR);

If your function return more parameter just add them sequentially 2, 3 …

Now add the input parameters of oracle function in your java code as follow:

cstmt.setString(2, “EMPLOYEE_ID”);

Note here we added one parameter because our oracle function take one parameter as input and index is set to 2 as we have already set our oracle function out parameter to index 1 in java code above. You can also set parameters as you needed and with different data type. You can also use parameter name instead of index cstmt.setString(parameterName, x)or cstmt.setInt(parameterName, x) whatever you want, In this case you should set all parameter by parameter name otherwise set all parameter by index.

Now call the execute function of CallableStatement to execute this statement as follows:

cstmt.execute();

You can get return data from your oracle function in java code as follows:

cstmt.getString(1)

Here 1 mean the out parameter index.

About these ads