How To: Execute an Oracle stored procedure from ArcObjects
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.
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.
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.