Step By Step Tutorial: Call Oracle API from Java Code

By | January 5, 2016

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:

order table sample data

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;
}

}

 

 

 

Ajmal Abbasi

Ajmal Hussain Abbasi is a TIBCO Consultant By Profession with more than 6 years experience in TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Spotfire, TIBCO BE, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in Telecom sector by using his skills in Tibco Designer, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi is also experienced in developing solutions using Oracle PL/Sql, Linux and Java. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *