[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 http://tanelpoder.com
-- 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 http://tanelpoder.com
-- 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下这个问题出现。