Skip to Main Content
  • Questions

  • Procedure to Move data from one table to other table based on date column

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 27, 2011 - 4:14 pm UTC

Last updated: April 28, 2011 - 11:58 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I am new as pl/sql developer. I have one issue. I will really appreciate your help. I have to move data from 'some tables'(A) to 'respective history tables'(B), based on date column of tables A.Every table in A has seprate retention period. Table structure is same for A and B.If data is older than 'X' days in tables A, then it has to move to B and this job will run daily. So, data in tables A has to include only latest 'X'days data everytime. There is one lookup like table C, which has two columns: table_name and retention days(X).Table C has list of tables(includes tables A) which need to move thier data to their respective history table based on retention period in C. I have to write procedure to move data from table A to Table B based on retention period mention in table C for tables A. After inserting data into respective tables B, delete it from tables A.
Please help me on how to do this. I will be very thankful to you.

and Tom said...

It would look something like this, this is detailed 'psuedo' code, you will code it the rest of the way using your schema names/object names/etc:

(this is pretty straightforward, easy stuff - am I missing some level of complexity???)


declare
   l_date date := sysdate;
begin

for x in (select l_date-retention dt, base_table_name, hist_table_name
            from lookup_table)
loop
    execute immediate 
   'insert /*+ append */ into ' || 
    dbms_assert.simple_sql_name( x.hist_table_name ) ||
   'select * from ' ||  
    dbms_assert.simple_sql_name( x.base_table_name ) ||
   'where date_column <= :x' using x.dt;

    execute immediate
   'delete from ' ||
    dbms_assert.simple_sql_name( x.base_table_name ) ||
   'where date_column <= :x' using x.dt;
end loop;

end;



It would be MUCH cooler if you could use partitioning for this - then it would become a simple partition exchange to get the data out and move it - no redo, no undo, fast, very very very few resources.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pl/sql

sam, April 28, 2011 - 10:20 am UTC

Tom:

Do you always use DYNAMIC SQL for this case if the tables are looked up instead of hardcoding the names using sql

insert into T1 select * from T2 Where....


Never seen dbms_assert before. DO you need that to reference the name
Tom Kyte
April 28, 2011 - 11:57 am UTC

You cannot bind identifiers. If the list of tables (identifiers) is dynamic (table driven), then using dynamic SQL would be applicable.

Never say Never
Never say Always
I Always say.

I will not say that I always use dynamic sql for this case, but there is a good chance I might.


dbms_assert is documented:

http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_assert.htm#ARPLS65375 

If you do not know of it AND you write dynamic sql
then
  you are probably subject to sql injection
end if




pl/sql

sam, April 28, 2011 - 7:42 pm UTC

Great. I will start using it with 11g with dynamic sql. it is not in 9i.

http://www.oracle-base.com/articles/10g/dbms_assert_10gR2.php
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.