Home » SQL & PL/SQL » SQL & PL/SQL » Need to display all column's name and value of a table in a query (Oracle 10G/11G)
Need to display all column's name and value of a table in a query [message #405863] Sat, 30 May 2009 11:01 Go to next message
krkris
Messages: 2
Registered: May 2009
Junior Member
HI Guys,
I'm need to write a SQL query to generate an audit report.
I have Account_history table, this table tracks changes to account table.
whenever insert/update/delete performed in the account table, modified records are copied to history table with change timestamp value.
My report shud show changes occured on a given day in following format.

account_name field_name from_value to_value updated_by changed_on

here field_name is database column name which was modified.
from_value original value and to_value is new value.

If it was a new account, then it will have one row in the history table and I have show all the fields and its value (in to_value) field.

I'm using following query for this report.


select account_name,
'column 1' AS field_name , --hard coded value for column1 name
.....
from account_history
where ...

UNION ALL

select account_name,
'column 2' AS field_name , --hard coded value for column1 name


.....
from account_history
where ...
.....


like this I'll repeat same query for each column in the table.
Is there any better way to write this query???
Is there any way to avoid hard coding of each column name and repeat the same query again n again for each column

Re: Need to display all column's name and value of a table in a query [message #405866 is a reply to message #405863] Sat, 30 May 2009 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>from_value original value and to_value is new value.
How do you handle different datatypes; such as DATE, NUMBER, VARRAY, NESTED TABLES, XMLTYPE, CLOB, etc.?

>Is there any better way to write this query???
IN ('COLUMN1','COLUMN2','COLUMN3', ETC.)

[Updated on: Sat, 30 May 2009 11:33]

Report message to a moderator

Re: Need to display all column's name and value of a table in a query [message #405867 is a reply to message #405866] Sat, 30 May 2009 12:33 Go to previous messageGo to next message
krkris
Messages: 2
Registered: May 2009
Junior Member
HI Guys,
I'm need to write a SQL query to generate an audit report.
I have Account_history table, this table tracks changes to account table.
whenever insert/update/delete performed in the account table, modified records are copied to history table with change timestamp value.
My report shud show changes occured on a given day in following format.

Say the account_history table has following columns

acount_history(
account_name,
client_name,
account_type,
client_address,
updated_by,
change_timestamp,
delete_flg
);

and

account table
(
account_name,
client_name,
account_type,
client_address
);

whenever a row is inserted trigger on account table copies same row + user_name of the person inserting and current timestamp to history table with delete_flg = 2.
whenever a row is modified trigger on account table copies old row + user_name of the person modifying and current timestamp to history table with delete_flg = 2.

whenever a row is inserted trigger on account table copies delete row + user_name of the person inserting and current timestamp to history table with delete_flg = 1 (indicates its deleted).

following is the snapshot of the data where accounts acc1 and acc3 have been updated and account acc2 has been created.
for acc1 account_type has been changed to type2 from type1.
for acc3 client_address column has changed.
since acc2 is new account , I have to report all the columns for that account.

account_name |------ field_name | from_value | --to_value--- | updated_by--- | changed_on
-------Acc1-------- | Account_type | ----type1------- | --type2--- | ABCccccccccccc | 02/01/2009
-------Acc3-------- | client_address | --address1----- | -address2 | XYzzzzzzzzzzzzz | 02/01/2009
------Acc2--------- | client_name--- |------------------- | -xxxxxxxx | ABCccccccccccc | 02/01/2009
-----Acc2---------- | account_type |-------------------- | -type----- | ABCccccccccccc | 02/01/2009
----Acc2----------- | client_address |------------------- | -address- | ABCccccccccccc | 02/01/2009



here field_name is database column name which was modified.
from_value original value and to_value is new value.

If it was a new account, then it will have one row in the history table and I have show all the fields and its value (in to_value) field.

I'm using following query for this report.


select account_name,
'Account_type' AS field_name , --hard coded value for column1 name
.....
from account_history
where ...

UNION ALL

select account_name,
'client_address ' AS field_name , --hard coded value for 2nd column name


.....
from account_history
where ...
.....

UNION ALL

select account_name,
'client_name' AS field_name , --hard coded value for 2nd column name


.....
from account_history
where ...
.....

like this I'll repeat same query for each column in the table.
Is there any better way to write this query???
Is there any way to avoid hard coding of each column name and repeat the same query again n again for each column


[Updated on: Sat, 30 May 2009 12:52]

Report message to a moderator

Re: Need to display all column's name and value of a table in a query [message #405868 is a reply to message #405863] Sat, 30 May 2009 13:02 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SELECT * FROM account_history WHERE .....
Why is SQL above inadequate for reporting changes?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Previous Topic: compilation errors in stored procedure
Next Topic: spool output to a file in dbms_job.submit
Goto Forum:
  


Current Time: Tue Dec 06 06:33:50 CST 2016

Total time taken to generate the page: 0.19598 seconds