Open Document Link - Oracle Purchasing

Scope:

Oracle Purchasing - Version: 11.5.10 to 11.5.10.4

Issue:

On performing the following steps:

- Create a purchase order or purchase requisition that is over the requestors' approval limit
- Submit the document for approval.
- Approver logs into i-Procurement or Oracle Purchasing
- Selects on notifications options to look at all approval notifications in queue.
- The approver tries to open the document via the "Open Document" link.

The page takes forever to process that request and nothing happens (The document does not open in the expected Oracle Form)

We faced this issue while upgrading to 11.5.10.2 from 11.5.10. And it recurred while we moved our single node instance to two nodes, by separating the Web and Forms node.

Cause:

The forms appears to 'hang' because the link is executing in a hidden iframe, so you never see the error that is actually occuring. The actual error that is occurring behind the scenes is:

Oracle Forms Web CGI: Error detected
Your request cannot be serviced at this time due to the following error:

Error:
URL Contains One or More Restricted Characters
This error is occurring because some characters are incorrectly being blocked.

Solution:

There is a patch for the issue (5447522) but we went for the workaround which is to comment out the variable FORMS60_BLOCK_URL_CHARACTERS in apps.conf and bounce the Apache.

BneInvalidException - Server TimeZone

Scope:

Oracle Applications 11.5.10.2

Issue:

Users are facing the following error while uploading data through Web ADIs:



Analysis:

The bne.log shows:

BneUserLocaleUtils.getTimeZone CLIENT_TIMEZONE_ID has not been set

Solution:

The profile options ‘Server Timezone’ and ‘Client Timezone’ are incorrectly set.

Note: In our case, we only needed to set ‘Server Timezone’

Poor Discoverer Performance

During the very start of my career, I used to be very intimated by the oh-so-famous Discoverer… Though the reason for this phobia always slipped my mind! But then, there are instances like the one I would be discussing here, which might be passed as a substantial reason!

Scope:

Oracle Discoverer - Version: 10.1.2.2

Issue:

On one of our production systems, we upgraded Discoverer 4 to 10, which seemed like a good move till the users started mailing like crazy that the reports that took 45 mins, now were taking 6 hrs!!

Analysis:

On checking the SQL being used by the Discoverer, we noticed that it was appending the hint /*+ NOREWRITE */ to every query. Also, when we executed the queries without this hint, through a client like say TOAD, the performance improved.

So we came to the conclusion that, Discoverer quite conveniently was not using the CBO(though all our tables were analyzed).

Solution:

We made the following changes to pref.txt:

1. UseOptimizerHints = 1 – Which was initially 0
2. UseNoRewriteHint = 0 – Which was initially not included(by default it was picking 1)

After editing the above file, applypreferences.sh needs to be run for the changes to take effect. Bouncing the Discoverer server is not necessary. Also on adding the above, Discoverer started using the ALL ROWS hint and stopped using the NOREWRITE hint.

Though, Oracle strongly recommends quite contrary to the changes we made, it still did work really well for us!! The only catch here is that the concerned tables have to be regularly analyzed or the performance would degrade manifold. Hope that helps!