Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 22nd, 2004, 01:06 PM
Registered User
 
Join Date: Jan 2004
Location: Farnham, , United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Storing complete table rows in a single field

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
  #2 (permalink)  
Old January 22nd, 2004, 01:26 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old January 22nd, 2004, 01:45 PM
Registered User
 
Join Date: Jan 2004
Location: Farnham, , United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Max function to return single rows. beerOne SQL Language 1 November 6th, 2008 04:52 PM
Storing checkboxes value in a single row abhishekkashyap27 C# 2005 2 April 8th, 2008 12:13 AM
Not getting complete table incorporated alh45 BOOK: Beginning Access 2003 VBA 0 March 22nd, 2006 04:19 PM
copy complete data in one table to another ramanadyv SQL Language 2 March 4th, 2006 11:53 PM
Storing single and double quotes in text roniestein Access 4 December 30th, 2003 05:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.