DBMS_Utility學習

Comma_to_table  和 Table_to_Comma，比較有用。以下是例子：
DECLARE
  v_len BINARY_INTEGER;
  v_tab DBMS_UTILITY.UNCL_ARRAY;
  v_idx BINARY_INTEGER;
  v_str VARCHAR2(200);  
BEGIN
  DBMS_UTILITY.COMMA_TO_TABLE (
    'a,b,c',  --每個元素必須符合Oracle命名規約，即：不能以數字開頭
    v_len,
    v_tab);
  
   v_idx := v_tab.FIRST;
   WHILE(v_idx IS NOT NULL) LOOP
      dbms_output.put_line(v_tab(v_idx));
      v_idx := v_tab.NEXT(v_idx);
   END LOOP;
   
   dbms_utility.Table_to_Comma(v_tab, v_len, v_str);
   dbms_output.put_line(v_str);
END;

a
b
c

a,b,c

取得資料庫各物件之間依賴關係 - dbms_utility.GET_DEPENDENCY
CREATE VIEW SAL_SUMMARY_BY_DEPT AS SELECT DEPTNO, SUM(SAL) SAL FROM emp GROUP BY DEPTNO;
EXEC dbms_utility.GET_DEPENDENCY('TABLE','SCOTT','EMP');
DEPENDENCIES ON SCOTT.EMP
------------------------------------------------------------------
*TABLE SCOTT.EMP()
*   VIEW SCOTT.SAL_SUMMARY_BY_DEPT()
也可以實用User_Dependencies(dba_dependencies, all_dependencies)來獲取依賴關係
SELECT * FROM user_dependencies WHERE REFERENCED_NAME = 'EMP';

計算雜湊值 - dbms_utility_get_hash_value
To get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the hash_size value. 
Using a power of 2 for the hash_size parameter works best.

SELECT DBMS_UTILITY.GET_HASH_VALUE('A', 0,2048) hash_value FROM dual;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1062165/，如需轉載，請註明出處，否則將追究法律責任。