[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))
;