How to disable '@' in SQL*Plus?

[Anthony]

I would like to execute a SQL script similar to following in sqlplus:

(test.sql)
select '
@a
'
from dual;

SQL*plus attempted to execute a script called a.sql and this fails:

SQL> @test.sql
SP2-0310: unable to open file "a.sql"

''
--------------------------------


Is there any method to disable SQL*plus from interpreting the @
chacter as the start command and execute a script?

(The expected output of the sql is as follows. Seems 'ed'iting SQL can
disable the @:

SQL> ed
Wrote file afiedt.buf

1 select '
2 @a
3 '
4* from dual
SQL> /


@a
----------------------------------------------------------------

@a


)

Thanks very much in advance

[Daniel Morgan]

Post by Anthony
(test.sql)
select '
@a
'
from dual;
SP2-0310: unable to open file "a.sql"
''
--------------------------------
chacter as the start command and execute a script?
(The expected output of the sql is as follows. Seems 'ed'iting SQL can
SQL> ed
Wrote file afiedt.buf
1 select '
3 '
4* from dual
SQL> /
@a
----------------------------------------------------------------
@a
)
Thanks very much in advance

SELECT keyword
FROM v$reserved_words
WHERE LENGTH(keyword) = 1;

Don't try to kludge your way around a bad design ... fix your design.

[Anthony]

Thanks Daniel for the reply.

Do you mean that it is impossible to disable this behaviour?

As far as I know, the DB does not interpert the '@' specially in this
case. It is SQLPlus which treat it as a special command. And surely
Oracle will allow a reserved word in a string.
(I would think that the '@' was considered reserved word because of
its use in db link. But not sure...)

BTW, I don't think having a '@' in a the start position of a line in
multi-line string indicates a bad design...

Actually, similar problem also arise for the following case:

(test.sql)
create or replace PACKAGE MyPackage IS
PROCEDURE MyProcedure(MyParam1 IN VARCHAR2) ;
/*
Do something.
@param MyParam1 First Parameter
*/
END MyPackage;
/

(In SQL*Plus)
SQL> @C:\test.txt
SP2-0310: unable to open file "param.sql"

Package created.

(I would agree that there may be an issue on programming style here.)

Post by Daniel Morgan
SELECT keyword
FROM v$reserved_words
WHERE LENGTH(keyword) = 1;
Don't try to kludge your way around a bad design ... fix your design.

[Bricklen]

Post by Anthony
Thanks Daniel for the reply.
Do you mean that it is impossible to disable this behaviour?
case. It is SQLPlus which treat it as a special command. And surely
Oracle will allow a reserved word in a string.
its use in db link. But not sure...)
multi-line string indicates a bad design...
(test.sql)
create or replace PACKAGE MyPackage IS
PROCEDURE MyProcedure(MyParam1 IN VARCHAR2) ;
/*
Do something.
@param MyParam1 First Parameter
*/
END MyPackage;
/
(In SQL*Plus)
SP2-0310: unable to open file "param.sql"
Package created.
(I would agree that there may be an issue on programming style here.)

You have a few issues here:
if it is for SQL*Plus only, type
set escape \

then your code can run:
select '
\@a
'
from dual;

or just run it as a single line, like
select '@a' from dual;
so there is no need to escape it.


If you are just using the @ symbol inside of comments, then try it like
this:

create or replace PACKAGE MyPackage IS
PROCEDURE MyProcedure(MyParam1 IN VARCHAR2) ;
/*
Do something.
-- @param MyParam1 First Parameter
*/
END MyPackage;
/

with the "--" preceding the @ symbol.


That's all that I can think of offhand. I also wouldn't recommend
putting suspect code like that into production, but that's totally up to
you.

[Daniel Morgan]

Post by Anthony
Thanks Daniel for the reply.
Do you mean that it is impossible to disable this behaviour?

Post by Daniel Morgan
SELECT keyword
FROM v$reserved_words
WHERE LENGTH(keyword) = 1;
Don't try to kludge your way around a bad design ... fix your design.

Impossible. You can kludge your way around it on a case by case basis
and probably get anything to work ... at least until Oracle releases a
patch that breaks the kludge. But the question I am asking is why, given
all of the characters on the keyboard, anyone would intentionally design
anything using a restricted keyword? Just change to something else and
the entire problem disappears.

And consider the implications of using any tools that can't use the
kludge's available to a programmer. I would think every production DBA
looking at your code would get a sick feeling in the pit of their
stomach that can best be expressed by saying: "He's going to write this
garbage and go on to something else and I'm the one that's going to have
to maintain it for the next five years. Well there go my evenings and
weekends."

I'm encouraging you to never use keywords for anything other than their
intended usage.

[Andy Hassall]

Post by Daniel Morgan
Impossible. You can kludge your way around it on a case by case basis
and probably get anything to work ... at least until Oracle releases a
patch that breaks the kludge. But the question I am asking is why, given
all of the characters on the keyboard, anyone would intentionally design
anything using a restricted keyword? Just change to something else and
the entire problem disappears.

[snip]

Post by Daniel Morgan
I'm encouraging you to never use keywords for anything other than their
intended usage.

But the OP posted an issue with an @ in a _literal string_, and a PL/SQL
_comment_. So you're saying do not use any string present in V$RESERVED_WORDS
in a literal string or a PL/SQL comment? That's rather limiting isn't it, given
that useful things like spaces are in that view?

It's clearly a bug in SQL*Plus (it has no right parsing anything other than
substitution variables inside strings or comments, certainly not running them
as an SQL*Plus command unless prefixed with the SQLPREFIX character); so how is
it a fault on the part of the OP?

e.g.

SQL> set sqlprefix ~
SQL> begin
2 /*
3 @none of sqlplus's business
SP2-0310: unable to open file "none.sql"
3 start start.sql
4 ~start start.sql
SP2-0310: unable to open file "start.sql"
4

(Unless you can find a reference in the manual that says the @ command will be
run even when in SQL or PL/SQL mode, regardless of the fact it doesn't make
sense - in which I'll stand corrected, but baffled).

Keywords shouldn't be used for identifiers, but this isn't about identifiers.

[Sybrand Bakker]

Post by Andy Hassall
It's clearly a bug in SQL*Plus (it has no right parsing anything other than
substitution variables inside strings or comments, certainly not running them
as an SQL*Plus command unless prefixed with the SQLPREFIX character); so how is
it a fault on the part of the OP?

It is quite clear Oracle isn't using the BNF method when parsing sql.


--
Sybrand Bakker, Senior Oracle DBA

[cjbj]

Post by Andy Hassall

Post by Daniel Morgan
Impossible. You can kludge your way around it on a case by case basis
and probably get anything to work ... at least until Oracle releases a
patch that breaks the kludge. But the question I am asking is why, given
all of the characters on the keyboard, anyone would intentionally design
anything using a restricted keyword? Just change to something else and
the entire problem disappears.

[snip]

Post by Daniel Morgan
I'm encouraging you to never use keywords for anything other than their
intended usage.

_comment_. So you're saying do not use any string present in V$RESERVED_WORDS
in a literal string or a PL/SQL comment? That's rather limiting isn't it, given
that useful things like spaces are in that view?
It's clearly a bug in SQL*Plus (it has no right parsing anything other than
substitution variables inside strings or comments, certainly not running them
as an SQL*Plus command unless prefixed with the SQLPREFIX character); so how is
it a fault on the part of the OP?
e.g.
SQL> set sqlprefix ~
SQL> begin
2 /*
SP2-0310: unable to open file "none.sql"
3 start start.sql
4 ~start start.sql
SP2-0310: unable to open file "start.sql"
4
run even when in SQL or PL/SQL mode, regardless of the fact it doesn't make
sense - in which I'll stand corrected, but baffled).
Keywords shouldn't be used for identifiers, but this isn't about identifiers.

SQL*Plus has a very basic command parser. There are some other listed
"features" at:

http://otn.oracle.com/support/tech/sql_plus/htdocs/runtime2.html#minus_sign

I expect the "@" issue has been a "feature" of SQL*Plus for the last 20 years.

-- CJ

[Daniel Morgan]

Post by cjbj

Post by Andy Hassall

Post by Daniel Morgan
Impossible. You can kludge your way around it on a case by case basis
and probably get anything to work ... at least until Oracle releases a
patch that breaks the kludge. But the question I am asking is why, given
all of the characters on the keyboard, anyone would intentionally design
anything using a restricted keyword? Just change to something else and
the entire problem disappears.

[snip]

Post by Daniel Morgan
I'm encouraging you to never use keywords for anything other than their
intended usage.

_comment_. So you're saying do not use any string present in V$RESERVED_WORDS
in a literal string or a PL/SQL comment? That's rather limiting isn't it, given
that useful things like spaces are in that view?
It's clearly a bug in SQL*Plus (it has no right parsing anything other than
substitution variables inside strings or comments, certainly not running them
as an SQL*Plus command unless prefixed with the SQLPREFIX character); so how is
it a fault on the part of the OP?
e.g.
SQL> set sqlprefix ~
SQL> begin
2 /*
SP2-0310: unable to open file "none.sql"
3 start start.sql
4 ~start start.sql
SP2-0310: unable to open file "start.sql"
4
run even when in SQL or PL/SQL mode, regardless of the fact it doesn't make
sense - in which I'll stand corrected, but baffled).
Keywords shouldn't be used for identifiers, but this isn't about identifiers.

SQL*Plus has a very basic command parser. There are some other listed
http://otn.oracle.com/support/tech/sql_plus/htdocs/runtime2.html#minus_sign
-- CJ

SQL*Plus is irrelevant to the fact that '@' is a keyword.

Rather than pontificating ... run the SQL statement I supplied.

[Andy Hassall]

Post by Daniel Morgan

Post by cjbj

Post by Andy Hassall

Post by Daniel Morgan
I'm encouraging you to never use keywords for anything other than their
intended usage.

_comment_. So you're saying do not use any string present in V$RESERVED_WORDS
in a literal string or a PL/SQL comment? That's rather limiting isn't it, given
that useful things like spaces are in that view?
It's clearly a bug in SQL*Plus (it has no right parsing anything other than
substitution variables inside strings or comments, certainly not running them
as an SQL*Plus command unless prefixed with the SQLPREFIX character); so how is
it a fault on the part of the OP?
Keywords shouldn't be used for identifiers, but this isn't about identifiers.

SQL*Plus has a very basic command parser. There are some other listed
http://otn.oracle.com/support/tech/sql_plus/htdocs/runtime2.html#minus_sign

Which is documented at least. And substitution variables can be turned off.
And is unlikely to ever be fixed, so the workaround is going to have to be
"don't start a line with @". It's still a bug, since it's not documented; and
does it ever make sense to execute a script in the middle of entering a command
into the buffer?

Post by Daniel Morgan
Rather than pontificating ... run the SQL statement I supplied.

Have you? The space character is listed there. As are many other bits of
punctuation. Only a leading @ causes a problem in SQL*Plus, and that's got
nothing to do with it being a keyword, it's because SQL*Plus is incorrectly
running a script when it should be accepting SQL or PL/SQL, not SQL*Plus
commands.

Why should the fact that something is listed in v$reserved_words stop you from
putting it in a comment or a literal string?

[cjbj]

Post by Daniel Morgan

Post by Anthony
(test.sql)
select '
@a
'
from dual;
SP2-0310: unable to open file "a.sql"
''
--------------------------------
chacter as the start command and execute a script?
(The expected output of the sql is as follows. Seems 'ed'iting SQL can
SQL> ed
Wrote file afiedt.buf
1 select '
3 '
4* from dual
SQL> /
@a
----------------------------------------------------------------
@a
)
Thanks very much in advance

SELECT keyword
FROM v$reserved_words
WHERE LENGTH(keyword) = 1;

Does that show the SQL*Plus "@" alias for START, the "@" that
separates the connection identifier in a connection string, or the "@"
in a database link?

Hint: it's not likely to be the first, which purely a client-side command.

-- CJ

[Charlie Edwards]

Post by Anthony
(test.sql)
select '
@a
'
from dual;
SP2-0310: unable to open file "a.sql"
''
--------------------------------
chacter as the start command and execute a script?
(The expected output of the sql is as follows. Seems 'ed'iting SQL can
SQL> ed
Wrote file afiedt.buf
1 select '
3 '
4* from dual
SQL> /
@a
----------------------------------------------------------------
@a
)
Thanks very much in advance

Seems a weird thing to want to do, but ...

select '
'||'@a
'
from dual
/

CE

[Brian Peasland]

How about:

SQL> select chr(10)||'a'||chr(10) from dual;

CHR
---

a



HTH,
Brian

Post by Anthony
(test.sql)
select '
@a
'
from dual;
SP2-0310: unable to open file "a.sql"
''
--------------------------------
chacter as the start command and execute a script?
(The expected output of the sql is as follows. Seems 'ed'iting SQL can
SQL> ed
Wrote file afiedt.buf
1 select '
3 '
4* from dual
SQL> /
@a
----------------------------------------------------------------
@a
)
Thanks very much in advance

--
===================================================================

Brian Peasland
***@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[Merci]

Can you replace the offending @ ?

select chr(64)||'a' from dual;

Post by Anthony
(test.sql)
select '
@a
'
from dual;
SP2-0310: unable to open file "a.sql"
''
--------------------------------
chacter as the start command and execute a script?
(The expected output of the sql is as follows. Seems 'ed'iting SQL can
SQL> ed
Wrote file afiedt.buf
1 select '
3 '
4* from dual
SQL> /
@a
----------------------------------------------------------------
@a
)
Thanks very much in advance

[Turkbear]

Post by Merci
select chr(64)||'a' from dual;

Post by Anthony
(test.sql)
select '
@a
'
from dual;
SP2-0310: unable to open file "a.sql"
''
--------------------------------
chacter as the start command and execute a script?
(The expected output of the sql is as follows. Seems 'ed'iting SQL can
SQL> ed
Wrote file afiedt.buf
1 select '
3 '
4* from dual
SQL> /
@a
----------------------------------------------------------------
@a
)
Thanks very much in advance

Your original test.sql works fine for me ( I renamed it test1.sql since I already had a test.sql)

test1.sql:
select '@a' from dual;


Output::
SQL> @test1.sql

'@
--
@a

SQL>

--------------------------------------------------------------------------------------------------------------------------------------------------