[20211123]sqlplus @與@@的區別.txt

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

--//當前正在整理自己以前寫的指令碼，管理有點亂。