Oracle Observations

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 (http://technet.microsoft.com/en-us/sysinternals/bb896645) 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:

c:\windows\system32\config\systemprofile\desktop

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:

Bug 11704826 : PROPOSAL FAILS IN 8.1.1.3 WITH WINDOWS 2008 AND OFFICE 2007

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.

(Possibly.)

May 10, 2011

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: http://download.oracle.com/otn_hosted_doc/forms/forms/A73152_01.pdf

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:
http://support.microsoft.com/kb/122957

Create a free website or blog at WordPress.com.