English

How To: 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

To execute an Oracle stored procedure:

IWorkSpace:ExecuteSQL "BEGIN <owner>.<stored_procedure_name>("<optional_arguments>"); END;"

The optional arguments are the stored procedures required input values.

  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.