SEO

I'm working with Oracle 10g and need an instance where whenever a
record is modified I can automatically store a copy of that record and
assign it a version number. So at any given time I can see a history
for that given record in the table.

I'm new to Oracle and I would like to avoid writing as much SQL as
possible if there is some built in functionality of trigger to do
this.

The front end of the tool is Oracle Forms.... If this were a Ruby on
Rails app I could simply use acts_as_versioned on my model and wham,
I'm all set!

Thanks!

On Aug 28, 10:33 am, Matthew Williams <matthew.d.willi...@gmail.com>
wrote:
> I'm working with Oracle 10g and need an instance where whenever a
> record is modified I can automatically store a copy of that record and
> assign it a version number. So at any given time I can see a history
> for that given record in the table.
>
> I'm new to Oracle and I would like to avoid writing as much SQL as
> possible if there is some built in functionality of trigger to do
> this.
>
> The front end of the tool is Oracle Forms.... If this were a Ruby on
> Rails app I could simply use acts_as_versioned on my model and wham,
> I'm all set!
>
> Thanks!

Then write a Ruby on Rails app so, wham, you're all set. Otherwise
resign yourself to the 'tedium' of learning how to write triggers to
populate history tables. It isn't rocket science. A copy of your
source table, with some 'bookkeeping' columns added (such as proc_dt,
vers_no, user_id, action) is the starting point; a before insert or
update or delete trigger is the next step. I would visit:

http://tahiti.oracle.com

and search the relevant documentation for triggers and learn how such
things are written. Should you have trouble you can post the code
you've written and we can assist in fixing the errant sections.


David Fitzjarrell

Thanks for the help. If I could write this as a Rails app I most
certainly would (with Oracle on the back end) but with the environment
I'm in right now I'm restricted to the Oracle development tools.

I'm certainly capable of making the other table and writing the
triggers, but if I can shave development time from this task I would
prefer to do so.

I'll give that site you gave me a look, I still might be able to find
a thing or two to help with this task.

Take care
-Matthew Williams

On Aug 28, 2:04 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Aug 28, 10:33 am, Matthew Williams <matthew.d.willi...@gmail.com>
> wrote:
>
> > I'm working with Oracle 10g and need an instance where whenever a
> > record is modified I can automatically store a copy of that record and
> > assign it a version number. So at any given time I can see a history
> > for that given record in the table.
>
> > I'm new to Oracle and I would like to avoid writing as much SQL as
> > possible if there is some built in functionality of trigger to do
> > this.
>
> > The front end of the tool is Oracle Forms.... If this were a Ruby on
> > Rails app I could simply use acts_as_versioned on my model and wham,
> > I'm all set!
>
> > Thanks!
>
> Then write a Ruby on Rails app so, wham, you're all set. Otherwise
> resign yourself to the 'tedium' of learning how to write triggers to
> populate history tables. It isn't rocket science. A copy of your
> source table, with some 'bookkeeping' columns added (such as proc_dt,
> vers_no, user_id, action) is the starting point; a before insert or
> update or delete trigger is the next step. I would visit:
>
> http://tahiti.oracle.com
>
> and search the relevant documentation for triggers and learn how such
> things are written. Should you have trouble you can post the code
> you've written and we can assist in fixing the errant sections.
>
> David Fitzjarrell

On Aug 28, 1:17 pm, Matthew Williams <matthew.d.willi...@gmail.com>
wrote:
> Thanks for the help. If I could write this as a Rails app I most
> certainly would (with Oracle on the back end) but with the environment
> I'm in right now I'm restricted to the Oracle development tools.
>
> I'm certainly capable of making the other table and writing the
> triggers, but if I can shave development time from this task I would
> prefer to do so.
>
> I'll give that site you gave me a look, I still might be able to find
> a thing or two to help with this task.
>
> Take care
> -Matthew Williams
>
> On Aug 28, 2:04 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
>
> > On Aug 28, 10:33 am, Matthew Williams <matthew.d.willi...@gmail.com>
> > wrote:
>
> > > I'm working with Oracle 10g and need an instance where whenever a
> > > record is modified I can automatically store a copy of that record and
> > > assign it a version number. So at any given time I can see a history
> > > for that given record in the table.
>
> > > I'm new to Oracle and I would like to avoid writing as much SQL as
> > > possible if there is some built in functionality of trigger to do
> > > this.
>
> > > The front end of the tool is Oracle Forms.... If this were a Ruby on
> > > Rails app I could simply use acts_as_versioned on my model and wham,
> > > I'm all set!
>
> > > Thanks!
>
> > Then write a Ruby on Rails app so, wham, you're all set. Otherwise
> > resign yourself to the 'tedium' of learning how to write triggers to
> > populate history tables. It isn't rocket science. A copy of your
> > source table, with some 'bookkeeping' columns added (such as proc_dt,
> > vers_no, user_id, action) is the starting point; a before insert or
> > update or delete trigger is the next step. I would visit:
>
> >http://tahiti.oracle.com
>
> > and search the relevant documentation for triggers and learn how such
> > things are written. Should you have trouble you can post the code
> > you've written and we can assist in fixing the errant sections.
>
> > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

'Shaving development time' is one thing, having other people do your
work for you under the GUISE of 'shaving development time' is another.

I have no problem assisting you with this task, but I will not write
it for you. The online documentation is an excellent source for the
information you need. After you get started writing this if you still
have problems post again and I will be happy to assist you.


David Fitzjarrell

On Aug 28, 2:40 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Aug 28, 1:17 pm, Matthew Williams <matthew.d.willi...@gmail.com>
> wrote:
>
>
>
> > Thanks for the help. If I could write this as a Rails app I most
> > certainly would (with Oracle on the back end) but with the environment
> > I'm in right now I'm restricted to the Oracle development tools.
>
> > I'm certainly capable of making the other table and writing the
> > triggers, but if I can shave development time from this task I would
> > prefer to do so.
>
> > I'll give that site you gave me a look, I still might be able to find
> > a thing or two to help with this task.
>
> > Take care
> > -Matthew Williams
>
> > On Aug 28, 2:04 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
> > > On Aug 28, 10:33 am, Matthew Williams <matthew.d.willi...@gmail.com>
> > > wrote:
>
> > > > I'm working with Oracle 10g and need an instance where whenever a
> > > > record is modified I can automatically store a copy of that record and
> > > > assign it a version number. So at any given time I can see a history
> > > > for that given record in the table.
>
> > > > I'm new to Oracle and I would like to avoid writing as much SQL as
> > > > possible if there is some built in functionality of trigger to do
> > > > this.
>
> > > > The front end of the tool is Oracle Forms.... If this were a Ruby on
> > > > Rails app I could simply use acts_as_versioned on my model and wham,
> > > > I'm all set!
>
> > > > Thanks!
>
> > > Then write a Ruby on Rails app so, wham, you're all set. Otherwise
> > > resign yourself to the 'tedium' of learning how to write triggers to
> > > populate history tables. It isn't rocket science. A copy of your
> > > source table, with some 'bookkeeping' columns added (such as proc_dt,
> > > vers_no, user_id, action) is the starting point; a before insert or
> > > update or delete trigger is the next step. I would visit:
>
> > >http://tahiti.oracle.com
>
> > > and search the relevant documentation for triggers and learn how such
> > > things are written. Should you have trouble you can post the code
> > > you've written and we can assist in fixing the errant sections.
>
> > > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> 'Shaving development time' is one thing, having other people do your
> work for you under the GUISE of 'shaving development time' is another.
>
> I have no problem assisting you with this task, but I will not write
> it for you. The online documentation is an excellent source for the
> information you need. After you get started writing this if you still
> have problems post again and I will be happy to assist you.
>
> David Fitzjarrell

Nope, no troubles. Nor am I looking for someone to write this for
me. I'm working on it as we speak in fact. I was just looking to see
if there were any capabilities of 10g that I could set a table to
automatically have it maintain history. Or any prebuilt frameworks.
(like the acts_as_version plugin for Rails which I was using as an
example).

I'm big into Open Source, I try to implement code re-use wherever I
can. Would be a waste to reinvent the wheel if there was a script to
automate the task I'm up against already out there floating around.
Time is money.

Take care.

On Aug 28, 2:22 pm, Matthew Williams <matthew.d.willi...@gmail.com>
wrote:
> On Aug 28, 2:40 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
>
>
>
> > On Aug 28, 1:17 pm, Matthew Williams <matthew.d.willi...@gmail.com>
> > wrote:
>
> > > Thanks for the help. If I could write this as a Rails app I most
> > > certainly would (with Oracle on the back end) but with the environment
> > > I'm in right now I'm restricted to the Oracle development tools.
>
> > > I'm certainly capable of making the other table and writing the
> > > triggers, but if I can shave development time from this task I would
> > > prefer to do so.
>
> > > I'll give that site you gave me a look, I still might be able to find
> > > a thing or two to help with this task.
>
> > > Take care
> > > -Matthew Williams
>
> > > On Aug 28, 2:04 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
> > > > On Aug 28, 10:33 am, Matthew Williams <matthew.d.willi...@gmail.com>
> > > > wrote:
>
> > > > > I'm working with Oracle 10g and need an instance where whenever a
> > > > > record is modified I can automatically store a copy of that record and
> > > > > assign it a version number. So at any given time I can see a history
> > > > > for that given record in the table.
>
> > > > > I'm new to Oracle and I would like to avoid writing as much SQL as
> > > > > possible if there is some built in functionality of trigger to do
> > > > > this.
>
> > > > > The front end of the tool is Oracle Forms.... If this were a Ruby on
> > > > > Rails app I could simply use acts_as_versioned on my model and wham,
> > > > > I'm all set!
>
> > > > > Thanks!
>
> > > > Then write a Ruby on Rails app so, wham, you're all set. Otherwise
> > > > resign yourself to the 'tedium' of learning how to write triggers to
> > > > populate history tables. It isn't rocket science. A copy of your
> > > > source table, with some 'bookkeeping' columns added (such as proc_dt,
> > > > vers_no, user_id, action) is the starting point; a before insert or
> > > > update or delete trigger is the next step. I would visit:
>
> > > >http://tahiti.oracle.com
>
> > > > and search the relevant documentation for triggers and learn how such
> > > > things are written. Should you have trouble you can post the code
> > > > you've written and we can assist in fixing the errant sections.
>
> > > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > 'Shaving development time' is one thing, having other people do your
> > work for you under the GUISE of 'shaving development time' is another.
>
> > I have no problem assisting you with this task, but I will not write
> > it for you. The online documentation is an excellent source for the
> > information you need. After you get started writing this if you still
> > have problems post again and I will be happy to assist you.
>
> > David Fitzjarrell
>
> Nope, no troubles. Nor am I looking for someone to write this for
> me. I'm working on it as we speak in fact. I was just looking to see
> if there were any capabilities of 10g that I could set a table to
> automatically have it maintain history. Or any prebuilt frameworks.
> (like the acts_as_version plugin for Rails which I was using as an
> example).
>
> I'm big into Open Source, I try to implement code re-use wherever I
> can. Would be a waste to reinvent the wheel if there was a script to
> automate the task I'm up against already out there floating around.
> Time is money.
>
> Take care.- Hide quoted text -
>
> - Show quoted text -

Oracle isn't open source. So, no, there is no 'automatic magical
clairevoyant mechanism in Oracle that knows what you want and gives it
to you'. You're on your own, here. Yes, time is money. And if you
do this correctly that money will be well-spent.

Again, I have no problems assisting with this, should you run into
difficulties.


David Fitzjarrell

On Tue, 28 Aug 2007 15:33:46 -0000, Matthew Williams
<matthew.d.williams@gmail.com> wrote:

>I'm working with Oracle 10g and need an instance where whenever a
>record is modified I can automatically store a copy of that record and
>assign it a version number. So at any given time I can see a history
>for that given record in the table.
>
>I'm new to Oracle and I would like to avoid writing as much SQL as
>possible if there is some built in functionality of trigger to do
>this.
>
>The front end of the tool is Oracle Forms.... If this were a Ruby on
>Rails app I could simply use acts_as_versioned on my model and wham,
>I'm all set!
>
>Thanks!

In the past Oracle Developer was capable of generating these history
tables.
Oracle Enterprise Edition has a feature called Oracle Workspace
Manager.
Examples probably on http://www.psoug.org/reference
(Morgan's library)

I have developed to a tool to generate those tables and triggers.
As I am not in the office I don't have access to it know.
It is pretty generic, and you end up with a set of pl/sql with the
table definition being referenced as a subtype.
Which means: any table Alteration and you only need to recompile (ie
not change) that piece of PL/SQL.

--
Sybrand Bakker
Senior Oracle DBA

<sybrandb@hccnet.nl> schreef in bericht
news:it39d3t14q2n951eqf7aiumr9phvpubd7j@4ax.com...
> On Tue, 28 Aug 2007 15:33:46 -0000, Matthew Williams
> <matthew.d.williams@gmail.com> wrote:
>
>>I'm working with Oracle 10g and need an instance where whenever a
>>record is modified I can automatically store a copy of that record and
>>assign it a version number. So at any given time I can see a history
>>for that given record in the table.
>>
>>I'm new to Oracle and I would like to avoid writing as much SQL as
>>possible if there is some built in functionality of trigger to do
>>this.
>>
>>The front end of the tool is Oracle Forms.... If this were a Ruby on
>>Rails app I could simply use acts_as_versioned on my model and wham,
>>I'm all set!
>>
>>Thanks!
>
> In the past Oracle Developer was capable of generating these history
> tables.
> Oracle Enterprise Edition has a feature called Oracle Workspace
> Manager.
> Examples probably on http://www.psoug.org/reference
> (Morgan's library)
>
> I have developed to a tool to generate those tables and triggers.
> As I am not in the office I don't have access to it know.
> It is pretty generic, and you end up with a set of pl/sql with the
> table definition being referenced as a subtype.
> Which means: any table Alteration and you only need to recompile (ie
> not change) that piece of PL/SQL.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Actually it was (and is) Oracle Designer which is capable of generating
history tables, or as they call it: journalling tables and triggers to
populate them. You could reverse engineer your tables to Designer and
generate the trigger code and journal tables. But if Sybrand's tools work
(and I don't doubt they do!) you should use those!

Shakespeare

On Wed, 29 Aug 2007 20:56:56 +0200, "Shakespeare" <whatsin@xs4all.nl>
wrote:

>But if Sybrand's tools work
>(and I don't doubt they do!) you should use those!

Obviously I don't own the copyrights. I can post the basic idea (next
week) and the OP has to work from there.
If you don't make the mistake to put every procedure in one package,
as I did the first time, it should work without problem.
Yes, you end up with n packages, and that can be a nuisance.

--
Sybrand Bakker
Senior Oracle DBA