​[20190716]十進位制轉換其他進位制指令碼.txt

[20190716]十進位制轉換其他進位制指令碼.txt

$ cat 10x.sql
column base10 format 99999999999999999999
column base&&2 format a30

select &&1 base10,
    listagg(
        substr(
          case &&2 when 32 then
            '0123456789abcdfghjkmnpqrstuvwxyz'
          else
            '0123456789abcdefghjkmnpqrstuvwxyz'
          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))
;

--//注意轉換32進位制碼錶裡面沒有e,i,o,l 4個字元。簡單測試看看。
--//說是支援其它進位制，實際上主要用於2，8，16，32進位制轉換。

1.環境：
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試:
SCOTT@test01p> @ 10x 16 8
        BASE10 BASE8
-------------- ------------------------------
            16 20

SCOTT@test01p> @ 10x 15 2
        BASE10 BASE2
-------------- -------------------
            15 1111

SCOTT@test01p> select sysdate from dual ;
SYSDATE
-------------------
2019-07-18 20:54:18

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f0wzs9nc663bn, child number 0
-------------------------------------
select sysdate from dual
Plan hash value: 1388734953
------------------------------------------------------------------
| Id  | Operation        | Name | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--//sql_id='f0wzs9nc663bn'.驗證看看。

SCOTT@test01p> select * from v$db_object_cache where name like 'select sysdate from dual '
  2  @ prxx
==============================
OWNER                         :
NAME                          : select sysdate from dual
DB_LINK                       :
NAMESPACE                     : SQL AREA
TYPE                          : CURSOR
SHARABLE_MEM                  : 12120
LOADS                         : 2
EXECUTIONS                    : 1
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 0
INVALIDATIONS                 : 0
HASH_VALUE                    : 409144692
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : VALID
TIMESTAMP                     : 2019-07-18/20:54:18
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 2
PINNED_TOTAL                  : 4
PROPERTY                      :
FULL_HASH_VALUE               : 2f36775c951d24fee073f84d18630d74
CON_ID                        : 3
CON_NAME                      :
ADDR                          : 000007FEFFE260C8
EDITION                       :

--//hash_value=409144692
SCOTT@test01p> @ 10x 409144692 16
        BASE10 BASE16
-------------- ------------------------------
     409144692 18630d74

SCOTT@test01p> @ 10x 409144692 32
        BASE10 BASE32
-------------- ------------------------------
     409144692 c663bn

--//與sql_id='f0wzs9nc663bn'的後面6位一致。實際上sql_id是FULL_HASH_VALUE 16進位制的後16位(e073f84d18630d74)就是sql_id。
--//只不過前者使用16位表示，後者32位表示。

--//0xe073f84d18630d74=16173543696822898036
SCOTT@test01p> @ 10x 16173543696822898036 32
               BASE10 BASE32
--------------------- ------------------------------
 16173543696822898036 f0wzs9nc663bn

--//與前面的sql_id完成對上。

3.附上dpc，prxx指令碼：
--//dpc.sql

set verify off
--select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost partition'));
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));

prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt

--//prxx.sql,注意前面有1個點可不是多餘的。
.

set termout off
def _pr_tmpfile=d:\tmp\pr.out

store set &_pr_tmpfile.set replace
set termout on

set serverout on size 1000000 termout off echo off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==============================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      ( rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

set termout off
@&_pr_tmpfile.set

get &_pr_tmpfile nolist
host del &_pr_tmpfile
set termout on