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>
Labels:
ampersand,
comment,
Oracle 11.2,
PL/SQL,
set scan off,
set scan on,
substition variable
Location:
West Sussex, UK
Subscribe to:
Post Comments (Atom)
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.
ReplyDelete