Define Another Cursor When Mixing ESQL and SP Calls

A customer reported that  finderr failed to detect end of table and break out of a repeat loop. This problem occurred because both ESQL and SP calls were used and only one cursor was defined. When mixing ESQL and SP calls, you need to create a new cursor and close it after each SP call.

Two properly working code examples are shown below. The first example uses the old method (CALL_ORACLE_STORED_PROECDURE) to call an Oracle SP. The second example uses the new command (SQL_SET_PROCEDURE_NAME)  to call an Oracle SP.

Example 1

Procedure OldCallProcedure
 Integer iRet iCredit
 Integer iOrderNumber iCustNumber iVendorNumber
 Integer iCursor1 iCursor2
 sql_open_cursor_stmt to iCursor1
 sql_set_stmt to "select Order_Number,Cust_Number,Vendor_Number from ordsys"
 sql_prepare_stmt
 sql_execute_stmt
 Repeat
 sql_set_current_cursor_stmt to iCursor1
     sql_fetch_next_row into iOrderNumber iCustNumber iVendorNumber
 If (Found) Begin
 Move 500 to iCredit
 Move 999 to iRet
 sql_open_cursor_stmt to iCursor2
 CALL_ORACLE_STORED_PROCEDURE "CUSTOMER_PKG.SET_CUST_CREDLIM" passing iCredit iRet
 sql_close_cursor_stmt to iCursor2
         showln 'COUNT: ' iCount ' - Order: ' iOrderNumber ' - Customer: ' iCustNumber ' - Vendor: ' iVendorNumber ' - SP Returned: ' iRet
 Indicate Found True
 End
 Until (Not(Found))
 End_Procedure

Example 2 

Procedure NewCallProcedure
 Integer iRet iCredit
 Integer iOrderNumber iCustNumber iVendorNumber
 Integer iCursor1 iCursor2
 sql_open_cursor_stmt to iCursor1
 sql_set_stmt to "select Order_Number,Cust_Number,Vendor_Number from ordsys"
 sql_prepare_stmt
 sql_execute_stmt
 Repeat
 sql_set_current_cursor_stmt to iCursor1
     sql_fetch_next_row into iOrderNumber iCustNumber iVendorNumber
 If (Found) Begin
 Move 500 to iCredit
 Move 999 to iRet
 sql_open_cursor_stmt to iCursor2
         SQL_SET_PROCEDURE_NAME "CUSTOMER_PKG.SET_CUST_CREDLIM" NUMPAR 2
 SQL_SET_PROCEDURE_PARAMETER 1 to iCredit IN
 SQL_SET_PROCEDURE_PARAMETER 2 to iRet OUT
 SQL_PROCEDURE_EXECUTE
 SQL_GET_PROCEDURE_PARAMETER 2 to iRet
 sql_close_cursor_stmt to iCursor2
         showln 'COUNT: ' iCount ' - Order: ' iOrderNumber ' - Customer: ' iCustNumber ' - Vendor: ' iVendorNumber ' - SP Returned: ' iRet
 Indicate Found True
 End
 Until (Not(Found))
 End_Procedure