Friday, 24 October 2014

Hash (#) sign and block comment in SQL*Plus

Today I hit a weird (or on the second thought actually completely normal) behaviour of SQL*Plus utility in certain very special way of events. I have got a package for review and here an excerpt, which brought my attention
create or replace package body SOME_PKG
[..]
/*####################################################
some comment
###################################################### */

procedure PROC_NAME(arg1 VarChar2) IS
[..]
When I call it to create on a test environment, I have got:
SP2-0734: unknown command beginning "##########..." - rest of line ignored.

As this file was created on a Windows environment I thought initially some hidden characters are obstacle or EOL signs. But not. It appears that when one works in editor mode of SQL*Plus, the hash sign (#) if being set as a first character in line, is interpreted as an opening of command line and all text after it is immediately interpreted. E.g.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace procedure test1 as
  2  /*
  3  # select 1 from dual;

         1
----------
         1

SQL> */
SP2-0042: unknown command "*/" - rest of line ignored.
SQL>
With block comment it turns into a deadly combo ;-) - one may think it is absolutely ok (and it works with let's say SQL Developer) but with SQL*Plus it fails (the closing of block comment may be ommited as in the example above) and then random errors appear.

No comments: