Tuesday, January 18, 2011

Slashes in SQL*Plus and PL/SQL

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: