Creating a Method to Use the Stored Procedure

In these steps, you add a method to the DataHandler.java class that can be used as an alternative to the addEmployee method. The new method you add here makes use of the insert_employee stored procedure.

Select the Application tab to display the Application Navigator.
If the DataHandler.java file is not already open in the Java Source Editor, double-click it to open it.
Import the CallableStatement interface as follows:
```
import java.sql.CallableStatement;
```

After the addEmployee method, add the declaration for the addEmployeeSP method.

public String addEmployeeSP(String first_name, String last_name, 
  String email, String phone_number, String job_id,
  int salary) throws SQLException {
}

The method signature is the same as that for addEmployee.

Inside the method, add a try block, and inside that, connect to the database.
```
try {
  getDBConnection(); 
}
```
In addition, inside the try block, create the SQL string:
```
sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
```
The question marks (?) in the statement are bind variables, acting as placeholders for the values of first_name, last_name, and so on expected by the stored procedure.

Create the CallableStatement:

CallableStatement callstmt = conn.prepareCall(sqlString);

Set the IN parameters:

  callstmt.setString(1, first_name);
  callstmt.setString(2, last_name);
  callstmt.setString(3, email);
  callstmt.setString(4, phone_number);
  callstmt.setString(5, job_id);
  callstmt.setInt(6, salary);

Add a trace message, and run the callable statement.

  System.out.println("\nInserting with stored procedure: " + 
                      sqlString);
  callstmt.execute();

Add a return message:
```
  return "success"; 
```

After the try block, add a catch block to trap any errors. The, call the logException created in the ‘Adding a Method to Handle Any SQLException in the Application.

catch ( SQLException ex ) {
  System.out.println("Possible source of error: Make sure you have created the stored procedure"); 
  logException( ex ); 
  return "failure";
}

Save DataHandler.java.

Example 6-7 Using PL/SQL Stored Procedures in Java

public String addEmployeeSP(String first_name, String last_name, 
  String email, String phone_number, String job_id,
  int salary) throws SQLException {
 
  try {
    getDBConnection(); 
    sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
    CallableStatement callstmt = conn.prepareCall(sqlString);
    callstmt.setString(1, first_name);
    callstmt.setString(2, last_name);
    callstmt.setString(3, email);
    callstmt.setString(4, phone_number);
    callstmt.setString(5, job_id);
    callstmt.setInt(6, salary);
    System.out.println("\nInserting with stored procedure: " + 
                       sqlString);
 
    callstmt.execute();
    return "success"; 
  }
  catch ( SQLException ex ) {
    System.out.println("Possible source of error: Make sure you have created the stored procedure"); 
    logException( ex ); 
    return "failure";
  }
}