Oracle Populate backup table from primary table

The program that I am currently assigned to has a requirement that I copy the contents of a table to a backup table, prior to the real processing.

During code review, a coworker pointed out that

INSERT INTO BACKUP_TABLE
SELECT *
FROM PRIMARY_TABLE

is unduly risky, as it is possible for the tables to have different columns, and different column orders.

I am also under the constraint to not create/delete/rename tables. ~Sigh~

The columns in the table are expected to change, so simply hard-coding the column names is not really the solution I am looking for.

I am looking for ideas on a reasonable non-risky way to get this job done.

Thanks.

Evil.

Answers


Does the backup table stay around? Does it keep the data permanently, or is it just a copy of the current values?

Too bad about not being able to create/delete/rename/copy. Otherwise, if it's short term, just used in case something goes wrong, then you could drop it at the start of processing and do something like

create table backup_table as select * from primary_table;

Your best option may be to make the select explicit, as

insert into backup_table (<list of columns>) select <list of columns> from primary_table;

You could generate that by building a SQL string from the data dictionary, then doing execute immediate. But you'll still be at risk if the backup_table doesn't contain all the important columns from the primary_table.

Might just want to make it explicit, and raise a major error if backup_table doesn't exist, or any of the columns in primary_table aren't in backup_table.


Need Your Help

Clearing cookies for each test not working. Using Karma + Qunit (using Pavlov) + Ember. Running in PhantomJS/Chrome

javascript cookies ember.js qunit karma-runner

I need to clear a login cookie before each test as previous tests might have stored the cookie. This is what I have right now, but it does not seem to get rid of cookies/credentials as requests for

iOS8 check if device has Touch ID

iphone ipad ios8 touch-id

LAContext has method to check if device can evaluate touch ID and gives error message.