Kbase 18536: How to Prevent Corruption of PROGRESS_RECID on Oracle
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/15/2008 |
|
Status: Verified
GOAL:
How to prevent progress_recid corruption on ORACLE, when the same table is being accessed from non-Progress utilities or packages?
GOAL:
How to maintain progress_recid column with a trigger?
GOAL:
How to prevent progress_recid column being filled with invalid values?
FACT(s) (Environment):
ORACLE DataServer.
FIX:
The ORACLE DataServer optionally needs a progress_recid column on the ORACLE table when using recid functions. When deleting or creating new records in a table, the DataServer automatically handles the Progress_id field
and fills it with a unique value.
If third-party (non progress dataserver) software is also accessing the same table, then the progress_id may be filled with the "unknown" value. The Progress functions will then behave incorrectly.
Although following configuration is not fully supported, here is a simple workaround to prevent corruption such as above occurring.
Create a trigger in the ORACLE table as follows:
/*** sample of ORACLE trigger */
CREATE TRIGGER INSERT_Nom_Table BEFORE INSERT ON Nom_Table
FOR EACH ROW
DECLARE
VAR_TMP NUMBER(7);
BEGIN
IF :NEW.PROGRESS_RECID IS NULL THEN
SELECT Nom_Table_SEQ.NEXTVAL INTO VAR_TMP FROM DUAL;
:NEW.PROGRESS_RECID := VAR_TMP;
END IF;
END;
/* ********** */