Oracle Observations

November 18, 2012

11g forms error

Filed under: 11g,Oracle forms — bigdaveroberts @ 1:21 pm

After migrating to 11g we encountered the following error in 2 of our forms after migration to 11g:

Error 801 at line 0, line 0
internal error [phd_get_defn:DI_U_NAM_RHS]
Error 908 at line 0, column 0
The stored format of <schema name>.<Procedure name> is not supported by this release.

I believe that the issue related to compiling in form code that called database packages that had dependencies in other databases.

But that isn’t important, there was one hit on the OTN forum that correctly pointed me at the correct Metalink article and patch:

So the problem is described in article 1058803.1 and resolved by 7708340.

So ultimately the problem doesn’t seem to require a blog post.

There was however one anomaly  the patch is a database patch and the read-me implied that the patch needed to be applied to the Weblogic server also the patch was relatively speaking large.

I have to admit I was highly dubious about applying a database patch to the middle tier so I went ahead and applied the patch to my development PC, and to my great surprise, it worked.

It also appears that technically you don’t need to apply it to the middle tier, it simply needs to be applied to the PC on which the forms are compiled, although obviously it would be good practice to keep the development PCs and server environments in sync with the live environment.
So sometimes, however unlikely it sounds, Oracle instructions are sometimes accurate straight out of the tin!

November 17, 2012

Oracle forms 11g, OLE2, Windows 2008 R2 64bit issue. (Possibly.)

Filed under: 11g,OLE,Oracle forms,Uncategorized — bigdaveroberts @ 10:12 pm

For clarity, its definitely a forms issue and It’s definitely an OLE2 issue.

But the rest is speculative.

The application was developed to produce reports as Excel spreadsheets using OLE2 to to populate the cells with data.

This was originally developed under Forms 6i and was successfully migrated to Forms 10g using the middle tier to generate the spreadsheets and then pass it back to the user using the same mechanism (WEB.SHOW_DOCUMENT) as used to pass oracle reports back.

Upon migration to Forms 11g this implementation still worked using 32bit windows server 2003 for the middle tier, but when we moved to 64bit Windows Server 2008 R2 the process failed.

We could see Excel being invoked, but it ran only briefly and then terminated without generating a spreadsheet or logging any form of error that we could find.

I tested using my development environment under windows 7 64 bit and the functionality worked fine.

So, it was back to Sysinternals process monitor ( to try and work out the difference in behavior.

My expectation was that it would be some obscure registry setting that would be the cause, in fact the behavior diverged after an attempt to read the following directory:


Strangely this directory was also missing on my windows 7 PC but it was present on windows server 2003.

I copied the desktop directory and its contents from the windows 2003 server to the windows 2008 server and the problem disappeared.

I then deleted the contents of the desktop directory and the Excel spreadsheet was still successfully generated.

So in conclusion, I guess, that when Oracle implemented it’s forms OLE2 interface, they based it on configuration information that is no longer present on 64 bit Windows Server 2008 R2.

However I am uncertain as to when that directory disappeared.

There is also the issue that on my development machine the problem didn’t manifest itself, indicating that the problem may be more complex than this initial investigation has exposed.

As a last observation, the closest I’ve come to an official document that hints at being related on Metalink is:


Although there is no explicit reference to OLE2 and the bug appears unresolved.

Not quite the conclusive conclusion that I like, but interesting enough for a blog post.


April 17, 2012

Oracle forms application slow when opening new forms with menu role security enabled.

Filed under: 8i,Oracle forms,Performance — bigdaveroberts @ 9:07 pm

I encountered this problem several years ago on an old Oracle 8i/Forms 6i system. After increasing the number of application users from about 250 to 280, the application had become unacceptably slow opening new forms, taking about 5 seconds to open each new form. When a large number of LOVs were actually implemented as new forms this was unacceptable.

I had recently been learning how to interpret statspack reports and I attempted to drill down from the top 5 waits to a suspect Query. Ultimately having identified the problem wait and the suspect hot block I proposed a solution.

Possibly with more experience, I’d have spotted the fact that in the section for most expensive SQL (in terms of buffer gets) one apparently simple SQL statement generated more than 70% of buffer gets:

-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

  Buffer Gets    Executions  Gets per Exec  % Total  Hash Value
--------------- ------------ -------------- ------- ------------
     99,955,919          593      168,559.7    71.5   2686798499
r by ROLE desc

The question is, why should such a simple query be so resource intensive?

Part of the answer to that question is that the query wasn’t as simple as it appeared in the statspack report. The FRM50_ENABLED_ROLES view is a view installed to support menu security in oracle forms and it is a view created by the script frmsec.sql  and based on other pre-existing views:

create or replace view FRM50_ENABLED_ROLES as
select urp.granted_role role,
sum(distinct decode(rrp.granted_role,
   'ORAFORMS$DBG',1,0)) flag
from  sys.user_role_privs urp, role_role_privs rrp
where urp.granted_role = rrp.role (+)
  and urp.granted_role not like 'ORAFORMS$%'
group by urp.granted_role;

If you analyse the query it becomes evident that the query isn’t as simple as first anticipated.

Execution Plan
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=31373358 Card=2157584180 Bytes=64727525400)
1    0   SORT (ORDER BY) (Cost=31373358 Card=2157584180 Bytes=64727525400)
2    1     VIEW OF 'FRM50_ENABLED_ROLES' (Cost=7471144 Card=2157584180 Bytes=64727525400)
3    2       SORT (GROUP BY) (Cost=7471144 Card=2157584180 Bytes=110036793180)
4    3         MERGE JOIN (OUTER) (Cost=7471144 Card=2157584180 Bytes=110036793180)
5    4           SORT (JOIN) (Cost=61 Card=1010 Bytes=17170)
6    5             VIEW OF 'USER_ROLE_PRIVS' (Cost=53 Card=1010 Bytes=17170)
7    6               SORT (UNIQUE) (Cost=53 Card=1010 Bytes=102161)
8    7                 UNION-ALL
9    8                   SORT (GROUP BY) (Cost=38 Card=601 Bytes=82938)
10    9                     NESTED LOOPS (OUTER) (Cost=6 Card=601 Bytes=82938)
11   10                       NESTED LOOPS (Cost=6 Card=601 Bytes=67312)
12   11                         NESTED LOOPS (Cost=6 Card=147 Bytes=12054)
13   12                           INLIST ITERATOR
14   13                             TABLE ACCESS (BY INDEX ROWID) OF 'SYSAUTH$' (Cost=6 Card=179 Bytes=6981)
15   14                               INDEX (RANGE SCAN) OF 'I_SYSAUTH1' (UNIQUE) (Cost=2 Card=179)
16   12                           INLIST ITERATOR
17   16                             TABLE ACCESS (CLUSTER) OF 'USER$'
18   17                               INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
19   11                         TABLE ACCESS (CLUSTER) OF 'USER$'
20   19                           INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
21   10                       INDEX (UNIQUE SCAN) OF 'I_DEFROLE1' (UNIQUE)
22    8                   NESTED LOOPS (Cost=7 Card=409 Bytes=19223)
23   22                     FIXED TABLE (FULL) OF 'X$KZDOS' (Cost=7 Card=100 Bytes=1700)
24   22                     TABLE ACCESS (CLUSTER) OF 'USER$'
25   24                       INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
26    4           SORT (JOIN) (Cost=7471083 Card=213622196 Bytes=7263154664)
27   26             VIEW OF 'ROLE_ROLE_PRIVS' (Cost=5500690 Card=213622196 Bytes=7263154664)
28   27               SORT (GROUP BY) (Cost=5500690 Card=213622196 Bytes=23925685952)
29   28                 NESTED LOOPS (Cost=26 Card=213622196 Bytes=23925685952)
30   29                   HASH JOIN (Cost=26 Card=2615355 Bytes=214459110)
31   30                     VIEW OF 'VW_NSO_1' (Cost=7 Card=179 Bytes=2327)
32   31                       SORT (UNIQUE) (Cost=7 Card=179 Bytes=4654)
33   32                         FILTER
34   33                           CONNECT BY
35   34                             FILTER
36   35                               INDEX (FAST FULL SCAN) OF 'I_SYSAUTH1' (UNIQUE) (Cost=2 Card=1065 Bytes=13845)
37   35                               FIXED TABLE (FULL) OF 'X$KZDOS' (Cost=7 Card=1 Bytes=13)
38   34                             TABLE ACCESS (BY USER ROWID) OF 'SYSAUTH$'
39   34                             INDEX (RANGE SCAN) OF 'I_SYSAUTH1' (UNIQUE) (Cost=2 Card=179 Bytes=4654)
40   30                     NESTED LOOPS (Cost=14 Card=1461092 Bytes=100815348)
41   40                       TABLE ACCESS (FULL) OF 'SYSAUTH$' (Cost=14 Card=17888 Bytes=697632)
42   40                       TABLE ACCESS (CLUSTER) OF 'USER$'
43   42                         INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
44   29                   TABLE ACCESS (CLUSTER) OF 'USER$'
45   44                     INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

Ultimately, I believe that the problem was a combination of application design and business process.

Firstly an overly granular security model resulted in up to 59 roles being granted to each user and secondly an absolute refusal to delete expired user accounts and only lock them when they became defunct led to a combination of over 2000 user accounts and over 80000 granted roles.

However without a change in corporate policy, could the problem be resolved in an acceptable manner?

Luckily Metalink has a couple of documents that propose alternative definitions of the FRM50_ENABLED_ROLES view: 107761.1 & 256561.1

While not intended primarily to increase performance, in testing the alternative implementations looked promising.

I raised an SR with Oracle to see what their approach would be and to confirm that they would support these alternative implementations of the view.

Oracle responded with the response that both Forms 6i and the view were unsupported.

The customer might have misunderstood when I explained that the level of support for the new version of the view was the same as that for Forms 6i.

Ultimately the new view definition was implemented and resolved the problem up till the decommissioning of the application earlier this year.

The essential lesson is that when you over-engineer a solution (and by the time I arrived, no one actually really had any understanding as to what the roles actually granted access to!) then even the oracle supplied views can fail!

The chances are that none of the developers at oracle working on Oracle 8i envisioned a system where only 20% of the created users were active and the total number of granted roles reached 80,000, so the issue can’t realistically been seen as foreseeable.

Ultimately I’m confident that the changes since Oracle 8i, where the cost base optimiser is now used to access the data dictionary, are likely to have resolved this issue for later versions, but if you do pick-up a long established Forms application where the back end technology hasn’t been brought up to date then as the number of users grows then this might become a problem for you.

May 10, 2011

Using default oracle forms icons and graphics.

Filed under: Oracle forms — bigdaveroberts @ 4:30 pm

One suggestion on tuning oracle forms applications included in the AMIS blog: was to use the icons included in the Oracle forms java runtime.

While I don’t imagine saving a few thousand bytes of data downloaded to the client PC will substantively improve performance, how do we use these free icons?

After a little experimentation it is apparent that all we need to do is to include a UNIX style (that is with a forward slash and case sensitive directory/file names) relative path.

For convenience the most useful icons are listed here:



And while it might only have a marginal effect on performance, it should be a good first step to establishing a consistent look and feel!

Interpreting ora-305500

Filed under: OLE,Oracle forms,PL/SQL — bigdaveroberts @ 9:28 am

The message returned from the sqlerrm function having encountered an ORA-305500 error consists of the slightly cryptic:

non-ORACLE exception.

However, while I have no documented confirmation of this opinion, the fact that this corresponds with the pre-defined oracle forms exception: OLE2.OLE_ERROR defined in the Oracle forms OLE2 package, then, unless this error is thrown in another oracle product, then I would suggest that it is safe to assume that what has been encountered is an OLE2 error!

And the simplest interpretation of the error is, the developer has ignored Oracles advice on handling OLE errors!

In the Oracle Forms documentation there is a clear example on how this error should be handled and what further information should be available.

Thus it is my recommendation for anyone who encounters this error and who has the source code available for their application to get hold of the appropriate Oracle documentation: Oracle Forms Developer and Oracle Reports developer – Common built in packages – release 6i document: A73152_01.pdf currently available here:

In the documentation (the best example is included in the OLE2.Last_Exception section) you will there get an explanation as to how Oracle recommends you handle this type of error:

— OLE errors are formatted as 32 bit unsigned integers and
— returned as Oracle NUMBERS. We want to extract only the
— error code, which is contained in the lowest 16 bits.
— We must first strip off the top [severity] bit, if it
— exists. Then, we must translate the error to an
— INTEGER or BINARY INTEGER and extract the error code.

Unfortunately, most people don’t follow the above advice, and if you search on Google for ‘OLE error 1004’ you will receive less hits than if you search for the original hex error number : 800A03EC.

So the advice is: If you are using the OLE2 package, make sure you add an error handler for OLE2.OLE_ERROR exception, then in the exception handler, make a call to OLE2.LAST_EXCEPTION to get the actual error number and then use the full error number in the error message rather than bothering to strip off the severity bit, etc.

In terms of actually interpreting the error, If you have a windows C compiler, you should have access to a copy of the WinError.h file where all the windows error messages are listed.

Look for the section beginning:

// Error definitions follow

// Codes 0x4000-0x40ff are reserved for OLE
// Error codes

Alternatively, if you don’t have access to this file, then you can download the file OLE error decoding tools from Microsoft support that lists most of the OLE errors and a very brief interpretation:

August 8, 2007

Running Forms under Citrix 1

Filed under: Citrix,Oracle forms — bigdaveroberts @ 1:59 pm

I’m sure everyone has their favorite scripts, either home grown or purloined to assist with database monitoring but there are always more things to check and new things to monitor.

The following scripts were developed for the analysis and monitoring of disconnection problems experienced after the system was converted into a three tear application by running the forms client under Citrix.

While developing the script I read the metalink article Note:274697.1 LOGOFF and LOGOFF BY CLEANUP Do Not Have Any LOGON Records in DBA_AUDIT_TRAIL and Vice-Versa.

The article defines its purpose to be:

This bulletin explains what are the differences between the LOGOFF and LOGOFF BY CLEANUP shown in SYS.AUD$ table and why they do not have any associated LOGON records in DBA_AUDIT_TRAIL and vice-versa.

While it includes examples of how normal logons and logoffs are recorded in theaudit table, it doesn’t explicitly indicate how disconnected processes are logged.

Thus given Oracles ‘vice versa’ comment above, I assumed that they were different and the timestamp on the ‘LOGOFF BY CLEANUP’ records would indicate the time that the process was terminated or the time the pmon process detected the dead process.

Having analysed the data collected, I came to the conclusion that my assumptions were wrong and it appears that rather than being different, the way Oracle handles disconnections is exactly the same way that it handles normal logoffs, with the logon record being cannibalised in the process.

The scripts:

The following is run whenever we suspect that there is a problem, which breaks down the disconnected sessions by client machine (Citrix server) and hour of the day:

SELECT terminal,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 0, 1, 0)) zero,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 1, 1, 0)) one,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 2, 1, 0)) two,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 3, 1, 0)) three,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 4, 1, 0)) four,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 5, 1, 0)) five,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 6, 1, 0)) six,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 7, 1, 0)) seven,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 8, 1, 0)) eight,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 9, 1, 0)) nine,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 10, 1, 0)) ten,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 11, 1, 0)) eleven,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 12, 1, 0)) twelve,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 13, 1, 0)) thirteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 14, 1, 0)) fourteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 15, 1, 0)) fifteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 16, 1, 0)) sixteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 17, 1, 0)) seventeen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 18, 1, 0)) eighteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 19, 1, 0)) nineteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 20, 1, 0)) twenty,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 21, 1, 0)) twentyone,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 22, 1, 0)) twentytwo,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 23, 1, 0)) twentythree
  FROM dba_audit_trail
 WHERE action_name = ‘LOGOFF BY CLEANUP’
  AND logoff_time > TRUNC(SysDate)
 GROUP BY terminal, TRUNC(logoff_time)

The following is run every morning and added to a cumulative log which gives a breakdown of disconnected sessions by Hour by Day which is used to identify trends:

SELECT TRUNC(logoff_time),
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 0, 1, 0)) t0,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 1, 1, 0)) t1,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 2, 1, 0)) t2,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 3, 1, 0)) t3,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 4, 1, 0)) t4,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 5, 1, 0)) t5,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 6, 1, 0)) t6,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 7, 1, 0)) t7,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 8, 1, 0)) t8,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 9, 1, 0)) t9,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 10, 1, 0)) t10,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 11, 1, 0)) t11,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 12, 1, 0)) t12,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 13, 1, 0)) t13,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 14, 1, 0)) t14,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 15, 1, 0)) t15,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 16, 1, 0)) t16,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 17, 1, 0)) t17,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 18, 1, 0)) t18,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 19, 1, 0)) t19,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 20, 1, 0)) t20,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 21, 1, 0)) t21,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 22, 1, 0)) t22,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 23, 1, 0)) t23
  FROM dba_audit_trail
 WHERE TRUNC(logoff_time) = TRUNC(sysdate – 1 )
  AND action_name = ‘LOGOFF BY CLEANUP’
 GROUP BY TRUNC(logoff_time)

May 23, 2007

How do you change the alert icons in Forms 6i? (and regedit sysinternals)

Filed under: Oracle forms — bigdaveroberts @ 3:46 pm

Easy, in only 2 steps:

Firstly, you need to edit the appropriate UI_ICON registry entry to add a new path for forms icons ahead of the default path.

Then, in this new directory you can then create 3 icons: caution.ico, stop.ico and note.ico.

When your forms application runs and displays an alert box, instead of the 3 default icons for each of the alert types, the icons you have specified will be displayed!

Further, should you wish to, many of the buttons in Forms builder (and presumably other tools) can be re-defined.

All you need to do is get hold of the regmon utility from sysinternals (Now distributed by Microsoft at ) and set it to tell you about all the icon files that ifbld60.exe is attempting to open, and by correctly identifying which icon corresponds to which button, it is possible to replace the buttons with your own creations!

Blog at