There are some occasions in which OpenEdge creates dynamic temp-tables automatically, that is, without resorting to an explicit CREATE TEMP-TABLE statement. Be aware that, even if created implicitly by OpenEdge, these dynamic temp-tables are *
not* deleted automatically by OpenEdge; they must be deleted explicitly by the 4GL program via a DELETE OBJECT statement instead, otherwise problems can occur such as memory leaks, abnormal growth of a client’s DBI file, or error 40 ("SYSTEM ERROR: Attempt to define too many indexes.").
This is the list of known conditions:
a) When running a procedure with an INPUT-OUTPUT or OUTPUT TABLE-HANDLE parameter, the called procedure has a static TABLE parameter and the handle variable used to catch the result does not point to a valid existing temp-table, as in the following example:
DEFINE VARIABLE hTable AS HANDLE NO-UNDO.
RUN myProcedure (OUTPUT TABLE-HANDLE hTable).
PROCEDURE myProcedure :
DEFINE OUTPUT PARAMETER TABLE FOR tt.
...
END.
In this case, OpenEdge implicitly creates a dynamic temp-table with a schema identical to temp-table tt and assigns its handle to variable hTable. This temp-table must be deleted explicitly when it’s no longer needed:
RUN myProcedure (OUTPUT TABLE-HANDLE hTable).
...
Use hTable
...
DELETE OBJECT hTable.
Executing a RUN with INPUT or INPUT-OUTPUT TABLE-HANDLE parameter does not exhibit this behavior, because hTable must point to a pre-existing temp-table, OpenEdge will use that temp-table instead of creating an implicit dynamic one. Similarly, if the receiving variable points to an existing valid temp-table, that temp-table will be used to catch the returned data.
b) Whenever the called procedure has a dynamic INPUT or INPUT-OUTPUT TABLE-HANDLE parameter, as in the following example:
RUN myProcedure (INPUT TABLE tt).
PROCEDURE myProcedure :
DEFINE INPUT PARAMETER TABLE-HANDLE phTable.
...
END.
OpenEdge implicitly creates a dynamic temp-table with a schema identical to the temp-table passed to the procedure, but does not delete it at the end of the procedure; the dynamic temp-table must be deleted explicitly instead as in the following:
PROCEDURE myProcedure :
DEFINE INPUT PARAMETER TABLE-HANDLE phTable.
...
DELETE OBJECT phTable.
END.
When an OUTPUT TABLE-HANDLE parameter is used, it will remain uninitialized until it is used to explicitly create a new dynamic temp-table or until it is assigned the value of the handle for an existing temp-table.
Be aware that under all circumstances, the DELETE OBJECT statement is not executed immediately when executed on a dynamic TABLE-HANDLE parameter. The delete is actually delayed until after the temp-table has been copied back to the caller.If the procedure contains multiple RETURN statements, ensure that each point where the procedure returns has the appropriate DELETE OBJECT statement(s). For example:
PROCEDURE myProcedure :
DEFINE INPUT PARAMETER TABLE-HANDLE phTable.
...
IF <some condition> THEN DO:
DELETE OBJECT phTable.
RETURN.
.END.
...
DELETE OBJECT phTable.
END.
c) When passing ProDataSets across procedures, dynamic temp-tables are implicitly created in exactly the same cases as when passing temp-tables (points a and b above), that is:
- When running a procedure with an INPUT-OUTPUT or OUTPUT DATASET-HANDLE parameter, the called procedure has a static DATASET parameter and the handle variable used is invalid,
- Whenever the called procedure has a dynamic INPUT or INPUT-OUTPUT DATASET-HANDLE parameter.
These need to be addressed by deleting the dynamic ProDataSet implicitly created, by means of a DELETE OBJECT statement in the appropriate place. Deleting the dynamic ProDataSet will delete the dynamic TEMP-TABLEs created with it as well.In order to check whether there are dynamic temp-tables in the client's memory which have not been properly deleted, refer to Article:
