While working on software development projects; there can be scenarios where you need to call Oracle API’s from Java code. In this step by step tutorial; I will explain how we can call Oracle Stored Procedure from Java Code.
Step 1: Write Example Oracle Procedure to be invoked
In order to prepare end to end tutorial; I am writing a simple procedure in Oracle which takes an integer Order ID as an input and returns Order status as the output parameter.
The stored procedure has a simple logic to query the order status from a table TBL_ORDER
Some sample data is populated in the table as shown below:
Oracle Procedure creation SQL Script is given below. It needs to be compiled successfully before we can proceed with subsequent steps of calling it from Java Code.
CREATE OR REPLACE PROCEDURE PRC_GET_ORDER_STATUS
(
PI_ORDERID IN NUMBER
, PO_ORDERSTATUS OUT VARCHAR2
) AS
v_status varchar2(30);
BEGIN
SELECT ORDER_STATUS INTO PO_ORDERSTATUS from TBL_ORDER
WHERE
ORDER_ID=PI_ORDERID;
EXCEPTION
WHEN NO_DATA_FOUND
then
PO_ORDERSTATUS:=’ORDER NOT FOUND ‘;
WHEN OTHERS
then
PO_ORDERSTATUS:=SQLERRM;
END PRC_GET_ORDER_STATUS;
Step 2: Write Java Code to Call Oracle API
Java.sql.DriverManager class should be imported in order to use getConnection method of this class. getConnection method when called b providing database URL, username and password as input parameters; returns Connection object for success scenario:
Connection con = DriverManager.getConnection(DBURL, User, Password);
After getting connection object created successfully; we need to create CallableStatement class object. For this java.sql.CallableStatement needs to be imported.
CallableStatement object is created by calling prepareCall method of connection class with call statement string as input as shown below:
String command = “{call SYSTEM.PRC_GET_ORDER_STATUS(?,?)}”;
CallableStatement cstmt = con.prepareCall(command);
In the above statement; we are creating the command to call storeed procedure SYSTEM.PRC_GET_ORDER_STATUS
Two question marks in the command indicate that this procedure has two parameters (IN, OUT or IN/OUT).
Now our next step is to specify input values for the IN parameter(s) and the type for the OUT parameter(s).
In our example stored procedure; first parameter is IN while second is OUT so we set them as below:
cstmt.setInt(1, orderId); //orderId integer value to be set as input parameter
cstmt.registerOutParameter(2, Types.VARCHAR); //output parameter specified as VARCHAR type
Now we have the statement prepared with all required parametric details. We can call the execute method now to get the Oracle API called:
cstmt.execute();
now we can get the OUT parameter value by calling getString method and specifying the index of the OUT parameter as shown below:
String message=cstmt.getString(2);
After this; we can close the statement using close method:
cstmt.close();
This completes all the steps for Java Oracle API invoking tutorial.
The complete code for this java class is shown below:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;public class OrderInformation
{public static void main(String args[]) throws Exception
{//call API for ORDER_ID 101
System.out.println(getOrderStatus(“jdbc:oracle:thin:@localhost:1521:XE”,”USERNAME”,”PASSWORD”,101));}
public static String getOrderStatus(String DBURL, String User, String Password,int orderId) throws Exception
{
String message;
try {
Connection con = DriverManager.getConnection(DBURL, User, Password);
System.out.println(“Connected to database”);String command = “{call SYSTEM.PRC_GET_ORDER_STATUS(?,?)}”;
CallableStatement cstmt = con.prepareCall(command);
cstmt.setInt(1, orderId);
cstmt.registerOutParameter(2, Types.VARCHAR);cstmt.execute();
message=cstmt.getString(2);
cstmt.close();}
catch(Exception ex) {
message= ex.getMessage();}
return message;
}}
