[20241112]使用10tox.sql脚本遇到的问题.txt

[20241112]使用10tox.sql脚本遇到的问题.txt --//自己在使用xto10.sql脚本时，输入参数错误，我没有想到竟然执行有结果，例子： SYS@book> @ xto10 d5f8b304 10 BASE10                         BASE10 ------------------------------ ------------------------------ d5f8b304                       136591304 --//按照道理我输入一个16进制数，转换10进制，应该输入如下： SYS@book> @ xto10 d5f8b304 16 BASE16                         BASE10 ------------------------------ ------------------------------ d5f8b304                       3589845764 --//自然联想到10tox.sql脚本应该存在问题。 --//我仔细看好像该问题在10tox.sql不存在，因为带入的参数1是10进制数，参数2转换的进制数，都要参与运算，任何1个包含非数字参数 --//都会报错。 --//例子如下： SCOTT@book> @ 10tox d 16   connect by level <= ceil(log(16,d+1))                                   * ERROR at line 17: ORA-00904: "D": invalid identifier SCOTT@book> @ 10tox 13 d   connect by level <= ceil(log(d,13+1))                                * ERROR at line 17: ORA-00904: "D": invalid identifier --//但是当我执行如下时，我遇到奇怪的问题： SCOTT@book> @ 10tox 8d 16 BASE10                         BASE16 ------------------------------ ------------------------------ 8d                             8 SCOTT@book> @ 10tox 18d 16 BASE10                         BASE16 ------------------------------ ------------------------------ 18d                            12 SCOTT@book> select ceil(log(16d,18d+1)) from dual ; CEIL(LOG(16D,18D+1)) --------------------             2.0E+000 --//脚本10tox.sql的执行没有报错，也就是oracle认为8d是"合法"一个10进制数字，当作数字8. SCOTT@book> select d1 from dual ; select d1 from dual        * ERROR at line 1: ORA-00904: "D1": invalid identifier SCOTT@book> select 1a,2b,3c,4d,5e,6f,7z1 from dual ;          A          B          C         4D          E         6F         Z1 ---------- ---------- ---------- ---------- ---------- ---------- ----------          1          2          3   4.0E+000          5   6.0E+000          7 --//oracle也太奇葩了，当作数字显示时，从出现英文字符开始，当作字段名字。 --//好像不全是 d，f 又不是，似乎d表示number，f表示浮点数字，另外写一篇分析看看。 SCOTT@book> select 1z1aaaa  from dual ;     Z1AAAA ----------          1 SCOTT@book> select 1z1aaaa  from dual ;     Z1AAAA ----------          1 SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 1297973503 9fg27t16pv07z            0      98559      1388734953  4d5d80ff  2024-11-12 09:57:25    16777217 --//z1aaaa被当作显示字段，相当于实际执行的时select 1 z1aaaa  from dual ; SCOTT@book> @ expand_sql_text 9fg27t16pv07z SELECT 1 "Z1AAAA" FROM "SYS"."DUAL" "A1" PL/SQL procedure successfully completed. --//这个问题不大好解决，写代码判断有点繁琐，仅仅在以后工作中注意这个细节。 --//附上源代码： $ cat 10tox.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   10tox.sql -- Purpose:     decimal convert Ximal -- -- Author:      lfree -- -- Usage: --     @ 10tox.sql <decimal> Ximal --     convert decimal to hexadecimal --     @ 10tox.sql <decimal> --     @ 10tox.sql <decimal> '' --     @ 10tox.sql <decimal> 16 -- -------------------------------------------------------------------------------- set term off column 2 new_value 2 select null "2" from dual where 1=2; select nvl('&2',16) "2" from dual; set term on column base10 format a30 column base&&2 format a30 select to_char('&&1') base10,     listagg(         substr(           case &&2 when 32 then             '0123456789abcdfghjkmnpqrstuvwxyz'           else             '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ@_'           end,           mod(             trunc(&&1/power(&&2,level-1)),             &&2           ) + 1 ,           1         )     ) within group (order by level desc) base&&2   from dual   connect by level <= ceil(log(&&2,&&1+1)) ;