Progress
DataServer
for ORACLE Guide


Using the Manual Upgrade Technique

Follow these steps to modify ORACLE tables manually to take advantage of the FIND PREV/LAST statements, cursor repositioning, and arrays. There are two parts to this process: in part A you make changes to your ORACLE database; in Part B you make changes to the schema holder. Part B is required only if you created your schema holder with a DataServer prior to Release 6.3D.

Part A
  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:
  3. CREATE SEQUENCE table-name_SEQ START WITH 1 INCREMENT BY 1; 
    

  4. Add a column to the table named progress_recid. This column holds a number that can be null:
  5. ALTER TABLE table-name ADD (progress_recid number null); 
    

  6. Update the table and set the progress_recid using table-name_SEQ.nextval:
  7. UPDATE table-name SET progress_recid = table-name_SEQ.nextval; 
    

  8. Create a unique index name, table-name##progress_recid, that consists of just the progress_recid column:
  9. CREATE UNIQUE INDEX table-name##progress_recid ON
    		table-name (progress_recid); 
    

  10. Drop every non-unique index from the table and recreate it using the same components. Add progress_recid as the last component:
  11. DROP INDEX table-name##index-name; 
    

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

  12. Verify that the sequence was created:
  13. SELECT table-name_SEQ FROM sys.dual; 
    

    This statement also verifies that the system table sys.dual exists. Your ORACLE database must have the sys.dual table to support the extended 4GL features. If the sys.dual table does not exist in your ORACLE database, see the “Creating the ORACLE sys.dual Table” section for instructions.

  14. Connect to ORACLE and use the Progress Data Dictionary’s ORACLE utilities to update the schema holder.
  15. NOTE: If you created your schema holder with a DataServer prior to Release 6.3D, follow the steps in Part B before you connect to ORACLE and update the schema holder.

Here’s an example of these SQL*Plus commands for the Customer table in the Progress demo database. The first command creates a sequence for the Customer table. The sequence starts with 1 and increments by 1:

CREATE SEQUENCE customer_SEQ START WITH 1 INCREMENT BY 1; 

This line adds a column to the Customer table. It names the column progress_recid and declares the data type as NUMBER, where the number can be null:

ALTER TABLE customer ADD (progress_recid number null); 

This command updates the Customer table and sets progress_recid using the sequence value:

UPDATE customer 
		SET progress_recid = customer_SEQ.nextval; 

This command creates a unique index for the Customer table that consists of progress_recid:

CREATE UNIQUE INDEX customer##progress_recid ON
		customer (progress_recid); 

This line drops the name index for the Customer table:

DROP INDEX customer##name; 

This command creates a new index for customer that uses the same components as the old index, with the addition of progress_recid:

CREATE INDEX customer##name ON customer (name,
		progress_recid); 

These commands drop the zip index for the Customer table and recreate it with the addition of progress_recid:

DROP INDEX customer##zip; 

CREATE INDEX customer##zip ON customer (zip,
		progress_recid); 

Part B

These steps are necessary only if you created your schema holder with a DataServer prior to Release 6.3D.

  1. Start the Progress Version 6 client and connect to the schema holder only. Do not connect to the ORACLE database.
  2. Access the Data Dictionary and select Modify Schema Modify Existing File.
  3. Type the filename ORACLE6_COLUMNS and press RETURN.
  4. Tab to the Hidden attribute and change it to NO. Press GO.
  5. Choose GoIndex, then add an index to ORACLE6_COLUMNS. Give the index any valid name and select any field. You are creating this index so that you can delete the file ORACLE6_COLUMNS. (The Data Dictionary does not let you delete a file that has no index.)
  6. Delete the table ORACLE6_COLUMNS.
  7. Apply the changes and exit the Data Dictionary.
  8. Enter and run the following procedure:
  9. FIND _Db WHERE _Db-name = "logical database name".
    RUN prodict/ora/ora6_crc.p (RECID (_Db)). 
    

  10. Connect to ORACLE and use the Progress Data Dictionary’s ORACLE utilities to update the schema holder.

Now you can run Progress procedures against the ORACLE database and take advantage of the DataServer’s compatibility features, with the exception of case-insensitive indexed fields.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095