Re: Field names?

In article <7g0bg5$3mb$1_at_news.eisa.net.au>,   "David Younger" <david_at_proware.com.au> wrote:
> 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
>
>

Two resources are available that enable you to do the above:

1. There is a system table (USER_TAB_COLUMNS, correct me if I'm wrong) that contains a list of tables and their corresponding columns.
2. The dynamic SQL package DBMS_SQL allows you to construct SQL messages at run-time.

So you can get the list of columns from USER_TAB_COLUMNS, and then use that to form a query as follows:

my_query := 'SELECT my_array_of_columns(index_into_array) FROM MY_TABLE_NAME';

and then use DBMS_SQL to run the query.

I'm not sure, but you may even be able to run a query of the type:

SELECT my_array_of_columns(index_into_array) FROM MY_TABLE_NAME;

directly in Oracle 8. This would save you from using DBMS_SQL.

Salaam Yitbarek

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Apr 26 1999 - 11:01:35 CDT