Execute an Oracle stored procedure from ArcObjects

Summary

Instructions provided describe how to execute an Oracle stored procedure from ArcObjects. It may become necessary to execute an Oracle stored procedure from within ArcGIS. To execute the procedure requires using ArcObjects and the IWorkSpace:ExecuteSQL method.

There is a limitation to using IWorkSpace:ExecuteSQL, it is not intended to be used for retrieving a result set or error message. Both of these limitations are addressed by adding a few additional steps within the stored procedure being executed.

Procedure

1. Add the error checking within the stored procedure and insert the results into a temporary table for validation.
   
   In the stored procedure, add the following lines and exception handle as the last steps the procedure performs before completing.
   
   err_num := SQLCODE;
   err_msg := SUBSTR(SQLERRM, 1, 100);
   INSERT INTO <your_temporary_table> VALUES (err_num, err_msg);
   
   EXCEPTION
   WHEN OTHERS THEN
   err_num := SQLCODE;
   err_msg := SUBSTR(SQLERRM, 1, 100);
   INSERT INTO <your_temporary_table> VALUES (err_num, err_msg);
   
   The temporary table is required to hold the values of the err_num and err_msg. The err_num value is the number of the Oracle error (SQLCODE) and the err_msg is the corresponding error message string.
   
   To retrieve a result set when executing a stored procedure, the procedure will have to write the results to a temporary table as well.
2. ArcObjects is required to check if the stored procedure encountered any errors as a final step. The following ArcObjects example demonstrates the use of a IQueryDef object to check the results:
   
   Dim pFeatureWorkspace As IFeatureWorkspace
   Dim pQueryDef As IQueryDef
   Dim pCursor As ICursor
   Dim pRow As IRow
   
   Set pFeatureWorkspace = m_pWorkspace
   Set pQueryDef = pFeatureWorkspace.CreateQueryDef
   pQueryDef.SubFields = "err_num, err_msg"
   pQueryDef.Tables = "<owner>.<your_temporary_table>"
   
   Set pCursor = pQueryDef.Evaluate
   Set pRow = pCursor.NextRow
   If pRow.Value(0) <> 0 Then
   'Status shows that there was an error, return error.
   MsgBox "Unable to execute stored procedure, " & pRow.Value(1) & ""
   End If
   
   The IQueryDef queries the temporary table and fetches the error number and error message. If the error number value is 0, then the procedure executed successfully, if not, the msgbox reports the error message.