Oracle Inserts

[dodda]

Hello

I have a requirement where i will get the flat file (app 2-3 million records) and i need to insert the data to oracle Database. I am Seq File stage, transformer and Oracle Enterprise stage for this. which is the best way to load the data to Oracle database. should i use load as write method or Upsert as write method. If i use upsert will it be both inserts and updates? Also i need to check for each record if it already exists in the database based on some key and if the record exists then i need to reject it to a file and if not i need to insert that record in the database. This check has to be done for every record and i need to commit all the records ewhich are inserted at the end of the job.

Thanks for your help

[betterthanever]

if the record already exists on a key, do you want to update the record (in the table) with the new one or you want to reject that record???

[dodda]

hello

Thanks for the response. I want to reject that record if it already exists.

Thanks

[betterthanever]

then why do you wanna have upsert???

[dodda]

Hi

I could not see any option called INSERT that is why i am thinking of using UPSERT mode.


Thanks

[betterthanever]

you can use WRITE METHOD TO "load" if you just want to insert.
but if you want to reject the records those exists, you can't have reject records tapping option in the ORACLE stage( in LOAD mode).

you may have to do it in your job seperately and reject the existing records before you load to the database.

[chulett]

It other words, do a lookup against the target table and only load those where the lookup fails.

[betterthanever]

[quote="chulett"]It other words, do a lookup against the target table and only load those where the lookup fails. ...[/quote]

EXACTLY

[dodda]

Thanks chulett

I want to commit the inserted records after the job has been finished successfully if the job aborts in the middle i will have to rollback. Is there a way thatcan be done with Load option.

Thanks

[betterthanever]

then write to a dataset load the DB eventually.

[chulett]

Well, that "load" option invokes sqlldr, so unless you can force it to do a conventional (DIRECT=FALSE) load you won't have any control over commits. If you've screened out any non-inserts then UPSERT may be an option as there are environment variables to control commits there.

[dodda]

Hello chulett,

As you said, after lookup against the target table i can send the failed records ( which are only inserts) to a database where i can use UPSERT method to insert records into database. I know there two environmental variables that control the commits. But wondering what are the values that i need to give if i want to commit after successful insertion and rollback if the job fails.

[chulett]

Zero.

[dodda]

Hello Chulett,

You mean to say Zero for both APT_ORAUPSERT_COMMIT_ROW_INTERVAL and APT_ORAUPSERT_COMMIT_TIME_INTERVAL env variables. So if the job fails all the records will be rolled back?

Thanks

[chulett]

I'm not sure you need to set both as I believe one overrides the other but yes - zero should mean only on successful completion. I'm unsure what the defaults are when those are not set, however... perhaps leaving them unset means the same thing?