CODEWITHSUNDEEP
oracle-databasesqlplus
Danilo Piazzalunga
Danilo Piazzalunga

Reputation: 7834

Oracle SQL*Plus: disable processing of '@' (at sign) metacharacter

Oracle SQL*Plus interprets the at sign, @ as a command to run a SQL script.

I have a .sql script which contains the @ character in comments, such as:

create or replace package mypkg
as

  /*
    Procedure foo

    @param bar blah blah blah
  */
  procedure foo(bar varchar2);

end mypkg;
/

How can I disable processing of the at sign? I am already using SET DEFINE OFF and SET SQLPREFIX OFF to disable unwanted processing of metacharacters.

Upvotes: 4

Views: 2046

Answers (2)

Bjarte Brandt
Bjarte Brandt

Reputation: 4471

I have used this commenting notation for years so I was surprised this was an issue. I make use of the package "pldoc" http://sourceforge.net/projects/pldoc/?source=directory to generate documentation. The standard way of commenting is like this (like javadoc):

create or replace package mypkg
as

  /**
  * Procedure foo
  * 
  * @param bar blah blah blah
  */
  procedure foo(bar varchar2);

end mypkg;
/

me@XE> @mypkg.sql

Package created.

Upvotes: 3

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

There are a couple of ways to disable the START command (which includes the @ and @@ commands).

One way is to place an appropriate entry in PRODUCT_USER_PROFILE (which is actually a synonym for SYSTEM.PRODUCT_PRIVS). To do so perform an INSERT similar to

INSERT INTO PRODUCT_USER_PROFILE
  (PRODUCT,     USERID, ATTRIBUTE, CHAR_VALUE)
VALUES
  ('SQL*Plus', 'DUMMY', 'START',   'DISABLED')

This requires that the userid you're using have INSERT privileges on PRODUCT_USER_PROFILE.

The second way is to use the -RESTRICT option with an argument of 3 when starting SQL*Plus. This may be simpler, but there are additional commands disabled by -RESTRICT 3 which you may need to have enabled, such as SPOOL. See the section titled "RESTRICT Option" at this page for more info.

Share and enjoy.

Upvotes: 4

Related Questions