Calling Stored Procedures

You can override the doDML() method of any entity object's EntityImpl class to invoke a PL/SQL Stored Procedure, instead of using the default DML. PL/SQL stored procedures are useful when your updates are complex and involve multiple tables instead of a simple update to a single table.

Your stored procedure can perform an update, insert, or delete instead of the default framework behavior of applying the appropriate UPDATE, INSERT, or DELETE statement on your entity object's behalf.

Here is sample code for overriding the doDML() method of any entity object's EntityImpl class to invoke a stored procedure, and pass arguments. For example, given a PL/SQL stored procedure like:

PROCEDURE updateDepartment( p_Deptno NUMBER,
                            p_Dname  VARCHAR2,
                            p_Loc    VARCHAR2 )

The code to call it when an entity is updated would look like:

Note: The following code assumes only updates are possible, disallowing inserts and deletes by ignoring them in the doDML() method.

   public void doDML(int operation, TransactionEvent e) { 
      // Don't call the superclass. This procedure handles only updates 
      //super.doDML(operation, e); 
      CallableStatement stmt = null; 

      // ONLY Perform updates, this entity won't allow inserts/deletes. 
      if (operation == DML_UPDATE) 
      { 
         // Prepare JDBC CallableStatement with the Stored Procedure Call 
         String updateStr = "{call updateDepartment(?,?,?)}"; 
         stmt = getDBTransaction().createCallableStatement(updateStr, 1); 
         try 
         { 
            // Bind the Statement Parameters and Execute this Statement 
            stmt.setString(1, getDeptno().toString()); 
            stmt.setString(2, getDname().toString()); 
            stmt.setString(3, getLoc().toString()); 
            stmt.execute(); 
         } 
         catch (Exception ex) 
         { 
            throw new oracle.jbo.JboException(ex); 
         } 
         finally 
         { 
            try 
            { 
               stmt.close(); 
            } 
            catch (Exception nex) 
            { 
            } 
         } 
      } 
   }