Create Stored Procedure in Oracle Database using JDBC API

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.

Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

To create a stored procedure in (MySQL) a database using JDBC API you need to −

Register the Driver: Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.

Establish a connection: Connect to the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.

Create Statement: Create a Statement object using the createStatement() method of the Connection interface.

Execute the Query: Execute the query to create stored procedures, using the execute() method of the Statement interface.

Let us create a table with name dispatches in Oracle database using CREATE statement as shown below −

CREATE TABLE Dispatches(
   PRODUCTNAME VARCHAR2(20),
   CUSTOMERNAME VARCHAR2(20),
   DISPATCHDATE DATE,
   DELIVERYTIME TIMESTAMP(6),
   PRICE NUMBER(38),
   LOCATION VARCHAR2(20)
);

Now, we will insert 5 records in dispatches table using INSERT statements −

insert into dispatches values('Key-Board', 'Raja', TO_DATE('2019-09-01', 'yyyy/mm/dd'), TO_DATE('11:00:00', 'hh:mi:ss'), 7000, 'India');
insert into dispatches values('Earphones', 'Roja', TO_DATE('2019-05-01', 'yyyy/mm/dd'), TO_DATE('11:00:00', 'hh:mi:ss'), 2000, 'Vishakhapatnam');
insert into dispatches values('Mouse', 'Puja', TO_DATE('2019-03-01', 'yyyy/mm/dd'), TO_DATE('10:59:59', 'hh:mi:ss'), 3000, 'Vijayawada');
insert into dispatches values('Mobile', 'Vanaja', TO_DATE('2019-03-01', 'yyyy/mm/dd'), TO_DATE('10:10:52', 'hh:mi:ss'), 9000, 'Chennai');
insert into dispatches values('Headset', 'Jalaja', TO_DATE('2019-04-06', 'yyyy/mm/dd'), TO_DATE('11:08:59', 'hh:mi:ss' ), 6000, 'Goa');

Following JDBC program establishes connection with the Oracle database and creates a procedure named retrieveData which retrives the contents of the above created table.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreatingStoredProcedures_Oracle {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      //Getting the connection
      String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe";
      Connection con = DriverManager.getConnection(oracleUrl, "system", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Query to create stored procedures
      String query = "CREATE PROCEDURE retrieveData() " +
      "BEGIN "+
         " SELECT * FROM Customers; "+
      "END";
      //Executing the query
      stmt.execute(query);
      System.out.println("Procedure Created......");
   }
}

Output

Connection established......
Procedure Created......