SQL to find all the Responsibilities to which a Concurrent Program is attached

SELECT resp_tl.responsibility_name,
rg.request_group_name
FROM fnd_concurrent_programs_tl prog_tl, fnd_concurrent_programs prog,
fnd_request_group_units unit,
fnd_request_groups rg,
fnd_responsibility resp,
fnd_responsibility_tl resp_tl
WHERE prog_tl.user_concurrent_program_name = '&Program_Name' AND unit.request_unit_id = prog.concurrent_program_id AND prog_tl.concurrent_program_id = prog.concurrent_program_id AND resp.request_group_id = unit.request_group_id AND resp.request_group_id = rg.request_group_id AND resp_tl.responsibility_id = resp.responsibility_id ORDER BY 2,1;

Basic logging Procedure in PL/SQL

As an Oracle Applications Technical Consultant, I spent a lot of time analyzing code to understand why something has happened or something did not happen inside big PL/SQL procedures written by some other developers. I get queries from functional consultants to explain them why that program did not update a particular record or why was the error message displayed on the screen. The answer will be either you did it in the wrong way or there is a bug in the code.

To arrive at this conclusion, I need to trace the flow of the code by adding logging messages at all possible points and executing the program again. Log messages can be written to concurrent program log file, a log file in the server or a log table in the database. I prefer to use table because it can be used in all the cases and it is easy to retrieve the messages using SQL.

Here is a simple but effective script that every PL/SQL developer should have in his kitty. Feel free to use it if you have not written this yet.

First we need to create a database table to store the log
messages. Here is the script to create the table

CREATE TABLE XX_LOG
(CREATION_DATE DATE,
MESSAGE VARCHAR2(4000),
SEQ NUMBER(10))
/

Now create the sequence to generate value for the column SEQ_NUMBER

CREATE SEQUENCE XX_LOG_SEQ START WITH 1 INCREMENT BY 1

Finally, create a procedure which will insert into the log table. This procedure is made an AUTONOMOUS_TRANSACTION so that it is COMMITed independent of the COMMIT or ROLLBACK of the calling function.

CREATE OR REPLACE PROCEDURE xxinsert_log(p_message in varchar2) AS
pragma autonomous_transaction;
BEGIN
INSERT INTO xx_log
VALUES (sysdate,SUBSTR(p_message,1,4000),xx_log_seq.nextval);
COMMIT;
END xxinsert_log;

Now calls to this procedure can be simply inserted to the programs you want to debug.

CREATE OR REPLACE PROCEDURE my_buggy_procedure (parameter1 in number) IS
BEGIN
xxinsert_log('Start of procedure. PArameter1 = 'parameter1);
..................................
..................................
xxinsert_log('Middle of procedure');
..................................
..................................
xxinsert_log('End of Procedure');
EXCEPTION
WHEN others THEN
…………………….
xxinsert_log('Exception of procedure 'sqlerrm);
END my_buggy_procedure;

After executing the procedure the messages can be retrieved and analyzed using simple SQL.

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.

Search Oracle Database for data using PL/SQL

I know that a piece of data is present somewhere in the database and most often I don't know where is it exactly located in the jungle of tables. The following are such situations.

i) A string is appearing in an Oracle Forms screen, I want to know which table it is stored. And if it is a non-database item Oracle diagnostics does not provide much help.

ii)I have an interface concurrent program which takes a flat file data and loads in certain tables. I want to know which table a particular information is stored.

I used to read the code or simply guess the table name and search in ALL_TABLES and then query in the possible tables. Being too lazy to do that, I wrote the following PL/SQL procedure which will do the task for me. The inputs to the program are

i) Search_string :- The data you want to search for
ii) Data_type :- The type of the data. NUMBER,VARCHAR2 and DATE are the possible options

The output of the program will be the names of the tables and columns where the given data is present. Remember to turn on the output. (SET SERVEROUTPUT ON)

DECLARE

  CURSOR c_column IS

    SELECT table_name

          ,column_name

          ,data_type

    FROM   all_tab_columns atc

    WHERE  1 = 1

    AND    data_type = UPPER('&Data_type') --the possible options are NUMBER, VARCHAR2 and DATE. You won't get any result if some other value is entered

          --   and owner in ('')   -- Add the names of the possible schemas where you expect to find the data. If you comment this line, it may take a lot of time for the search

          -- and table_name like upper('%') --Use this condition if you have any clues on the table name. (Eg :- Table name contains word 'INVOICE'). This will improve the efficiency

          -- and column_name like upper('%')--Use this condition if you have any clues on the column name. (Eg :- Column name contains word 'DATE'). This will improve the efficiency

    AND    EXISTS (SELECT 1 FROM all_tables at WHERE at.table_name = atc.table_name

    and atc.TABLE_NAME like 'FND%'); -- This condition ensures that only tables are searched, not the views

  v_search_string VARCHAR2(100) := '&Search_string';

  v_count         NUMBER;

  v_sql_stmt      VARCHAR2(2000);

  e_table_col_not_found EXCEPTION;

  e_invalid_data_type   EXCEPTION;

  PRAGMA EXCEPTION_INIT(e_table_col_not_found

                       ,-942);

BEGIN

  dbms_output.put_line('Searching for ' || v_search_string);

  FOR r_column IN c_column

  LOOP

    BEGIN

      IF r_column.data_type = 'VARCHAR2'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || ' like ''' || v_search_string || '''';

      ELSIF r_column.data_type = 'NUMBER'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || '= to_number(''' || v_search_string || ''')';

      ELSIF r_column.data_type = 'DATE'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || '= to_date(''' || v_search_string ||

                      ''',''DD-MON-YYYY'')';

      END IF;

      EXECUTE IMMEDIATE v_sql_stmt

        INTO v_count;

      IF (v_count > 0)

      THEN

        dbms_output.put_line('Found in Table ' || r_column.table_name || ' Column :-  ' || r_column.column_name);

      END IF;

    EXCEPTION

      WHEN e_table_col_not_found THEN

        dbms_output.put_line('Error. Skipping Column ' || r_column.column_name || ' of table ' || r_column.table_name);

    END;

  END LOOP;

  dbms_output.put_line('Search Over');

END;

The program contains a cursor which will loop through all the tables and columns present in the database. For each of the table and column, the program will form a dynamic SQL query to see whether the data is present in the particular column.

The program is awfully slow if it searches the entire Oracle Applications database, so it will be better to narrow the search by providing extra conditions in the main cursor. The possible conditions are

i) OWNER IN ('ABC', 'XYZ') :- If you have any clues on the module where you expect the data to be present use the schema(s) corresponding to the module. If you don't have any idea, at least you can avoid certain schema using something like OWNER NOT IN ('SYS')

ii) TABLE_NAME LIKE UPPER('%ABC%') :- If you can guess the probable strings in table name use this condition

iii)COLUMN_NAME LIKE UPPER('%ABC%') :- If you can guess the probable strings in column name use this condition

The procedure has been very much helpful for me when used appropriately.