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?


Ken Needham

How are you doing this in SQL Server? I have done this in Oracle using a stored procedure for add, another for delete and another for edit.

The add procedure adds the record, the edit procedure adds a new record to the table with the same ID. The ID needs to be a sequence to ensure uniqueness.

I have some sample code at home if this is what you need.





Sal

In SQL Server I'm just storing the complete record as XML in a single field of the audit table. Simple in SQL Server but not so straightforward with Oracle.
I need to store records from many different table types in one table, hence the XML.
To do the same thing in Oracle without using XML seems tricky. But I'm new to Oracle so I'm probably missing something obvious.

Ken

Ken Needham