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

Anthony wrote:

> 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

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

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

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

thtsang_yh@yahoo.com.hk (Anthony) wrote in message news:<4178bc57.0401282108.3d6c076f@posting.google. com>...
> 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

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

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

CE

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.)


Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1075354681.632738@yasure>...

> 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 wrote:

> 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.)
>

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.

How about:

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

CHR
---

a



HTH,
Brian

Anthony wrote:
>
> 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

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

Brian Peasland
dba@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"

Anthony wrote:

> Thanks Daniel for the reply.
>
> Do you mean that it is impossible to disable this behaviour?
>
> Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1075354681.632738@yasure>...
>
>
>>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.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

On Thu, 29 Jan 2004 08:12:26 -0800, Daniel Morgan <damorgan@x.washington.edu>
wrote:

>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]
>
>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.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

On Thu, 29 Jan 2004 18:31:01 +0000, Andy Hassall <andy@andyh.co.uk>
wrote:

> 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

Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1075354681.632738@yasure>...
> Anthony wrote:
>
> > 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
>
> 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