27 replies to this topic

[Phil]

Hi all,

I am having problems whilst migrating data from Pro-isam to Oracle.

The error is very intermittant and very irritating.

I have created 11 Oracle tables from 11 .pro files and then created 11 functions to delete from the .pro files and add to the Oracle tables.

The functions are designed to run on to the next one, and this is where the problem is. If I run them singlely they are fine and the data migrates successfully, but if I tag them all together the 2nd function will always fall over, with the most common Oracle error message ; unique constraint.

I have inserted WAITS in between the functions but this doesn't resolve the issue either.

Any Ideas ?

Phil

[Neil Hunter]

Have you tried a commit at LS or function exit ?

Are the files that you are converting dependant on each other ?

[Phil]

Neil,

I tried an embedded SQL COMMIT in Function Exit Logic, which had no bearing on the problem.

And, no the files are not dependant on each other.

Phil

[Neil Hunter]

Very odd,

Stupid question, if you truncate the tables before hand, does this have any bearing ?

[Phil]

Nope.

[Neil Hunter]

Hmmm, most common error is unique constraint violated right ?

Thats very strange

And have you tried a different second function ?

[Neil Hunter]

Do the indexes on the tables match the key structure of the pro isam file you converting ?

[Phil]

Yes, I have tried different functions, and it'll always fall over on the 2nd one.

Yes, the indexes on the Oracle tables match the key structure of the pro isam files.

All data that was written with CO/DIV = 'Y' has now been converted to have @COMP as the first key.

[Neil Hunter]

If you change it from @COMP to something like XX_COMP ?

And setup XX_COMP = @COMP

Edited by Neil Hunter, 10 March 2005 - 11:45 AM.

[Phil]

Still the same.

[Phil]

Neil, you have helped me to identify the area of the problem.

I have just chosen 4 files that did not have CO/DIV 'Y' and therefore didn't need @COMP inserting. All 4 files migrated successfully.

On we go.................

[Neil Hunter]

Cool

So you fully up and running then ?

[Phil]

No

[Neil Hunter]

You still getting the same errors ?

[TalentedFool]

Personally I would have a look at how the primary keys are setup within the oracle table and how the unique constraint index is set up.

This is prob a dumb statement but If you're only keying the file on the value of @COMP then the unique constraint against the table is going to fall over every time. Like I said - prob dumb question.

Can you post a DB schema for the table it falls over on?