Oracle 11 upgrade challenges

PLS-00593: default value of parameter “x” must match that of spec

At one customer, we are upgrading from Oracle 8.1.7.4 to Oracle 11g. It was about time. So far, there are not many challenges. The few problems so far have to do with migrating from a Single Byte character set to a Multibyte character set (on purpose) and some PL/SQL mismatches. Today I was looking into some package bodies that did not compile in 11g. The error was a PLS-00593: default value of parameter “x” must match that of spec.  So we seemed to have a few packages with one or more functions in them in which the parameter default values did not match. So I had to straighten that and I wondered: what is the one it must be if I do not want the risk of changing the application logic that may rely on the default values. The one in the specification, or the one in the body? The best way to answer that question is to test it:

CREATE OR REPLACE PACKAGE par_test
IS
  PROCEDURE test_par (a IN VARCHAR2, b IN VARCHAR2 DEFAULT 'TRUE');
END;
/ 

CREATE OR REPLACE PACKAGE BODY par_test
IS
  PROCEDURE test_par (a IN VARCHAR2, b IN VARCHAR2 DEFAULT 'FALSE')
  IS
  BEGIN
   DBMS_OUTPUT.put_line ('b=' || b);
  END;
END;
/

BEGIN
  par_test.test_par ('And the answer is...', 'UNKNOWN'); -- specify a value for parameter b
  par_test.test_par ('And the answer is...'); -- rely on default value
END;
/

Result:
b=UNKNOWN
b=TRUE

So it seems to be the default value of the package specification that rules…

PLS-00801: internal error [phd_get_defn: unexpected overloaded D_S_ED]

One other error that came along was in one other package body. I got the scary looking PLS-00801: internal error [phd_get_defn: unexpected overloaded D_S_ED]. There was not much to be found on the Internet, except that you need to contact Oracle support if you get it. I like to check if I can isolate the cause for it first and then probably solve it.

The package body consists of a lot of generated code, generated by PL/Generator (a PL/SQL generator I have used with lots of joy. It is created by Steven Feuerstein and has an even much more powerful successor called the Quest CodeGen Utility now). Since almost all the packages in the schema contained similar generated code, I had a feeling the error must be caused by my own customized code. So I commented out the custom procedures/functions in the specification and tried to compile both spec and body. That already compiled fine. So I started to un-comment the custom modules one by one and very soon I found that the error was caused by calls to

PRAGMA RESTRICT_REFERENCES(code_convert_to,WNDS,WNPS);

 These packages were created in the years of Oracle 7 and back then it was a lot of times necessary to “tell” Oracle that a function adhered to certain rules, like that it did not changed package state or database state, in order to use it in regular SQL statements. As years went by, the compiler got more intelligent and Oracle could find out a lot of these things itself. I did not expect that I would get a nasty PL/SQL error on the remains of this “Oracle 7” code once… Well, it may be of use to anyone who gets this same error, since Google and MetaLink did not help me here. 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s