[20211123]sqlplus @与@@的区别.txt

--//调式sql脚本，遇到一个古老的问题，就是@与@@的区别的区别。

1.环境：
SCOTT@book> @ ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> help @

 @ ("at" sign)
 -------------
 Runs the SQL*Plus statements in the specified script. The script can be
 called from the local file system or a web server.

 @ {url|file_name[.ext]} [arg ...]

 where url supports HTTP and FTP protocols in the form:

    http://host.domain/script.sql


 @@ (double "at" sign)
 ---------------------

 Runs the specified script. This command is almost identical to
 the @ command. It is useful for running nested scripts because it
 has the additional functionality of looking for the nested script
 in the same url or path as the calling script.

 @@ {url|file_name[.ext]} [arg ...]

--//@@ 不同在与because it  has the additional functionality of looking for the nested script in the same url or path as
--//the calling script.
--//我的理解调用脚本的在相同的url或者path指向的脚本。

2.我遇到的问题：
$ echo $SQLPATH
/home/oracle/sqllaji:/home/oracle/sqllaji/tpt

--//我的环境定义了两个PATH.优先查找我写的路径。

$ cat ~/sqllaji/tpt/ev.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

@oerr &1
prompt alter session set events '&1 trace name context forever, level &2';;
alter session set events '&1 trace name context forever, level &2';

--//执行遇到的情况如下：
SCOTT@book> show verify
verify OFF

SCOTT@book> @ ev 10046 12
Error 10046 is : ORA-10046: enable SQL statement timing
Enter value for 1: 10046
alter session set events '10046 trace name context forever, level 12';
Enter value for 1: 10046
Session altered.

--//我开始有点不理解为什么每次必须输入参数1，而参数2不需要输入。

$ cat ~/sqllaji/tpt/oerr.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

-- OERR functionality - list description for and ORA- error code
-- The data comes from $ORACLE_HOME/rdbms/mesg/oraus.msb file
-- which is a binary compiled version of $ORACLE_HOME/rdbms/mesg/oraus.msg file

@@saveset
set serverout on size 1000000 feedback off
prompt
exec dbms_output.put_line(sqlerrm(-&1))
prompt
@@loadset

--//注解oerr那行正常，原来在sqllaji目录下也有一个oerr.sql.
--//两者写法有一点点不同。
 $ cat ~/sqllaji/oerr.sql
prompt
SET serveroutput ON SIZE 1000000
SET feedback off
EXEC dbms_output.put_line('Error ' || &&1 || ' is : ' ||sqlerrm(-1 * &&1));
prompt
undefine 1
SET feedback ON

--//加入路径执行才发现，才发现两者的输出有点点不同。

SCOTT@book> @ oerr 10046
Error 10046 is : ORA-10046: enable SQL statement timing
~~~~~~~~~~~~~~

SCOTT@book> @ tpt/oerr 10046
ORA-10046: enable SQL statement timing

--//我做了几个测试。
--// 改名~/sqllaji/oerr.sql => ~/sqllaji/oerr.xxx.测试正常，修改回来。
--// 改名~/sqllaji/tpt/oerr.sql => ~/sqllaji/tpt/oerr.xxx. 测试时必须输入参数1。

--//先临时修改如下：
$ cat ev.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

@@oerr &1
prompt alter session set events '&1 trace name context forever, level &2';;
alter session set events '&1 trace name context forever, level &2';

--//继续测试：
SCOTT@book> @ ev 10046 12
SP2-0310: unable to open file "/home/oracle/sqllaji/tpt/oerr.sql"
alter session set events '10046 trace name context forever, level 12';
Session altered.

--//也就是打入@@oerr.sql 只能在相同目录下查询并执行。
--//改名~/sqllaji/tpt/oerr.xxx => ~/sqllaji/tpt/oerr.sql。

--//再测试正常。

SCOTT@book> @ ev 10046 12
ORA-10046: enable SQL statement timing
alter session set events '10046 trace name context forever, level 12';
Session altered.

--//当前正在整理自己以前写的脚本，管理有点乱。