We all do work tables during our architectural design and development. To Clear the work table, we then rely on some bogus field, or say something like PID, to delete off the records.
Such deletion of records takes up space on the Database side, as it just deletes the data and does not reclaim the space from the table space, even when there’s no records. Delete Records statement, uses more transaction logs, . TRUNCATE Table is the SQL that has to be used in these scenarios.
BSFN – B8000002 does something similar to Truncate table, though the SQL generated is "Delete From TableName".
Note: Sometimes this BSFN does not fire Delete Table triggers. I dunno in what cases it does.
The B8000007 and the B8000002 business functions must be used together to delete all the rows in a table. They will not delete the table specifications or the physical table in the database. B8000007 must be called first to get the environment handle, with the handle only then B8000002 is able to delete all records from the specified table. B8000007 must be called again to free the handle.
The B8000007 Get Input Output Env Handles is used to get the environment handle where the table can be found.
The B8000002 Delete All Rows From Table is used to delete all the rows in a selected table.
The B8000007 Free Input Output Env Handles is used to free the environment handles.
B8000007 – Get Input Output Env Handles
To use the B8000007, you will first need to create event rule variables, one for the Input Handle and one for the Output Handle. These variables need to be an ID type. It would be best to use the Data Dictionary alias HANDLE.
Unless the B8000007 is being used in a table conversion there are no input and output environments. In the example below it is assumed that B8000007 is not being used in a table conversion, so all environments are filled with the system variable SL LoginEnvironment. In the example below Input_HANDLE and the Output_HANDLE are event rule variables created by using the Data Dictionary alias HANDLE.
SL LoginEnvironment -> szInputEnvironment
Input_HANDLE <- idInputEnvHandle
SL LoginEnvironment -> szOutputEnvironment
Output_HANDLE <- idOutputEnvHandle
SL LoginEnvionment -> szLoginEnvrionment
B8000002 – Delete All Rows From Table
The business function B8000002 will delete all the records in a table. It does not delete the table specifications or the physical table in the database.
The business function has two arguments. The first argument is of type string. This argument must pass in the Object Name of the table. Example: F550101. The second argument is of type ID and should be the environment handle value returned by B8000007. The value from the B8000007 to be used is idOutputEnvHandle. In the example above the handle was returned to the event rule variable Output_HANDLE.
B8000007 (Free Input Output Env Handles)
After the B8000002 business function has been used to delete the records, the Input and Output Environment handles need to be freed or removed from memory. The business function B8000007 Free Input Output Env Handles is used for this purpose. The inputs and outputs for this business function will be the same except that the Input and Output Handles should be bi-directional. See the example below.
SL LoginEnvironment -> szInputEnvironment
Input_HANDLE <-> idInputEnvHandle
SL LoginEnvironment -> szOutputEnvironment
Output_HANDLE <-> idOutputEnvHandle
SL LoginEnvionment -> szLoginEnvrionment
Source: Oracle/Metalink3
Many thanks for discussing such an topical article with all of us. I’ve bookmarked your blog will come back for a re-read again. Keep up the very good work.