ITPub博客

首页 > 数据库 > Oracle > [20211123]sqlplus @与@@的区别.txt

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

原创 Oracle 作者:lfree 时间:2021-11-23 15:24:04 1 删除 编辑

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

   


 @@ (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
-- 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
-- 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
-- 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.

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2843662/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3081
  • 访问量
    6806384