Flash back query - How to find when a column was updated?

Oracle 10g offers powerful mechanism of retrieving historic data via flash back queries. Simply put, we can find the state a table was in at a particular point in time. For example if you want to find the state of the table on September 11 2009, we can write a query like this.

SELECT *
FROM FND_USER
AS OF TIMESTAMP (TO_TIMESTAMP ('11-SEP-2009 08:46:40','DD-MON-YYYY HH24:MI:SS'));

This will give the output as though the query was run on '11-SEP-2009 08:46:40' provided flash back query is enabled in the system and data is available for the date. Usually in the system this data is available for a few days depending on DBA setups.

Uses

1. This feature is much useful to analyse synchronization issues. Frequently we encounter situations like a report showing different output for the same input when executed at different points in time. Some issue occurred at some point in time but not replicable now. Using flash back query we can see and analyze the data in the table when the report was run and arrive at the conclusion.
   



2. In a system with out proper auditing functionalities, it will be useful to find the time at which data was changed so as to find which program or user manipulated data incorrectly.

Here is a PL/SQL script which can be used to find when particular data was modified.

DECLARE
v_sql VARCHAR2 (2000);
v_table_name VARCHAR2 (30) := '&table_name';
v_where_clause VARCHAR2 (2000) := '&where';
v_count NUMBER;
v_start BOOLEAN := TRUE;
last_scn SYS.smon_scn_time.time_dp%TYPE;
BEGIN
FOR rec_scn IN (SELECT SCN,
time_dp
FROM SYS.smon_scn_time
ORDER BY SCN DESC)
LOOP
v_sql :=
'select count(rowid) from '
|| v_table_name
|| ' as of scn('
|| rec_scn.SCN
|| ') where '
|| v_where_clause;

EXECUTE IMMEDIATE v_sql
INTO v_count;
IF (v_count = 0)
THEN
IF (v_start = TRUE)
THEN
DBMS_OUTPUT.put_line ('The record does not exists in the system');
ELSE
DBMS_OUTPUT.put_line ( 'The record was created between '
|| TO_CHAR (rec_scn.time_dp,
'DD-MON-YYYY HH24:MI:SS')
|| ' and '
|| TO_CHAR (last_scn,
'DD-MON-YYYY HH24:MI:SS'));
END IF;

EXIT;
END IF;

last_scn := rec_scn.time_dp;
v_start := FALSE;
END LOOP;
IF (v_count <> 0)
THEN
DBMS_OUTPUT.put_line ( 'The record was created before '
|| TO_CHAR (last_scn, 'DD-MON-YYYY HH24:MI:SS'));
END IF;
END;

For example if you want to find when user with a particular id was created, you can use this script. First write a query to fetch the particular record only


SELECT *
FROM fnd_user
WHERE user_id = 10964;


Run the script filling in the two parameters.

Table_Name :- 'FND_USER'
Where_clause :- 'user_id = 10964'


The script will output the approximate time in which the record was created as given below.

"The record was created between 11-SEP-2009 12:46:06 and 11-SEP-2009 12:51:37"

You can compare the time in CREATION_DATE column with the script output and see if it matches. Not all the tables will have audit columns and the script will be more useful in such conditions.

If the record was created long back and flash back data is not available, then message will be displayed like

"The record was created before 07-SEP-2009 01:15:39"


If we want to find when the EMAIL_ADDRESS of a user was modified, we can run the script as given below. First write the query to fetch exact record.


SELECT *
FROM fnd_user
WHERE user_id = 7 and email_adress='wizard@oz.org'


This query should return one and only one row.


Now run the script with the following parameters

Table_Name :- 'FND_USER'
Where_clause :- 'user_id = 7 and email_adress=''wizard@oz.org''


(Notice that for quotes an additional quote should be used as escape charecter.)

The output will be

'The record was created between 08-SEP-2009 08:15:22 and 08-SEP-2009 08:15:22'

Limitations

1. Flash back data is available only for a limited time period based on DBA settings.



2. Accurate time of modification can't be found. Usually we will get a time period of 5 minutes as output.

The script will be useful to analyse non-reproducible data issues provided flash back query is enabled and historical data is available for the time.

Five Situations where NVL must be used

When I was undergoing my green field training on SQL and PL/SQL basics, I was introduced to the NVL built-in. A simple built in with 2 parameters that will return the first parameter if it is not NULL and return second parameter otherwise. Being new to database programming I thought it can be handy some times. Some times? Well in my last PL/SQL code of 1000 lines, I used 13 of them. So for me this is the most used built-in.

Here I am trying to explain five situations where we must use NVL. To be honest, I learned most of them in the hard way. That is after defects were reported on my code during testing.

1) Date Range checking

I made this mistake back when I accessed value from a value set for one of my programs for first time

SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = &value_set_id
AND SYSDATE BETWEEN start_date_active AND end_date_active;

The comparison with NULL always leads to FALSE. The correct statement will be

SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = &value_set_id
AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)
AND NVL (end_date_active, SYSDATE + 1);

2. Comparison using Not Equal To<>

This is similar to the first case, but is less obvious.

Consider the following block

DECLARE
a NUMBER := 5;
BEGIN
IF (a <> 5)
THEN
DBMS_OUTPUT.put_line (a ' is not equal to 5');
ELSE
DBMS_OUTPUT.put_line (a ' is equal to 5');
END IF;
END;

Perfect? Even I thought so. But not when 'a' is NULL.

DECLARE
a NUMBER := NULL;
BEGIN
IF (a <> 5)
THEN
DBMS_OUTPUT.put_line (a ' is not equal to 5');
ELSE
DBMS_OUTPUT.put_line (a ' is equal to 5');
END IF;
END;

Output says " is equal to 5". The point is NULL is neither equal to not not equal to any other value. So null<>5 returns false and else part of the condition works. The correct code will be

DECLARE
a NUMBER := NULL;
BEGIN
IF (NVL (a, -1) <> 5)
THEN
DBMS_OUTPUT.put_line (a ' is not equal to 5');
ELSE
DBMS_OUTPUT.put_line (a ' is equal to 5');
END IF;
END;


3. Summary Functions

The data in my table 'table1' is

ID	AMOUNT
1	20
2	70
3	40
4
5	60
6	80
7	100

Now if I want to take the sum of records with id 8, well that record does not exist and mathematically 0 is the desired result.

SELECT SUM (amount)
FROM table1
WHERE ID = 8;

But the above SELECT will return null instead. So an NVL must be added to solve this.

SELECT NVL (SUM (amount), 0)
FROM table1
WHERE ID = 8;


4.Arithmetical Expressions

If any value in the calculation is NULL, the entire result becomes null regardless of other values. Try this code.

CREATE OR REPLACE PROCEDURE calculate (
num1 NUMBER,
num2 NUMBER
)
IS
a NUMBER := num1;
b NUMBER := num2;
BEGIN
DBMS_OUTPUT.put_line ('a+b=' (a + b));
DBMS_OUTPUT.put_line ('a-b=' (a - b));
DBMS_OUTPUT.put_line ('a*b=' (a * b));
DBMS_OUTPUT.put_line ('a/b=' (a / b));
END calculate;


The procedure can be called as

BEGIN
calculate (10, 5);
END;

The output is

a+b=15
a-b=5
a*b=50
a/b=2

Now suppose 'a' is null

BEGIN
calculate (null, 5);
END;

All outputs becomes NULL.

a+b=
a-b=
a*b=
a/b=

To avoid this we have to replace the possible NULL values with appropriate values using NVL. Rewrite the procedure as

CREATE OR REPLACE PROCEDURE calculate (
num1 NUMBER,
num2 NUMBER
)
IS
a NUMBER := NVL (num1, 0);
b NUMBER := NVL (num2, 0);
BEGIN
DBMS_OUTPUT.put_line ('a+b=' (a + b));
DBMS_OUTPUT.put_line ('a-b=' (a - b));
DBMS_OUTPUT.put_line ('a*b=' (a * b));
DBMS_OUTPUT.put_line ('a/b=' (a / b));
END calculate;

And we get the intended output.

a+b=5
a-b=-5
a*b=0
a/b=0

5.Looping through VARRAY

Consider the following program which print the contents of a varray. For our test, create a new type

CREATE OR REPLACE TYPE varray_type AS VARRAY (10) OF VARCHAR2 (20);

Now create a database procedure which will accept a varray as parameter and display the contents.

CREATE OR REPLACE PROCEDURE print_varray (
my_varray IN varray_type
)
IS
BEGIN
FOR i IN my_varray.FIRST .. my_varray.LAST
LOOP
DBMS_OUTPUT.put_line (my_varray (i));
END LOOP;
END;

Now test our procedure

DECLARE
my_varray varray_type := varray_type ('Rahul',
'Ameer',
'Jay'
);
BEGIN
print_varray (my_varray);
END;

This will print the output in the dbms_output as expected.

Rahul
Ameer
Jay

All well and good. But if the varray is initialized dynamically and it happens to contain no elements.

DECLARE
my_varray varray_type := varray_type ();
BEGIN
print_varray (my_varray);
END;


I expect the program to complete without printing anything. However I get a PL/SQL numeric or value error. Well, when varray

is not initialized with any data, both my_varray.FIRST and my_varray.LAST becomes NULL which can't be a loop limiting variable
throwing the error. So NVL must be used around both of them.

CREATE OR REPLACE PROCEDURE print_varray (
my_varray IN varray_type
)
IS
BEGIN
FOR i IN NVL (my_varray.FIRST, 0) .. NVL (my_varray.LAST, -1)
LOOP
DBMS_OUTPUT.put_line (my_varray (i));
END LOOP;
END;

This solves this issue.

So the summary is that before all calculations and comparisons we must ensure that the variables used in the process are not NULL.