Consultor Eletrônico

Status: Verified

GOAL:

How does the ORACLE DataServer maintain the PROGRESS_RECID column?

GOAL:

How to manually add the PROGRES_ RECID column in an ORACLE table?

FACT(s) (Environment):

Oracle DataServer
OpenEdge DataServer Category: Oracle

FIX:

The PROGRESS_RECID is maintained through ORACLE's sequence generator, i.e., progress_recid = table-name_SEQ.nextval.

By default, sequence is generated and progress_recid column populated when performing a migration through the Progress-to-ORACLE conversion utility.

However, if a migration is not performed, this column and it's corresponding sequence need to be manually added to ORACLE.

The exact steps are as following:

1.Using SQL*Plus, log in as the ORACLE user who owns the table.

2.Create a sequence generator for the table named table-name_SEQ. Start with 1 and increment by 1:

CREATE SEQUENCE table-name_SEQ START WITH 1 INCREMENT BY 1;

3.Add a column to the table named progress_recid. This column holds a number that can be null:

ALTER TABLE table-name ADD (progress_recid number null);

4.Update the table and set the progress_recid using table-name_SEQ.nextval:


UPDATE table-name SET progress_recid = table-name_SEQ.nextval;

5.Create a unique index name, table-name##progress_recid, that consists of just the progress_recid column:

CREATE UNIQUE INDEX table-name##progress_recid ON table-name (progress_recid);

6.Drop every non-unique index from the table and recreate it using the same components. Add progress_recid as the last component:

DROP INDEX table-name##index-name;
CREATE INDEX table-name##index-name ON table-name (column-name, progress_recid);

7.Verify that the sequence was created:

SELECT table-name_SEQ FROM sys.dual;

8. Connect to ORACLE and use the Progress Data Dictionary's ORACLE utilities to update the schema holder.