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.

Why Password field in Oracle Applications Users Screen is disabled?

Our Oracle Apps development instance was refreshed from UAT a week ago. After the refresh we were given a common login with System Administrator responsibility. Each one of us was supposed to reset their password using the common login. As usual I navigated to the Users screen to reset my password and encountered the below screen.



Anything unusual?

Any way I find something in the very field I was trying to edit. The password field was disabled. I can’t reset my password. Frustrated !!!!!!!

Now I can ask my DBA and he will do some magic to enable this field in seconds? But being a curious technical consultant with a bit of spare time in hand, I decided to find it myself. After opening the fmb and subsequently going through two to three stored programs, I got it. The culprit is a profile option named Applications SSO Login Types.

I navigated to Systems Profile Values screen (Profile --> Systems) and found the value for this for the user I wanted to change the password.



The possible values for this profile option are

i) SSO
ii) Local and
iii) Both

Now I got it. Single Sign On was implemented in the system where in password is maintained in a central database and shared by different applications.

As you can see the value is SSO which means the password field will be disabled for that user for whatever login you use because the password is not stored in the local Oracle Apps database. I changed the profile value to Local and my password field got enabled.

Back to work!!!!

Oracle Apps Diary: Find the SQL behind List of Values(LOV) of an Ebusiness Suite screen

SQL Query that returns all days of a month

Early (?) morning today I received a call from a friend working in Mumbai as a PL/SQL developer. He was working overnight developing an SQL report and is stuck with an issue.

The requirement is to sum up values of a table based on date. The table had two relevent columns a date and an amount. Each date will have several entries. So the following select should do it.

select my_date, sum(amount)
from table1
where (month = current month)
group by my_date

The output will be

month_dates      Amount
-----------      ------
02-JUN-2009       1000
05-JUN-2009       4450  
...........
...........
...........
30-JUN-2009       2341 

You can see that 01-JUN-2009 , 03-JUN-2009 and 04-JUN-2009 is missing. This is because there are no corresponding entries in the table for these dates.However the requirement is that report output should print all the dates of a month irrespective of whether there is any entry in table1 or not. In the case with no entries amount should be printed as 0. So the ideal output will be

month_dates        Amount
-----------        ------
01-JUN-2009             0
02-JUN-2009          1000
03-JUN-2009             0 
04-JUN-2009             0
05-JUN-2009          4450 
...........
...........
...........
30-JUN-2009          2341 

So what he required is a second select statement which will return all the dates of a month as given below

month_dates
-----------

01-JUN-2009
02-JUN-2009
...........
...........
...........
30-JUN-2009

Then he can use this as an inline select statement as given below.

select all_days.my_date, sum(table1.amount)
from table1, (select_statement_2) all_days
where table1,my_date(+)=all_days.my_date
group by all_days.my_date

The output will be as expected

month_dates        Amount
-----------        ------
01-JUN-2009             0
02-JUN-2009          1000
03-JUN-2009             0  
...........
...........
...........
30-JUN-2009          2341 

So now the task is to write the select_statement_2 which will have as many rows as the number of days in the month and with one column which will have the dates in ascending order from the first day to last_day. So its time to play around with date functions again

For simplicity let us assume that we have to take the current month only.

To start with, I tried

SELECT TRUNC (SYSDATE, 'MON') month_dates
FROM DUAL

And this got me the first day of the month as following

month_dates
-----------

01-JUN-2009


Now I have to get a maximum of 31 rows which is the maximum number of days in a month. For the purpose I used a hierarchical query.


SELECT TRUNC (SYSDATE, 'MON')
FROM DUAL
CONNECT BY LEVEL <= 31

This gave me 31 rows with same date


month_dates
-----------

01-JUN-2009
01-JUN-2009
01-JUN-2009
...........
...........
01-JUN-2009


Now I incremented the date based on the rownum pseudo column


SELECT TRUNC (SYSDATE, 'MON') +rownum-1
FROM DUAL
CONNECT BY LEVEL <= 31

So the output now is

month_dates
-----------

01-JUN-2009
02-JUN-2009
...........
...........
...........
30-JUN-2009
01-JUL-2009

We are almost there, but the last row 01-JUL-2009 is the problem. The number of days in different months is different. It can be 28,29,30 or 31. So to correct this issue, I added an additional condition in the where clause which will ensure that the date printed will always belong to the current month.


SELECT TRUNC (SYSDATE, 'MON')+rownum-1
FROM DUAL
where to_char(SYSDATE,'MON')=to_char(TRUNC (SYSDATE, 'MON')+rownum-1,'MON')
CONNECT BY LEVEL <= 31

Now the last row is gone

month_dates
-----------

01-JUN-2009
02-JUN-2009
...........
...........
...........
30-JUN-2009

Now If I replace SYSDATE with any other date, all days of that month will be printed. To generalize, the following select statement will take a month and year as input and will query all dates of that month

SELECT TO_DATE ('01-' || '&Month' || '-' || '&year', 'DD-MON-YYYY')
+ LEVEL
- 1
FROM DUAL
CONNECT BY LEVEL <= 31
AND TO_CHAR ( TO_DATE ('01-' || '&Month' || '-' || '&year',
'DD-MON-YYYY')
+ LEVEL
- 1,
'MON') = '&Month'

For an input of Month-'FEB' and year '2008' the output will be

month_dates
-----------

01-02-2008
02-02-2008
..........
..........
29-02-2008

As I write this post my friend called me again to say that its working and I am a happy man.

Searching for the navigation of a form in Oracle Application

Many times it happened to me that I know that there is some custom or standard form out there in some navigation under some responsibility of Oracle Applications. There is no direct way of finding this from front end or through a simple back end query. I was working with a new project where there were a lot of custom Menu entries which I did not know the navigation. So finally I wrote the following query.





SELECT LPAD ('*', (LEVEL - 1) * 2, '*') || prompt NAVIGATION,

DECODE (menu_type,'R','Responsibility',NULL) Type

FROM (SELECT prompt,

LEVEL menu_level,

TYPE menu_type,

menu_id,

sub_menu_id

FROM ( SELECT prompt,

menu_id,

sub_menu_id,

function_id,

'M' TYPE

FROM fnd_menu_entries_vl

WHERE prompt IS NOT NULL

UNION

SELECT responsibility_name,

0,

menu_id,

-1,

'R'

FROM fnd_responsibility_vl

WHERE NVL (end_date, SYSDATE + 1) > SYSDATE) fme

START WITH UPPER (prompt) LIKE Upper('&Menu_Name')

CONNECT BY PRIOR menu_id = sub_menu_id)

START WITH menu_type = 'R'

CONNECT BY PRIOR sub_menu_id = menu_id




Just give the approximate name for the menu item as input parameter and the query will output all available navigations to this menu item.