Monday, July 23, 2012

SET SCAN OFF and SET SCAN ON


This was tested on Oracle 11.2. It's not a good idea to use an ampersand in a PL/SQL comment. Oracle assumes it is the start of a substitution variable and prompts you to input a value:

SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands & how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /
Enter value for how:
old   3: -- Ampersands & how they cause problems in PL/SQL.
new   3: -- Ampersands  they cause problems in PL/SQL.

Procedure created.

SQL>

... and the comment entry is not stored correctly in the database:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands  they cause problems in PL/SQL.
null;
end;

SQL>

One way to deal with this is to get your developers to use the word and instead:

SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands and how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /

Procedure created.

SQL>

Then the comment is stored correctly:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands and how they cause problems in PL/SQL.
null;
end;

SQL>

If that is not possible, you can do set scan off before compiling the PL/SQL then Oracle does not treat the ampersand as the start of a substition variable. Before you do this, you need to be sure that your PL/SQL does not contain any genuine substitution variables:

SQL> set scan off
SQL> show scan
scan OFF
SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands & how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /

Procedure created.

SQL>

... and again the comment is stored correctly:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands & how they cause problems in PL/SQL.
null;
end;

SQL>

It's probably best to set scan on afterwards in case any SQL which follows includes a real substitution variable:

SQL> set scan on
SQL> show scan
scan ON
SQL>

1 comment:

Andrew Stuart Reid said...

Looking at this again, I think all the SELECT statements from USER_SOURCE should have had an ORDER BY to sort the PL/SQL text by line number. Fortunately on this occasion, missing them out does not seem to have made any difference.