Explain Callable statement object with an example program

10.B] Explain Callable statement object with an example program.

Answer:

The CallableStatement interface is used to call stored procedures and functions in a database. Here’s a detailed explanation with the provided snippet and an example.

Explanation

• Purpose:
• Call stored procedures or functions from Java.
• Set input parameters and retrieve output parameters.

Code Snippet Explanation

1. Prepare the CallableStatement:

   String SQL = "{call getEmpName (?, ?)}"; // Stored procedure call
   cs = conn.prepareCall(SQL);

• "{call getEmpName (?, ?)}" specifies the stored procedure getEmpName with two parameters.

1. Set Input Parameters:

   cs.setInt(1, 100);

• cs.setInt(1, 100); sets the first parameter (employee ID) to 100.

1. Register Output Parameters:

   cs.registerOutParameter(2, Types.VARCHAR);

• cs.registerOutParameter(2, Types.VARCHAR); registers the second parameter as an output parameter of type VARCHAR.

1. Execute the CallableStatement:

   cs.execute();

• Executes the stored procedure.

1. Retrieve Output Parameter:

   String Name = cs.getString(2);

• Retrieves the output parameter value, which is the employee’s first name.

1. Close the CallableStatement:

   cs.close();

• Closes the CallableStatement to release resources.

Example Stored Procedure in Oracle

Here’s an example of an Oracle stored procedure that retrieves an employee’s name based on their ID:

CREATE OR REPLACE PROCEDURE getEmpName (
    EMP_ID IN NUMBER, 
    EMP_FIRST OUT VARCHAR
) AS
BEGIN
    SELECT first INTO EMP_FIRST
    FROM Employees 
    WHERE ID = EMP_ID;
END;

Full Example Code

Below is a complete Java code example demonstrating how to use CallableStatement with the given Oracle stored procedure.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;

public class CallableStatementExample {
    public static void main(String[] args) {
        // Database connection details
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "username";
        String password = "password";

        // JDBC objects
        Connection conn = null;
        CallableStatement cs = null;

        try {
            // Load JDBC driver
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // Establish the connection
            conn = DriverManager.getConnection(url, user, password);

            // Prepare CallableStatement
            String sql = "{call getEmpName (?, ?)}";
            cs = conn.prepareCall(sql);

            // Set input parameter
            cs.setInt(1, 100);

            // Register output parameter
            cs.registerOutParameter(2, Types.VARCHAR);

            // Execute the stored procedure
            cs.execute();

            // Retrieve and display output parameter
            String name = cs.getString(2);
            System.out.println("Employee Name: " + name);

        } catch (ClassNotFoundException e) {
            System.out.println("JDBC Driver not found.");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("SQL Exception occurred.");
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (cs != null) cs.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Key Points:

• Prepare Call: Use conn.prepareCall("{call procedure_name (?, ?)}") to call the stored procedure.
• Set Parameters: Use setXXX() methods for input parameters.
• Register Output Parameters: Use registerOutParameter(index, sqlType) for output parameters.
• Execute Procedure: Use execute() to run the stored procedure.
• Retrieve Results: Use getXXX() methods to fetch output parameters.

Replace the database connection details with your actual database credentials.