​[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