[20220414]toad呼叫執行指令碼問題.txt

[20220414]toad呼叫執行指令碼問題.txt

--//早上測試執行使用toad執行如下：
1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ tpt/pd _optim_peek_user_binds
Show all parameters and session values from x$ksppi/x$ksppcv...
 NUM N_HEX NAME                   VALUE DESCRIPTION
---- ----- ---------------------- ----- ----------------------------
2180   884 _optim_peek_user_binds TRUE  enable peeking of user binds

2.測試：
--//toad 下execute as script
@ tpt/pd _optim_peek_user_binds

Show all parameters and session values from x$ksppi/x$ksppcv...
old: select
   n.indx + 1 num
 , to_char(n.indx + 1, 'XXXX') n_hex
 , n.ksppinm pd_name
 , c.ksppstvl pd_value
 , n.ksppdesc pd_descr
from sys.x$ksppi n, sys.x$ksppcv c
where n.indx=c.indx
and (
   lower(n.ksppinm) || ' ' || lower(n.ksppdesc) like lower('&1')
--   or lower(n.ksppdesc) like lower('&1')
)
new: select
   n.indx + 1 num
 , to_char(n.indx + 1, 'XXXX') n_hex
 , n.ksppinm pd_name
 , c.ksppstvl pd_value
 , n.ksppdesc pd_descr
from sys.x$ksppi n, sys.x$ksppcv c
where n.indx=c.indx
and (
   lower(n.ksppinm) || ' ' || lower(n.ksppdesc) like lower('"%_optim_peek_user_binds%"')
--   or lower(n.ksppdesc) like lower('"%_optim_peek_user_binds%"')
)
未選擇任何行。

--//很奇怪並沒有返回行。我開始以為是展開引數是裡面_引起的問題。在windows下sqlplus下執行：

SYS@78> @ tpt/pd _optim_peek_user_binds
Show all parameters and session values from x$ksppi/x$ksppcv...
       NUM N_HEX NAME                     VALUE   DESCRIPTION
---------- ----- ------------------------ ------- ----------------------------
      2180   884 _optim_peek_user_binds   TRUE    enable peeking of user binds

--//為什麼toad在執行沒有返回行了。
select
   n.indx + 1 num
 , to_char(n.indx + 1, 'XXXX') n_hex
 , n.ksppinm pd_name
 , c.ksppstvl pd_value
 , n.ksppdesc pd_descr
from sys.x$ksppi n, sys.x$ksppcv c
where n.indx=c.indx
and (
   lower(n.ksppinm) || ' ' || lower(n.ksppdesc) like lower('"%_optim_peek_user_binds%"')
--   or lower(n.ksppdesc) like lower('"%_optim_peek_user_binds%"')
)

--//再次執行確實沒有返回行。仔細檢查發現問題在於lower('"%_optim_peek_user_binds%"') 裡面的雙引號。

SYS@book> select lower('"%_optim_peek_user_binds%"') c30 from dual;
C30
------------------------------
"%_optim_peek_user_binds%"

--//也就是toad的execute as script在展開引數是把裡面雙引號沒有剔除。如果執行如下：

select
   n.indx + 1 num
 , to_char(n.indx + 1, 'XXXX') n_hex
 , n.ksppinm pd_name
 , c.ksppstvl pd_value
 , n.ksppdesc pd_descr
from sys.x$ksppi n, sys.x$ksppcv c
where n.indx=c.indx
and (
   lower(n.ksppinm) || ' ' || lower(n.ksppdesc) like lower('%_optim_peek_user_binds%')
--   or lower(n.ksppdesc) like lower('"%_optim_peek_user_binds%"')
)


       NUM N_HEX
---------- -----
PD_NAME                                                                         
--------------------------------------------------------------------------------
PD_VALUE                                                                        
--------------------------------------------------------------------------------
PD_DESCR                                                                        
--------------------------------------------------------------------------------
      2180   884
_optim_peek_user_binds                                                          
TRUE                                                                            
enable peeking of user binds                                                    
                                                                                
已選擇 1 行。

--//也就是toad的execute as script 與sqlplus的指令碼描述執行有一點點不同。

3.為什麼出現雙引號，我帶入的引數並沒有雙引號，實際上檢視tpt的pd.sql指令碼很容易理解：

$ cat tpt/pd.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.

@@pd2 "%&1%"

$ cat tpt/pd2.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.

col pd_name head NAME for a56
col pd_value head VALUE for a30 WRAP
column pd_descr heading DESCRIPTION format a99 word_wrap

Prompt Show all parameters and session values from x$ksppi/x$ksppcv...

select
   n.indx + 1 num
 , to_char(n.indx + 1, 'XXXX') n_hex
 , n.ksppinm pd_name
 , c.ksppstvl pd_value
 , n.ksppdesc pd_descr
from sys.x$ksppi n, sys.x$ksppcv c
where n.indx=c.indx
and (
   lower(n.ksppinm) || ' ' || lower(n.ksppdesc) like lower('&1')
--   or lower(n.ksppdesc) like lower('&1')
);

--//pd.sql指令碼呼叫pd2.sql裡面自動加了雙引號。導致toad下這個問題出現。