Extracting records from Oracle based on a created time of a Unix file

15 pts.
Tags:
SQL
I wish to extract updated records from an Oracle database using a scheduled PL/SQL based procedure run every 15 minutes. The last updated date/time is stored in a column, I am currently comparing using last_upd > (SYSDATE - 15/1440). However this meand that is the job fails and is re-run, some updated records will be lost. I had the idea of creating an empty file each time the extract was run and somehow using the created time of the file to compare against. Is this possible in PL/SQL ? Otherwise can I query a file directly if the contents is the last extracted time

Answer Wiki

Thanks. We'll let you know when a new response is added.

Why do you want to create an empty file ? Why not saving the timestamp after a successful run of your procedure in a new created working table. Your procedure is then able to check the update field against the last saved timestamp in this table. If your procedure run into exception, don’t reset the timestamp of the working table. So you do not loose updated records after a re-run of your procedure. If the procedure is always called within the same database session, you can also use a package variable ( declared in the package specification ) to keep the last timestamp.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following