>>Then you have zeroed out all your options. I suggest adding another column to the Oracle table[s] and use integers as your surrogate PK. Otherwise you have to change to data type of the key field to CHARacter. PK data types of varchar2 are generally a bad idea.
>
>I agree 100% that varchar as a PK is a bad idea. Currently I am working on a support application to another app that controls the Oracle database. I am going to try to add my own key fields and see if it has any effects on the original app. Is there a way to use a sequence as a default field value in Oracle? If not, then I'm not sure how I can insert values into the new surrogate key fields since I'm not in control of inserting the records. Thanks for the help..
>
>-David-

David,

Not sure about using a sequence as a default value but I am currently using sequences to generate primary keys as follows:

PL/SQL:
/*Get the next company unique id*/
CREATE OR REPLACE FUNCTION CompanyFNEXT RETURN NUMBER
IS
COUNT1 NUMBER;
BEGIN
SELECT COMPANY_S.nextval INTO COUNT1
FROM dual;
RETURN(COUNT1);
END;
/

VFP:

nRet=SQLExec(oData.dbHandle,"Select CompanyFNext from Dual",'temprow')

This mechanism came right out of the knowledge base.

You could also populate the column using a 'Before Insert' trigger in Oracle:

CREATE OR REPLACE TRIGGER SOMETABLE_INSERT
BEFORE INSERT ON SOMETABLE FOR EACH ROW

DECLARE

v_Id Number;


BEGIN

If :new.id_column Is Null Then

Select CompanyFNext Into v_Id from dual;
:new.id_column := v_Id;
End If;
End;

To use the trigger mechanism you will have to allow your id column to be nullable so that Oracle wont complain about not having a value when you do the insert.

What you may find is that by altering the underlying data structure the other application that interacts with the database may be 'broken'.

Mark