Re: Storing complete table rows in a single field

ken_at_kentl.com (Captain Cabbage) wrote in message news:<41d41973.0401220937.223b9f5c_at_posting.google.com>...
> I need to be able to store complete rows retrieved from various tables
> (SELECT * ...) as a single field in another table. Basically I need to
> keep a record of every change made to various tables, like an audit
> trail, but I need the entire record not just the changes.
> I can do this in SQL Server but now I need to port it to Oracle.
> I get the feeling that Object Tables and %ROWTYPE comes into it
> somewhere but I can't quite get it all to work together.
>
> Any ideas?

Hmmm, without knowing which version of Oracle it's hard to recommend anything specific. So it has to be a general advice, or roll-your-own.

O-Tables and ROWTYPE may come into it indirectly. Through PL/SQL. I'd look at triggers, with PL/SQL code to merge the data as one column only. You won't get it automatically generated, unless you roll your own PL/SQL code generator based on dictionary information for each table. I'd also be VERY careful with dumping the lot from multiple tables into a single audit table: great recipe for a HUGE bottleneck (or should I say a *small* bottleneck?).

And that's about all I can come up with without more specific info.

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Thu Jan 22 2004 - 20:53:23 CST