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 ..".

Sagar Alias Jacky

I learned from my sister today that Mohanlals' much awaited Sagar Alias Jacky will be released this Thursday. Last time I was this eager and excited for a movie was for Rajnis' Sivaji. Hype and expectations usually doesn't bodes well for a movie. But Sivaji was an exception and I hope the same for SAJ. I came to know about SAJ when I saw the huge poster near Srikumar theatre in Trivandrum last December. Then I thought SAJ would a

Retake

An embellished modern retake of Iruthapatham Nutandu; in the same line as Don and Billa. See, no one would have complained such a venture, obvious I may add, because Irupataham Nutandu was easily the best candidate for such a much over-due remake from Malayalam stream.

Return/Revenge

A sequel with maybe Sekharan Kuttis' illegal son seeking revenge, more corrupt and cunning political characters, Lal surrounded with dozen obsequious croonies, winding dialogues while camera rotate around Lal. Boy! it would have been a train wreck which might even make Chenkol a better sequel.

Reloaded

Something like main characters are carried over with a new backdrop and supporting casts. Indian audience seems to have stomach for such super hero flicks as they made Lage Raho a bigger hit than MBBS.

And the promoters are not leaving any stones unturned to make it point that it is indeed reloaded. Yes Sagar Alias Jacky - Reloaded.

My expectations from the movie are

• Intro of Mohanlal. It must pull even the most skeptical of Lal fan out of his seat and scream. It should set the tune for the rest of movie and catapult me (an imbecile) to the new world of J.A.C.K.Y and stay me put there for the rest of movie.

• Indelible and goose pimple moments. If you will, I must add like the one from Irupatham Nootandu

• In the lift with Ambika were Lal non chalantly delivers the dialogue "Sagar alias Jacky" and walks away with signature background music.

• In customs' uniform after busting Sekharankuttys' cheap trick. "Eda nayinte moneay ...Sekharan kutty nee onnu orapicho nee ee nasraniyeye kanda divasam muthal ninte andhyam arambichu kazhinju ..now you start your count down COUNT DOWN





• Bust up with police after parking car in no parking zone (in front of AGs' office).

• Mario Puzzo style twists and a better climax. I was impressed with turn of events in the last scene of Big B and no doubt Neerad is from the staple of RGB.

I am planning to travel to Dallas to watch the movie first week. Fingers crossed. Hoping to be back with a rave review.

Watch full movie: Irupatham Nutandu.

Trailers:

Printing Word doc FND attachments

We recently came across an unique requirement of printing the Word document attachments along with a report. I googled and metalinked. But found no direct solution and there were few open Enhancement Requests open with Oracle. We finally found a way. The idea is very simple. Get the attachments BLOB stored in database to apps server using JDBC and print the file using soffice. But lotsa work must be done and most of them need an APPS DBA's help. This article outlines the steps but not in detail. You should spend some time based on your apps configuration to get it work.

Technical Overview of FND attachments.

All file attachments are stored in FND_LOBS table in file_data column. You can link this back to your attachment entity using the following query.

SELECT fl.file_data,
fdt.media_id,
fad.entity_name,
fdt.description
FROM fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_lobs fl
WHERE fad.document_id = fdt.document_id
AND fl.file_id = fdt.media_id
AND fdt.language = userenv('Lang')
AND fad.entity_name = 'MTL_SYSTEM_ITEMS'
AND fad.pk1_value = 183
AND fad.pk2_value = 164217


Here we are getting the attachment details of Discrete Item Master (Entity_Name: MTL_SYSTEM_ITEMS) and primary key values PK1_VALUE(Organization_ID) and PK2_VALUE (Inventory_Item_id).

For your entity you can get the details by navigating to Responsibility: Application Developer and Menu Navigaton: Attachments > Document Entity and Attachments > Attachment Functions.

Steps to print the attachments

Step 1: Java program to bring attachment BLOB to apps tier. Link.
This program gets media id and brings the BLOB from fnd_lob to apps tier.

Parameters : 1:User name 2:Password 3:Host 4:Port 5:SID 6:MediaId 7:FileSuffix

Step 2: Download Open Office and configure it in apps tier. I used my Apps DBA's help for it and it is a linux expert job.

Vaguely we have to do the following.
a.Install open office on the server
b.Log in as the oraowner, start a X session and launch open office writer
c.From root run the following: (This will need to be run after reboot or added as a service)
$ unset DISPLAY
$ soffice –headless –accept="socket,host=127.0.0.1,port=8100;urp;" –nofirststarwizard


Step 3: Create a simple shell script to use Java program in Step 1 and Open Office in Step 2 to print the file.

#!/bin/ksh
# $Header: XXAttachmentPrint.ksh$
#
# Purpose:
# Copy a file from the db to a temp location and print it.
#
# $5 MediaId
# $6 FileName
# $7 PrinterName
#
# Set Application Environment Variables
export PATH=${PATH}:/usr/local/bin
export XXENVPREF=`echo ${TWO_TASK}|awk ' { print tolower($1) } '`

# intialize the apps context in linux. You must replace this with your logic for executing APPSORA.env from $APPL_TOP
. XXsetenv${XXENVPREF}.ksh
. XXSetAppsEnv.ksh


export MEDIAID=${5}
echo "MediaId=" ${MEDIAID}
export FILENAME=${6}
echo "FileName=" ${FILENAME}
export PRINTERNAME=${7}
echo "PrinterName=" ${PRINTERNAME}

# Change Directory to temp location
cd ${COMMON_TOP}/temp

# Execute the java code to extract file. You can read db user name, passwd host etc from concurrent program params 1-4 or read it from another
# file/ exported variables in instance.
java tempel.oracle.apps.xxtmg.crm.AttachmentReader apps `cat ~/APPSpswd${XXENVPREF}.txt` ${XXDBHOST} ${XXDBPORT} ${XXENV} ${MEDIAID} ${FILENAME}.doc

# Open Office print command
soffice -headless -pt ${PRINTERNAME} ${FILENAME}.doc

# Clean up extracted document
rm ${FILENAME}.doc

#
# End of File



Step 4: Seed the above shell script as a concurrent program in APPS.

Application Developer > Concurrent > Programs/Executable

Execution file name should be same as the script name in Step 3.

Step 5: Write a simple PLSQL program which based on entity name and PK value launches the Step 4 program by passing parameters for media id, printer and file name.
PROCEDURE print_mtl_attachments(p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER) IS

CURSOR c IS
SELECT fdt.media_id
FROM fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_lobs fl
HERE fad.document_id = fdt.document_id
AND fl.file_id = fdt.media_id
AND fdt.language = userenv('Lang')
AND fad.entity_name = 'MTL_SYSTEM_ITEMS'
AND fad.pk1_value = p_organization_id
AND fad.pk2_value = p_inventory_item_id;

l_request_id NUMBER;
BEGIN

FOR c_rec IN c LOOP
x_request_id :=
fnd_request.submit_request (<app short name>,
<prog short name from step 4>,
<some desc>,
TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
FALSE,
c_rec.media_id,
'F' || c_rec._media_id || '-' || to_char(SYSDATE,'MMDDYYYYHH24MISS', -- unique file name,
<printer name>
);
END LOOP;

END print_mtl_attachments;

Step 6: Seed another concurrent program for the PLSQL in step 5.

Step 7: Add concurrent program in Step 6 to a request set containing your report.

When To Fuse

Oracle, by announcing Application Unlimited are suggesting customers to take an evolutionary path to Oracle Fusion applications. In layman terms, they are asking Customers to take their own time to move to Fusion Apps. Which certainly has failed to enthuse most of the customers and most of them will hesitate to adopt the Apps in the foreseeable future. There is a hope that Larry Ellison might pull a Steve Jobs in terms of time to market and convincing customers to embrace the new technology. But ERP is not a fancy utility device and they might even miss the oppurtunity. As far as I (Oracle Apps Technical Consultant), am concerned, the big buck question is when is the right time to start learning Fusion? Or with my current skill sets (read Apps 11i and OA Framework) how fast I will get out dated?

One thing is for certain. Fusion is for real. I met few of my old colleagues at Oracle who are currently part of Fusion Apps development. And based on it, insider information or rumor or whatever you want to call, but certianly from reliable resources

• Coding is almost over and it might hit the market (may be to privileged few) around October 2009.
• It is toned down version with minimum features.
• It is developed almost from scratch. And not using the database design of EBS 11i or 12.
  
• Lots of RAD and less coding.
• They faced huge number of issues with Fusion Tech stack and believes it is still evolving and might take some releases to stabilize.
• Entirey different from OAF. There are some similarities.
• Dont worry about learning Fusion tecnology now.
  

Oracle is also recommending most Customers/Oracle Apps Consulting Organizations to start learning Fustion middleware and related technologies like SOA, BPEL, BIEE etc. And also promises that future releases of EBS will be using these techologies.

I will try to make a very silly guess how long OAF will last and when we should start embracing the Fusion technologies?

Oracle EBS Timeline

1987 - Oracle started its application division
1993 - First client server application release
1997 - Oracle announces native Java support and promises new set of RAD tools in Java. Genesis of Jdeveloper.
1998 - Oracle announces EBS 10.7 NCA
1999 - Releases Jdeveloper 2.0
EBS - Releases 11.0.3
2000 - First release of EBS 11i (11i1 and 11i2)
First release of Oracle 9i Application server
2001 - Release 11i3, 11i4 and 11i5
Release of Oracle 9i JDeveloper
2002 - Release of 11i6, 11i7 and 11i8.
Used JTF-the mother of OAF*
2003 - Release of 11i9
Started using OAF in its products.*
2004 - Release of 11i10
Maybe first stable and ironically last release of OAF as after 11.5.10 Oracle internally stopped providing enhancements or additions to OAF.*
2005 - Announces Project Fusion after acquiring host of companies.
2007 - Release of 12
2008 - Release of Fusion middle ware
Announcement of Application Unlimited and Evolutionary path to Fusion.


Timeline reference:

http://www.oracle.com/oraclemagazine/20/o20timeline.html

http://www.oracle.com/support/library/brochure/lifetime-support-applications.pdf

http://apps2fusion.com/apps/oa-framework/197-brief-history-of-oa-framework-and-future

* No references available.


Some observations based on the above timelines.
- It took seven years (97-04) for OAF or to be precise web based ERP application technology stack to become stable internally. OAF started percolating to outside consultancy world only after 11i10.
- It took six years and seven major releases for Oracle to release a relatively stable first application (11i7).

And my silly guess.

2009 - First cut privileged release of Fusion Apps.
2010 - EBS new release with more features from Fusion middleware.
2011 - First market release of Fusion Apps. Stable Fusion middleware.
2012 - Migration path to EBS to Fusion Apps clear.
2013/14 - Few more release of EBS slowly migrating towards Fusion and few more Fusion Apps releases.
2015 - No more new EBS releases and customers slowly start embracing Fusion Apps releases.

So,

- We got another two years for first stable release of Fusion Apps
- We got three years before EBS start looking like Fusion Apps.
- We got a good six year before Oracle might stop making EBS releases or OAF is completely out of the scene.

And a big question mark looms around the success of Fusion Middleware.

I don't want to make any conclusions and leave the readers to make chocies.