Oracle 9i to Oracle 10g Upgrade – Help needed for some PL/SQL errors

45 pts.
Tags:
Oracle 10g
Oracle 9i
Oracle 9i to 10g Upgrade
Oracle upgrades
We are doing a Database server upgrade from Oracle 9i to Oracle 10g. As a part of this upgrade activity, I got in total three different PL-SQL Errors for which I had to make code changes in the database server. These errors were: (1) PLS-00371: at most one declaration for "string" is permitted. (2) PLS-00593: default value of parameter "string" in body must match that of spec. (3) PLS-00216: NUMBER precision constraint must be in range (1 .. 38) In Oracle 9i Database server version we were not getting these errors. I wanted to be sure that these errors are because of the version upgrade and there is no other cause. I tried to search on google for PLS-00371 errors. I got instances where other developers have got the same error during migration. But there is no satisfactory explanation which drills down the cause to internals of version upgrade. I understand that PLS-00371 and PLS-00593 are most probably because of PL/SQL compiler change in the Oracle 10g and Oracle 9i version. However, I want to understand what exactly is the change that has caused these errors. It would be very helpful for me if you can explain one or all of these errors as an outcome of version upgrade.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Could you please post the code (relevant portions) in which these errors are being reported ?
    69,045 pointsBadges:
    report
  • PrateekSingh
    Hi Carlos, Thanks for looking into this. Because its a client code, I cannot post it. However, I can explain you the scnarios. PLS-00371: There was a package P1 which had double definition for a variable V1. Stored Procedure SP1 was trying to consume P1.V1. SP1 compiled succssfully in Oracle 9i but gave the PLS error in Oracle 10g. PLS-00593: There was a variable V2 for which was defined in Package P2. The default value of V2 in packages specification and body were different. P2 compiled successfully in Oracle 9i but not in Oracle 10g. PLS-00216: In a package there was a number variable defined as N1 NUMBER(40,scale). This compiled successfully in Oracle 9i but not in Oracle 10g. Kindly let me know if you want to know anything more about the code. Thanks once more. Regards, Prateek
    45 pointsBadges:
    report
  • carlosdl
    Well, I think this is not an answer, but this is what I can tell you about the errors: PLS-00371: You can define a variable more than once in a package spec in any version of the database (I think), but you will get an error when some code inside the package body references that variable. I have tested it in 8i and 10g, and the results were the same. I find pretty strange that this was different in 9i. I would say that was a bug in 9i (were you using the last release/patch set for 9i ?) PLS-00593: I think that defining a procedure's parameter with different default value in the spec and the body was permitted in all previous versions of the database, and it was corrected in 10g. I would say also that this was a bug in previous versions, since you could see the body with some default value for that parameter, but when running the code, the default value used was the one from the spec definition, which was really misleading. PLS-00216: Seems to be another bug fixed. In 8i (and I guess in 9i also) you could define a number with a precision of 42 at maximum, but if you defined the variable with a precision of 43, the error message was 'precision constraint must be in range (1 .. 38)', which was also confusing since a precision of 42 caused no errors. Do you really need a number with a precision of 40 ? -------------- I think it was good that you were alerted for these code errors, since you could get unexpected results if they were not reported an compilation time, as in 9i. Regards,
    69,045 pointsBadges:
    report
  • PrateekSingh
    Hi Carlos, Thanks for the explanations. Regarding the first error i.e, PLS-00371, I have tested that behavior in 8i and not in 9i. I am not getting any compilation error in 8i. When you have done the double declaration of the variable v1, both the declarations should be exactly same. Have you tested in this way? If you have tested in this way, are you getting run-time error in 8i or compile time error. Thanks for your explanations for the second and third errors. Thanks and Regards, Prateek
    45 pointsBadges:
    report
  • carlosdl
    Hi Prateek. I get compile time errors in 8i regardless of whether both the declarations are exactly same or not. This was my test: Connected to: Oracle8i Release 8.1.7.4.1 - Production JServer Release 8.1.7.4.1 - Production SQL> create or replace package the_test as 2 var1 varchar2(10); 3 var1 varchar2(10); 4 5 procedure test; 6 end the_test; 7 / Package created. SQL> create or replace package body the_test as 2 procedure test is 3 begin 4 dbms_output.put_line('testing'); 5 var1 := 'test'; 6 end; 7 end the_test; 8 / Warning: Package Body created with compilation errors. SQL> show errors; Errors for PACKAGE BODY THE_TEST: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/2 PLS-00371: at most one declaration for 'VAR1' is permitted in the declaration section 5/2 PL/SQL: Statement ignored ------ If the varable is not referenced in the body, no errors are reported: SQL> create or replace package the_test as 2 var1 varchar2(10); 3 var1 varchar2(10); 4 5 procedure test; 6 end the_test; 7 / Package created. SQL> SQL> SQL> create or replace package body the_test as 2 procedure test is 3 begin 4 dbms_output.put_line('testing'); 5 --var1 := 'test'; 6 end; 7 end the_test; 8 / Package body created. SQL> show errors; No errors. Regards,
    69,045 pointsBadges:
    report
  • PrateekSingh
    Hi Carlos, Sorry for wrong information and for the time it might have cost you. I am compiling on Oracle 9.2.0.4.0 version, when there are no errors. Thanks and Regards, Prateek
    45 pointsBadges:
    report
  • PrateekSingh
    Hi Carlos, I re-checked the scnario in an Oracle 8 database server, and it compiles successfully for me. My guess is that there some compiler settings for 8i and 9i version which are different in yours and mine database server. Thanks and Regards, Prateek
    45 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following