[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 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下这个问题出现。