Select/Fetch must be used with Open/Close in Event Rule?

Hi all,

If I use "Select/Fetch" to fetch records form a table, is it a must to use
"Open" before "Select/Fetch" and "Close" after records operation?

Is there any negative impact on the system if fetch data using
"Select/Fetch" without "Open/Close"?

Thanks&Regards,
David Huang

No. It is not necessary to open and close a table when selecting & fetching records from it. And the opening and closing of tables does not affect system performance.

Opening and closing of tables primarily are done for two reasons (that I can think of). Firstly to avoid system overheads IF you are using many tables in an application/UBE. Opening and closing table handles may be a good thing to do then. However even massive multi-table UBEs such as the Invoice R42565 performs well without opening and closing of tables.

Secondly it may be done for keeping ER code clean. A glance at code wherein tables are opened and closed gives the developer an idea of the processing begin and end for the given table (something akin to transaction processing begin and end)

use table open/close if you need to use transaction processing and insert/update/delete records. if you want to fetch record only, you don't have to use table open/close.

Hi Richard,

Could you please explanin why should to use Open/Close with Delete/Update/Insert. If don't use, what's the negative impact?

Thanks,
David Huang

David,
I had some problems in Xe when using Select/Fetch in UBE's.
In the cases that you do not loop till the end of last records of the select. For example:

Event Do Section
-------------------------------

Select F...
Fetch Next F...
If SV_FILE_IO_STATUS = ER_SUCCESS
End If

If the select retrieves more than one record, and do not use more the fetch next to go to the last record... and is a large number of interactions into a do section ER, the UBE goes to error.
In the logs we see that we exceded the max number of cursors in oracle. (Configured in JDE.INI). So in this cases we required to use the Close. This comand releases the cursor of the select and let the UBE run with no errors.
I'm not sure if this is the behavior in other versions of EO, but at least in Xe and Oracle it worked like this for us.


Hope this helps.

Hi Carlos,

Thanks for your input, it's a useful information.

Regards,
David Huang