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:


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 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;



No comments: