Logging or spooling updated values in a table to a file or table
Hi,
I have a situation where I am updating a SINGLE column in an Oracle table using SQL from a ksh script. Is there anyway for me to spool or log information about the rows that where actually updated ? I would like to save the log of rows changed to a flat file. If not, a temp table will be OK as well. If this is not feasible via spool or log files, how else would you suggest capturing this information ?
part of the ksh scripts:
...
sqlplus -s userid/password <<!
set verify off heading off echo off
update tbl1 set tbl1.col1 = .....
exit;
...
Please note that triggers and stored procs are not an option. Points awarded to actual ksh code or SQL script. No points awarded to vague direction or hints.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use a log table:
SQL> select column1 from tablea
2 /
COLUMN1
----------
1
2
3
4
Elapsed: 00:00:00.00
SQL> select column1 from tableb
2 /
COLUMN1
----------
2
3
Elapsed: 00:00:00.00
Look at this simple script:
set verify off
accept id prompt 'enter id: '
insert into tableb select * from tablea where column1 = &id;
update tablea set column1 = 5 where column1 = &id;
SQL> @test
enter id: 4
1 row created.
Elapsed: 00:00:00.00
1 row updated.
Elapsed: 00:00:00.00
SQL> select column1 from tablea
2 /
COLUMN1
----------
1
2
3
5
Elapsed: 00:00:00.00
SQL> select column1 from tableb
2 /
COLUMN1
----------
2
3
4
Elapsed: 00:00:00.00
Now, it all depends on what value are supplying.
Can you post your update?
SQL> select column1 from tablea
2 /
COLUMN1
----------
1
2
3
4
Elapsed: 00:00:00.00
SQL> select column1 from tableb
2 /
COLUMN1
----------
2
3
Elapsed: 00:00:00.00
Look at this simple script:
set verify off
accept id prompt 'enter id: '
insert into tableb select * from tablea where column1 = &id;
update tablea set column1 = 5 where column1 = &id;
SQL> @test
enter id: 4
1 row created.
Elapsed: 00:00:00.00
1 row updated.
Elapsed: 00:00:00.00
SQL> select column1 from tablea
2 /
COLUMN1
----------
1
2
3
5
Elapsed: 00:00:00.00
SQL> select column1 from tableb
2 /
COLUMN1
----------
2
3
4
Elapsed: 00:00:00.00
Now, it all depends on what value are supplying.
Can you post your update?
ASKER
Sorry, I was not very clear in my original post. What I am looking to capture is the actual value of the column that is updated BEFORE and AFTER the update. In other words, in the code above, I need to capture of the value of tbl1.col1 prior and after it is updated. My update query is pretty simple, all it does is update tbl1.col1 which meet a very simple criteria.
tbl1 has about 10000 rows and about 100 are updated every time the ksh script runs.
tbl1 has about 10000 rows and about 100 are updated every time the ksh script runs.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...
sqlplus -s userid/password <<!
set verify off heading off echo off
set serveroutput on size 1000000
spool logfile.spl
DECLARE
TYPE plstab IS TABLE OF ROWID INDEX BY PLS_INTEGER;
tbl plstab;
BEGIN
update tbl1 set tbl1.col1 = <whatever>
where <condition>
RETURNING rowid BULK COLLECT INTO tbl;
IF tbl.COUNT > 0 THEN
FOR i IN tbl.FIRST..tbl.LAST LOOP
dbms_output.put_line(tbl(i
END LOOP;
END IF;
END;
/
exit;
...
Hope that helps!