Re:Variable column names?

Hi all,
You can get the field names from
dba_tab_columns.table_name and
dba_tab_colunms.column_id

But I haven't found a way to pass these colunmns as variables into PL/SQL procedures where they would
be used in cursors and update statements.

I'm actually trying to implement the reverse of an audit trail. I'd like to have a generic iransaction nput table
with colunns

TABLE_NAME, COLUMN_NAME, VALUE where the table name and the column name direct the PLSQL to put the value into the proper column.

It seems that it can be done Dynamic SQL. I'd like to avoid Pro*C or OCI.

It would be great if you could use Dynamic SQL from PL/SQL let's say by passing a string to a Oracle stored
procedure but that doesn't seem available.

You could hack it up by creating separate procedures for each column testing for the col name and then calling
the appropriate procudure but that would require procedure changes every time you created a new column.

I can implement that by using Perl to generate the SQL required but this may be too slow for some projects.

I also looked into Oraperl but I was surprised that OraPerl seems based on Perl 4 which we can't
use here because of out Y2K support standards.

So the question is:

What's the simpliest way to use variable table and column names in cursors, inserts, updates. etc.

-ed

Simon Hedges wrote:

> You can query the data dictionary for the table
> (e.g. all_tab_columns or some such). If you ever manage to
> write a generic utility of this type, I'd be interested to see it, as it
> would be very useful.
>
> Simon Hedges
> Gloucester
> UK
>
> >David Younger wrote in message <7g0bg7$3mb$2_at_news.eisa.net.au>...
> >In a PL/SQL script, how can I programatically iterate through all the
> fields
> >in a table without specifically knowing the field names?
> >
> >Specifically, I am trying to write a generic auditing function that will be
> >triggerd on insert, update and delete that will be passed the table name
> and
> >will write out all the old/current values of each field.
> >
> >Thanks
> >David Younger
> >
> >

• Ed Kulis 408-341-6061 Hal Computer Systems
• MIS Manager ekulis_at_hal.com Campbell, CA 95008
• Pager 408-860-7756 408-379-7000