Re: Supporting multiple oracle versions in a trigger

"Jack Addington" <jaddington_at_shaw.ca> wrote in message news:p_S5f.240333$oW2.157677_at_pd7tw1no...
> "HansF" <News.Hans_at_telus.net> wrote in message
> news:pan.2005.10.19.21.37.34.954878_at_telus.net...
>
> > Pretty please - explain WHY you do this. While there are valid reasons,
> > they are few and far between.
>
> I will try to explain and am interested in your feedback/suggestions or
> enhancment ideas to clean things up from a dba side.
>
> Basic Overview:
>
> My application is a data driven generic data collection tool that allows
the
> client to define 'data collections' which are made up of one or more 'data
> fields'. I have a collection table, a field definition table, and a
> collection_field table.
>
> These collection/field combos are basically pseudo tables/columns to the
> user but I store / manage all the data in five core tables
> (numbers,strings,dates,attachments, clobs) and use data header tables to
> keep track of everything and link the data back to the appropriate
> collection/field. I collect each piece of data as a single row in the
core
> tables.
>
> The easiest example is collecting data from a test - Each field is a
> question and answer. The field.txt column is the question and the
> field.definition columns define the type of data accepted. Depending on
the
> data type the actual data is stored in the appropriate core data table.
>
> I then offer two methods of updating the data for the user:
>
> 1) I use a generic view and a insteadof trigger to present the data (one
> field per row) and then on update pass the data based on type to an
> appropriate update function. This allows the client to quickly modify or
> add to the data collection. Fields can be dropped, added, hidden,
> re-formatted, etc without need to recompile anything.
>
> 2) I have one transpose view per collection that uses lookup functions to
> transpose the data from the cores tables into a single row. This also has
> an instead of trigger that updates the data. This method allows the use
of
> custom data entry screens but restricts how much change the client can do
to
> the collection.
>
> Reason for Dynamic SQL:
>
> The reason for dropping/creating tables is that I provide real indexed
> tables for each data collection broken out by client. These tables are
used
> for advanced searching, separating different clients data, as well as
> providing simple data dumps for clients to query from other tools. After
> each update on the data I refresh the client tables with a simple
> delete/insert from the transpose view. The transpose view works very
> quickly with primary keys but isn't great for generic searches. Ideally I
> would have used snapshots but I couldn't get quick refreshes due to the
> complicated sql in the view.
>
> Whenever the client updates the collection/field definition, such that the
> base table is out of date, I drop the table, recreate it, and repopulate
it.
> The frequency once the client is setup and collecting data is quite rare
and
> is done as part of an administrative process. I haven't done too much
> collision testing where data updates are going on while the client tables
> are being re-created.
>
> Any thoughts are greatly appreciated.
>
> thx
>
> Jack
>
>

Ouch! This is a very unscalable solution. Let me ask a question, when you write code in C or Java do you just make everything a generic object or use a generic struct? Why would you do that in a database? (other than it is possible)
Jim Received on Thu Oct 20 2005 - 22:15:56 CDT