Forum Stats

  • 3,824,838 Users
  • 2,260,429 Discussions
  • 7,896,325 Comments

Discussions

Passing data from one row/record structure to another by attribute name matching

dirkvanhaute
dirkvanhaute Member Posts: 27 Bronze Badge
edited Mar 19, 2020 6:09AM in Database Ideas - Ideas

In join syntax, we have an option to compare columns with the same name

.. join tab2 using (col1, col2)

This is useful because they tend to contain the same data. 

In a similar way, moving data from one row/record structure to another would be easier if there was a way to tell the database : "copy the values from all attributes of the source row/record to the attributes with the same name in the destination row/record.  If there is no such attribute, ignore it.  For the unique names in the destination, take null as value".

I know code can be generated, but Oracle already has the better invalidation mechanisms, and we don't want to generate the code on every call.

Problem 1 : Copy records from table t to t_hist where t_hist has an extra deleted_date column:

Whe need a cast like this:

E.g;

create table t      (num1 integer, num2 integer, num3 integer, num4 integer);

create table t_hist (num2 integer, num1 integer, num3 integer, delete_date date default on null sysdate )

insert into t_hist

select select cast(* as t_hist%rowtype) // new
from t;

Would be the equivalent of

insert into t_hist

    select num1, num2, num3, null as delete_date

from t;

Extra thoughts

insert into t_hist
select cast( (sysdate + num4 as delete_date, t.*) as t_hist%rowtype)
from t;

create table x (delete_date date);

insert into t_hist
select cast( (x.delete_date, b.*) as t_hist%rowtype)
from   x, t;

Problem 2 : The same issue with row types & cursor types:

E.g.

declare

   t_rec t%rowtype;
   t_hist_rec t_hist%rowtype;

begin

   -- Copies num1..3 from t_rec to t_hist_rec and sets delete_date to null
   t_hist_rec := cast(t_rec as t_hist%rowtype);

end;

After   from Morten Braeten is fixed, it could be applied to database trigger :new and : old also

Problem 3 : The same issue translated to record types

Something a little bit more complex than idea from Lukas Eder :

dirkvanhauteberxSven W.sdstuber
5 votes

Active · Last Updated