SQL & PL/SQL

DB version:11g


I am not sure if i have created an unnecessarily large post to explain a simple issue. Anway, here it is.

I have been asked to code a package for Archiving .

We'll have two schemas;The original schema and an Archive schema (connected via a DB Link)

ORIGINAL Schema -------------------------> ARCHIVE Schema
		     via DB Link

When records of certain tables in the ORIGINAL schema meet the archiving criteria (based on Number of Days Old, Status Code etc), it will be moved ('archived') to the ARCHIVE schema using the INSERT syntax

insert into arch_original@dblink
(
col1,
col2,
col3,
.
.
.
.
)
select col1,
col2,
col3,
.
.
.
.
from original_table

The original table and its archive table has the same structure, except that the Archive table has an additional column called archived_date which just records when a record got archived.

create table original
(
col1 varchar2(33),
col2 varchar2(35),
empid number
);


create table arch_original
(
col1 varchar2(33),
col2 varchar2(35),
empid number,
archived_date date default sysdate not null
);

We have tables with lots of columns(there are lots of tables with more than 100 columns) and when all column names are explicitly listed like the above syntax, the code becomes huge.

Alternative Syntax:

So i thougt of using the syntax

insert into arch_original select original.*,sysdate from original;  -- sysdate will populate archived_date column

Eventhough the code looks simple and short, i've noticed a drawback to this approach.

Drawback:
For the next release, if developers decide to add/drop a column in the ORIGINAL table in the Original Schema, that change should be reflected in the archive_table's (ARCHIVE schema) DDL script as well. It is practically impossible to keep track of all these changes during the development phase.


If i use

insert into arch_original select original.*,sysdate from original;  

syntax, you will realise that there is change in the table structure only when you encounter an error(due to missing/new column) in the Runtime. But, if you have all the column names listed explicitly like

insert into arch_original@dblink
(col1,
col2,
col3,
.
.
.
.
)
select col1,
col2,
col3,
.
.
.
.
from original_table

then you'll encounter this error during the Compilation itself. I prefer the error due to a missing/new column during the Compilation itself rather than in Runtime.

So what do you guys think? I shouldn't go for

insert into arch_original select original.*,sysdate from original; 

syntax because of the above Drawback. Right?