import java.sql.*;
public class Proc
{
public static void main(String[] args)
{
Connection con = null;
CallableStatement stmt = null;
try
{
// Load JDBC driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// Establish the connection
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");
// Disable auto-commit for transaction management
con.setAutoCommit(false);
// Prepare the CallableStatement
stmt = con.prepareCall("{call insertR(?,?)}");
stmt.setInt(1, 1011);
stmt.setString(2, "Amit");
// Execute the stored procedure
stmt.execute();
// Commit the transaction if successful
con.commit();
System.out.println("Success");
}
catch (SQLException e)
{
// Handle SQL exceptions
System.out.println("SQL Error: " + e.getMessage());
if (con != null)
{
try
{
// Rollback the transaction in case of failure
con.rollback();
System.out.println("Transaction rolled back.");
}
catch (SQLException ex)
{
System.out.println("Error during rollback: " + ex.getMessage());
}
}
}
catch (Exception e)
{
// Handle other exceptions
System.out.println("Error: " + e.getMessage());
}
finally
{
// Ensure resources are closed
try
{
if (stmt != null)
{
stmt.close();
}
if (con != null)
{
con.close();
}
}
catch (SQLException e)
{
System.out.println("Error closing resources: " + e.getMessage());
}
}
}
}
Breakdown:
1. Imports Required Classes
import java.sql.*;
The program declares necessary classes of the java.sql package. This package contains classes which are used in Java to deal with databases.
2. Class Declaration:
public class Proc {
}
The class Proc is declared where the main method of the program is defined and runs the application.
3. Main Method:
public static void main(String[] args) throws Exception {
The main method is the entry point of the program. It is marked with throws Exception to propagate any exceptions that may occur during execution, such as database connection issues or SQL errors.
4. Loading the Oracle JDBC Driver:
Class.forName(“oracle.jdbc.driver.OracleDriver”);
It loads the Oracle JDBC driver: oracle.jdbc.driver.OracleDriver. It is required for the program to connect to an Oracle database in order to open a connection to the database.
5. Connecting to the Database:
Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”, “system”, “oracle”);
- DriverManager.getConnection() connects to the Oracle database based on the given JDBC URL, jdbc:oracle:thin:@localhost:1521:xe, with the username being system and the password being oracle.
- The connection string indicates that Oracle is running on the local host (localhost), at port 1521, and accessing the XE (Express Edition) database.
6. Preparing the CallableStatement:
CallableStatement stmt = con.prepareCall(“{call insertR(?,?)}”);
- A CallableStatement object is obtained by calling the prepareCall() method, which calls the stored procedure insertR from the database.
- The stored procedure is expected to take two parameters (?). The question marks (?) are placeholders for the actual values that will be passed to the stored procedure.
7. Setting the Parameters for the Stored Procedure:
stmt.setInt(1, 1011);
stmt.setString(2, “Amit”);
- stmt.setInt(1, 1011) sets the first parameter of the stored procedure to the integer value 1011 likely representing an ID.
- stmt.setString(2, “Amit”) sets the second parameter of the stored procedure to the string value “Amit” likely representing a name.
8. Executing the CallableStatement:
stmt.execute();
stmt.execute() calls the stored procedure. Since the stored procedure insertR is probably doing an INSERT in the database, this call will insert the values (1011, \”Amit\”) into the corresponding table in the database.
9. Printing Success Message:
System.out.println(“success”);
If the stored procedure runs successfully, then this message will be printed on the console showing that the operation is successful.
10. Closing the Connection:
For practical use cases, the database connection should be closed to free up resources. However, this is not the case in the example above; the connection will not be explicitly closed. It should be added in a finally block, as illustrated by the statement con.close().
Database Setup:
For this program to run, the following should exist in the Oracle database:
1. Stored Procedure insertR:
The stored procedure insertR should exist in the Oracle database and should take two parameters: an integer and a string, such as the following example:
sql
CREATE OR REPLACE PROCEDURE insertR (
p_id IN NUMBER,
p_name IN VARCHAR2
) AS
BEGIN
INSERT INTO employees (id, name) VALUES (p_id, p_name);
END insertR;
This example assumes the existence of a table employees with columns id (NUMBER) and name (VARCHAR2).
2. Table employees:
The employees table (or similar) should already be created in the database. It should have columns that can accommodate the parameters passed to the stored procedure (id and name).
Expected Output:
Assuming the stored procedure insertR successfully inserts the values 1011 and “Amit” into the database, the output of the program will be:
success
This message indicates that the data insertion was successful.
Possible Errors/Issues:
1. Database Connection Failure:
If the Oracle database is not running or if the connection details (localhost, 1521, system, oracle) are wrong, a SQLException will be thrown and the program will not connect to the database.
2. Procedure Not Defined:
If the insertR procedure is not defined in the database, then the program will throw a SQLException with a message stating that the procedure is not defined.
3. Incorrect Input Parameters:
When the stored procedure is attempting to accept a variety of data types or when the parameters on insertR are different, the program will raise SQLException because of mismatching types of parameters.
4. Database Constraints:
If there exist constraints within the database, such as uniqueness or relationships between two tables with foreign key constraints, and the input data violates the constraints (like duplicate IDs in the inserted values), then the program throws SQLException.