Oracle Observations

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 0×4000-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

December 14, 2007

PLSQL_Warnings in 11g

Filed under: 11g,PL/SQL — bigdaveroberts @ 4:05 pm

In preparation for the on/off/on upgrade of our application from Oracle 8.1.7 to 9.2.8, I thought it would be interesting to migrate the PL/SQL to my installation of an 11g Oracle database that came out of the UKOUG conference OTN install fest, and also, out of interest see what warnings come out of the PL/SQL compiler when you set PLSQL_Warnings=’enable:all’ compilation option.

Pleasingly out of almost 45,000 lines of source, only one new error was generated:

SQL> show errors;
Errors for PACKAGE BODY P_BODY:

LINE/COL ERROR
——– —————————————————————–
211/5 PLS-00593: default value of parameter “P_PARAM” in body must match that of spec

Here the only problem was that while Oracle 8i is happy for a default parameter value to be specified only in the package body definition, Oracle 11g requires the default parameter to be specified in both the package and the package body.

Eg. while the following code works in 8i, it doesn’t in later releases:

SQL> create or replace PACKAGE P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2
4 );
5 END;
6 /

Package created.

SQL> create or replace PACKAGE body P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2 := ‘N’
4 ) IS
5 BEGIN
6 NULL;
7 END;
8 END;
9
10 /

Package body created.

A small anomaly:

If a package does include errors or warnings, then if the package and body is compiled, then the errors are not displayed, only the following cryptic error message:

SQL> alter PACKAGE P_BODY compile plsql_warnings=’enable:all’;

SP2-0809: Package altered with compilation warnings

SQL> show errors;
SP2-0564: Object “p_bodyplsql_warnings=’enable:all’” is INVALID, it may not be described.

However if only the body is compiled, then the errors and warnings are successfully displayed.

So that leaves me with a selection of PLW warnings to evaluate and/or fix:

PLW-05005: function P_FUNCTION returns without value at line 244
PLW-06002: Unreachable code
PLW-06006: uncalled procedure “P_PROCEDURE” is removed.
PLW-06009: procedure “P_PROCEDURE” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
PLW-06010: keyword “NAME” used as a defined name
PLW-06010: keyword “REF” used as a defined name
PLW-06010: keyword “RESULT” used as a defined name
PLW-07202: bind type would result in conversion away from column type
PLW-07203: parameter ‘P_PARAMETER’ may benefit from use of the NOCOPY compiler hint
PLW-07204: conversion away from column type may result in sub-optimal query plan

A superficial review of the error messages manual indicates that the number of potential warnings has grown substantially from the 9 and 11 warnings of 10g releases 1 and 2, to a total of 36 documented warnings in 11g release 1. With 3 of the 8 warning types received being new ones from 11g.

Oracle 10 R1 manual page:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10744/plwus.htm#sthref64

Oracle 10 R2 manual page:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/plwus.htm#ERRMG53664

Oracle 11 R1 manual page:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/plwus.htm

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.