I had a problem recently with a large package, which used to compile without errors on
Oracle 9. When I moved it to Oracle 10 and tried to compile it there, it produced several pages of SP2-0734 messages. The package had about a dozen statements something like this:
SELECT
NUMERICAL_EXPRESSION1
/
NUMERICAL_EXPRESSION2
FROM
SOME_TABLE_OR_VIEW
ETC
Each statement was doing a division and the slash was on a line by itself several places from the start of that line. These statements were interpreted correctly under Oracle 9 but not by Oracle 10 nor 11. I did an experiment with an SQL statement doing a simple division and ran it on Oracle 9, 10 and 11. You can see the results on the screen print below (click to enlarge it):
In the top left hand corner, the Oracle 9 version works correctly.
In the bottom row, the Oracle 10 and 11 versions both fail as soon as they see the slash in column 8. I believe Oracle sees it as an instruction to run the SQL typed so far rather than a division symbol.
In the top right hand corner, the Oracle 10 version is rewritten with the slash at the end of
line 1 and it works correctly again.
I then went back to the package with the compilation errors, changed the dozen or so division statements in the same way and the compilation errors disappeared.
When I had some spare time I raised a Service Request with Oracle. They confirmed that it was bug 4219339 and also provided a different workaround, setting SQLPLUSCOMPATIBILITY to a value less than 9. That worked too, as you can see in the example below, which I ran on Oracle 10.2.0.1.0 on Linux:
SQL> select 1
2 /
select 1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> set sqlpluscompatibility 8.1.7
SQL> select 1
2 /
3 2
4 from dual;
1/2
----------
.5
SQL>
No comments:
Post a Comment