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!!!!

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); -- 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.

SQL Query to print Calendar of any month

Here is an SQL query to print Calendar in the traditional style. Always aligning to 5 rows.

select nvl(decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7,'DD'), null),decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+35,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+35,'DD'), null)) SUN ,

nvl(decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+1,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+1,'DD'), null),decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+36,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+36,'DD'), null)) MON ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+2,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+2,'DD'), null) TUE ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+3,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+3,'DD'), null) WED ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+4,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+4,'DD'), null) THU ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+5,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+5,'DD'), null) FRI,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+6,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+6,'DD'), null) SAT

from dual, (select to_date('01-&Month-&Year','DD-MON-YYYY') MY_DATE from dual)

connect by level<=5

What is Oracle Apps?

I have faced this question now and then ever since I started working on Oracle Applications. Mostly the questioner will be a friend, working in some other technologies who wants to develop an interface to Oracle Applications from their systems or an aspiring software professional who sees Oracle Apps as a career choice. Then I had to (over)simplify things and answer. Usually the conversation will be like this.

So you are working in Oracle A-p-p-s. What is that?

Oracle Applications or Apps is an ERP package.

I thought oracle was a database.

Oracle RDBMS is one of the most successful products from Oracle Corporation. Oracle Apps is a different product from the same company. As I said it is an ERP product.

E.R.P? I have heard the term somewhere.

Simply put, Enterprise Resource Planning is the software used by big companies (enterprises) for storage and manipulation of data.

I am also working in such a system which we developed in Java. But that is not called ERP.

They say there is a higher level of integration in ERP. The chief features of ERP are common database and functional modularity. For example, the employees from HR module will be considered as suppliers in purchasing module and used by payables module to pay their expenses.

This will be done in our system also. Anyways, if it is such a product how it can be used by many companies? Each customer’s requirements are unique. How can they use the same software?

Basically the operations of all the companies are same. They buy row materials, pay money for that, stock them, manufacture a product, sell them off and get money. They hire employees, give them salary, assess their performance and fire them. Isn’t that more or less same everywhere? And this "more or less" part is covered by customizations to this software.

My current client is a financial company. They collect, manipulate and sell financial data, they don't manufacture anything. So why in the world they have to pay for software which handles manufacturing part?

That is why Oracle provides this huge software as modules. There are over 150 modules. Each enterprise can buy only those modules which they need. Also they can use some module of oracle apps and other modules using legacy systems or other ERP packages. So your client can omit the manufacturing and inventory modules and buy the financial modules.

So this is a product of Oracle which will be installed in enterprises. You are not working with Oracle. Then what are you doing with Oracle Apps?


As you said each company's requirements are unique. So customizations are needed to the Apps software. So the enterprises buy the ERP package from Oracle and the companies like ours customize them. For example the client may need to track extra information for the employees which is not provided by Oracle or they don't want to enter certain information which the standard program will ask for. So they need front end and back end modifications. They may need custom reports. They may need to convert there existing data to the new system. They will need interfaces to receive data from and send data to legacy systems or modules of other packages.

These customizations can be pretty big. There can be up to 20% of customizations, over which is not recommended by experts. My current project has around 30 people working for 2 years to complete the customization.

So what are the tools you are using?

The back end is obviously Oracle RDBMS and so lot of logic is written in PL/SQL database packages. Reports are built using Oracle Reports. The front end is developed with Oracle Forms which is an event driven IDE like Visual Basic.

Like VB? Don't they need client side installations on each machine?

Not really. Oracle Forms can run through the browser. Only Jinitiator plug in needs to be installed on client browser which will get installed automatically on the first access of Oracle apps. Also in the newer versions of Apps a significant part of pages are developed using Oracle Applications Framework which uses Java. And these pages will be pure web pages.


So which is the latest version of Oracle Apps?

Oracle R12 was released in early 2007. Still many implementations are going on E-Business suite 11i (11.5.9 and 11.5.10). And we are expecting the release of Oracle Fusion in another couple of years which is expected to have drastic new features.

Which are the other ERP packages?

SAP is the champion of ERP currently with the largest market share. Then there are PeopleSoft, J.D. Edwards, Baan etc. In last few years Oracle has acquired a number of these companies including PeopleSoft and, J.D. Edwards. So we are expecting the good features of these packages to be integrated into the Fusion release.

Cool. I think I have the basic idea now. But will be back with more questions.

Always welcome.

Script to register Table and Columns in Oracle Applications

Last day I was trying to register a flex field on a custom table. I could not find my custom table in the Tables LOV. Then it occurred to me that I have to register my custom table for it to be available in any LOV in apps. So I navigated to the Table Registration screen to find it read only .

After a bit of browsing I found that I had to use an API for the purpose. So I used ad_dd.register_table API to do the same. That was not the end and now I had to register each of my 40 plus columns one by executing ad_dd.register_column API. I knew there should be an easier way. I googled but could not find the code to register the table as well as columns in one short.

Finally I wrote the script myself.

CREATE OR REPLACE PROCEDURE register_table (
table_name VARCHAR2,
application_short_name VARCHAR2
)
AS
status VARCHAR2 (10);

CURSOR c_columns ( p_table_name all_tab_columns.table_name%TYPE )
IS
SELECT column_name,
data_type,
data_length,
nullable,
ROWNUM,
data_precision,
data_scale
FROM all_tab_columns
WHERE table_name = p_table_name;

CURSOR c_constraints (p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE )
IS
SELECT constraint_name,
table_name,
status
FROM all_constraints
WHERE table_name = p_table_name AND owner = p_application_short_name AND constraint_type = 'P';

CURSOR c_constraint_columns (
p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE
)
IS
SELECT acc.constraint_name,
acc.column_name,
acc.POSITION
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.table_name = p_table_name
AND ac.constraint_type = 'P'
AND ac.owner = p_application_short_name;

BEGIN

DBMS_OUTPUT.put_line ('Registering Table '|| table_name ||'in application ' || application_short_name);

ad_dd.register_table (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_tab_type => 'T'
);

FOR r_columns IN c_columns (table_name)
LOOP

DBMS_OUTPUT.put_line ('Registering Column '|| r_columns.column_name);

ad_dd.register_column (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_col_name => r_columns.column_name,
p_col_seq => r_columns.ROWNUM,
p_col_type => r_columns.data_type,
p_col_width => r_columns.data_length,
p_nullable => r_columns.nullable,
p_translate => 'N',
p_precision => r_columns.data_precision,
p_scale => r_columns.data_scale
);

END LOOP;

FOR r_constraints IN c_constraints (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ('Creating Primary Key Constraint ' || r_constraints.constraint_name);

SELECT DECODE (r_constraints.status,
'ENABLED', 'Y',
'N'
)
INTO status
FROM DUAL;

ad_dd.register_primary_key (p_appl_short_name => application_short_name,
p_key_name => r_constraints.constraint_name,
p_tab_name => table_name,
p_description => 'Primary Key for Table '|| table_name,
p_key_type => 'D',
p_audit_flag => 'N',
p_enabled_flag => status
);
END LOOP;

FOR r_constraint_columns IN c_constraint_columns (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ( 'Registering Primary Key Column '
r_constraint_columns.column_name
' for Constraint '
r_constraint_columns.constraint_name);

ad_dd.register_primary_key_column (p_appl_short_name => application_short_name,
p_key_name => r_constraint_columns.constraint_name,
p_tab_name => table_name,
p_col_name => r_constraint_columns.column_name,
p_col_sequence => r_constraint_columns.POSITION
);
END LOOP;

END register_table;

The procedure will register the table, all the columns of the table and the primary key in Apps. It can be executed as

begin
register_table('XXAP_TEST_TABLE','SQLAP');
end;

Now that the script is in place, I could register the table and columns in single shot and I could get the table and columns in the LOVs.