[PL/SQL] Global Temporary Tables - 用途 & 做法

近來碰到一些問題就是在報表開發過程中, 因應需求會撰寫很多SQL而這些SQL可能抓取來自不同的Oracle Database的資料. 但是可能報表要一樣的結果但是不同的人寫就會出現不同的結果, 問題是正確答案只有一個啊. 原因就在前端複雜的schema 設計&流程邏輯, 常常因為開發人員的認知不同而出現落差.

這時如何在架構上讓資料抓取(Data Extraction)和報表呈現(Report Presentation)做有效的區隔就很重要(這邊注重的是資料庫端, 在應用端已有很多方案像Struts等). 就資料庫端而言, 有一個Oracle的技術 'Global Temporary Tables(GTT)' 就有探討的價值. 簡單的說, GTT可以將複雜的資料抓取邏輯隱藏並將結果用報表呈現人員習慣的Table 方式呈現. 另一個重點是該Table的資料只在特定Session中存在, 如果Session結束, 資料即消失, 但是 Table本身還是存在.

下面就來做個測試吧!

SQL> CREATE GLOBAL TEMPORARY TABLE worktable (x NUMBER(3));

Table created.

這時可以在行開啟另一個SQLPlus session, 可以看到方才另一個Session 建的GCC, 並且可以做DML, 如下.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

    X
----------
    1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

no rows selected

ㄟ!見鬼了, 怎麼一 Commit資料就不見了, 原因在見下面建GCC的參數. 建立GCC時,
用'On Commit Preserve Rows', Commit完後, 資料便不會消失.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable

2  (x NUMBER(3))
3  ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

    X
----------
    1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

    X
----------
    1

這時如果從其他Sesseion看相同的GCC是看不到資料的, 如下.

SQL> SELECT * FROM worktable;

no rows selected

再試試 truncate 吧!

SQL> INSERT INTO worktable (x) VALUES (2)

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

    X
----------
    2

SQL> TRUNCATE TABLE worktable;

Table truncated.

SQL> SELECT * FROM worktable;

no rows selected

SQL> commit;

Commit complete.

下Truncate指令一樣只會把Session內的資料清除.

[PL/SQL] 動手開發PL/SQL專案前的第一步

PL/SQL, 這個在 Oracle 世界中最適合做資料處裡的開發工具. 素以執行效率佳, 和 Oracle 整合度高, 開發簡單廣受開發人員喜愛. 但是開發簡單後面的涵義是: 程式開發的品質如何控制? 品質涵蓋的層面很廣, 符合使用者需求, 效能好和程式的可維護性! 這一期的Oracle雜誌，PL/SQL Guru - Steven Feuerstein 發表一篇文章談到,一個PL/SQL開發團隊從哪幾個點切入可以有效控制程式品質和達到使用者需求, 在第一時間就將後續的開發架構架構好!

1. Correct:符合使用者需求
這裡談到的是正確性，所謂正確性就是正確的達到使用者的需求，當然一大半是需求＆系統分析的事情，這裡Oracle管不著. 但是使用者的需求瞭解後，開發人員如何確定程式有做到該做的事呢？ 單元測試在當中扮演一個重要的角色，多數開發團隊會要求開發人員在程式開發完後提交單元測試報告，但是單元測試多由開發人員自行定劇本自行測試，涵蓋度和正確度如何無從得知？這時所謂的自動化測試（Automated regression test)就是一個重要的工具. 坊間常見的工具有 utPLSQL, PLUTO, PL/Unit, DBFit, Quest Code Tester. 這些工具的產品網址都在連接中，另外除了最後一個是要錢以外，其他看了都是Open Source的，大家有興趣可以試試！ 哪天也要自己的team試試，再將結果PO出來！

2. Fast Enough:效能好

• Optimizing Compiler
  可參考以下文件有詳細說明. 在10G 後就有這項功能, 主要目的在提升PL/SQL在 runtime的效能. 而做法則是在 compile時, 使用不同的優化(Optimization)手段. 它是透過以下參數去控制,"PLSQL_OPTIMIZE_LEVEL".
• Bulk processing
  透過此工具, 將原始一筆一筆的處理方式轉換為多筆批次處理. 這篇文章對效能的提升有一些實驗和驗證(該文章號稱有三十倍快).
  
• Pipelined table function
  允許不同的 PL/SQL 間的資料傳遞不必受限於僅只傳遞參數而可以傳遞 ResultSet並且不用額外建立Table. 尤其在 ETL程式裡, 資料不必透過實體Table處理而是直接透過 Pipelined table傳遞, 對效能有一定的助益. 可以參考這篇文章.
  
• Function result cache
  可以參考這篇文章, 有完整的介紹. 簡短的說, function result cache的好處在, 當主程式呼叫另外的 PL/SQL function時, 這個 PL/SQL function可能含 SQL. 這會造成所謂的 Context Switch和 IO的增加. 因此 function result cache可以透過語法(Create function XXX (I1 varchar2) return varchar2 Result_Cache is) 設定將該 PL/SQL function的結果快取(Cached)起來, 加快處理速度.
• Native compilation
  參考"Optimizing Compiler"的文章, 所謂 Native Compilation就是將PL/SQL編譯成 C語言, 直接和Oracle整合, 效能較佳.
  

3. Maintainable:程式的可維護性

• Naming conventions & Syntax standards
  變數和程式名稱的命名規則應該都要有文件作規範.
  
• Writing SQL in PL/SQL
  SQL的產生應該有一個獨立的Data Access Layer而不是任由開發人員在PL/SQL中 Hardcode. 透過這個Layer, 將SQL的複雜邏輯隱藏起來,以利後續的維護和開發.
  
• Error Management
  統一的 Error Handling 機制和模組.
  
• Application Tracing
  統一的tracing 機制和模組,可參考文章.
  
• Version control and backups
  版本備份, 我的團隊是用 Daily 從 Metadata中備份PL/SQL至檔案中, 以利未來查詢之用.

上面談的規矩都是規矩, 開發人員是否遵守又是另外一件事情?這時候適時的 Code review是有必要的,確認開發人員有確實遵守規定.
在你組成PL/SQL開發團隊前, 可以將上述的建議當成 Check List來確認開發團隊是否可以正確開發出快速且維護性高的系統.

[PL/SQL] Oracle SQL 批次Update，如何確保完整性?

Oracle SQL 批次Update如何確保完整性，對很多人來說是一件很麻煩的事情.當一個Update指令失敗，程式會立即跳離並Rollback已經更新的資料. 但多數的狀況是，造成失敗的資料只是上千筆資料中的一兩筆而已，其他沒有問題的更新應該還是要繼續執行. 這時要如何處理呢？Oracle magazine 3-4月號提供了三個Solution供大家參考. 大家看看吧！

1. Nested Block: 這是一般人做常用的作法，就是將Update指令寫成Cursor，一筆一筆擷取處理 後再做單筆的更新，並將整個過程用Begin-Exception-End包裹起來，當有Update失敗的情形 時用Exception處理，以確保後續的更新得以繼續進行. 這樣的好處是邏輯很清楚，有問題時很好處理但是問題來啦，當資料量大時，效能是必有問題. 另一個好處是多數的Oracle版本都可支援此作法.

2. ForAll Save Exceptions: 這個作法就是針對第一個方式的效能問題作改善.
利用 Bulk Collect Into的功能將資料從Cursor放置入變數後， 再做批次資料更新. 更新時多一個 'Save Exceptions'的指令，這時程式會將過程中的更新錯誤資料存入於宣告區的Exception變數中，之後再將其存入Table以供後續Trouble shooting之用. 使用'Save Exceptions'指令，Oracle會將已執行的DML Rollback並將錯誤訊息存入SQL％Bulk_Exceptions中，並繼續執行下一個指令. 但是這時被Rollback的資料會包含同一個Statement已經執行的Update.
這一段比較複雜，節錄原文裡的部分Source code以供說明.
從Code裡面看到作者是用他自行開發的QEM(PL/SQL Error Management Framework) 來記錄Exception中的詳細資訊.

======================================================================
CREATE OR REPLACE PROCEDURE change_salary_for (
dept_in IN employees.department_id%TYPE
, pct_increase_in IN NUMBER
, fetch_limit_in IN PLS_INTEGER
)
IS
bulk_errors exception; PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

CURSOR employees_cur
IS
SELECT employee_id, salary
FROM employees
WHERE department_id = dept_in;

TYPE employee_tt
IS
TABLE OF employees.employee_id%TYPE
INDEX BY BINARY_INTEGER;

employee_ids employee_tt;

TYPE salary_tt
IS
TABLE OF employees.salary%TYPE
INDEX BY BINARY_INTEGER;

salaries salary_tt;
BEGIN
OPEN employees_cur;

LOOP
FETCH employees_cur
BULK COLLECT INTO employee_ids, salaries
LIMIT fetch_limit_in;

FOR indx IN 1 .. employee_ids.COUNT
LOOP
salaries (indx) := compensation_rules.adjusted_compensation (
employee_id_in => employee_ids (indx)
, pct_increase_in => pct_increase_in
);
END LOOP;

FORALL indx IN 1 .. employee_ids.COUNT
SAVE EXCEPTIONS
UPDATE employees
SET salary = salaries (indx)
WHERE employee_id = employee_ids (indx);
EXIT WHEN employees_cur%NOTFOUND;
END LOOP;

EXCEPTION
WHEN bulk_errors
THEN
FOR indx IN 1 .. sql%BULK_EXCEPTIONS.COUNT
LOOP
q$error_manager.register_error (
error_code_in => sql%BULK_EXCEPTIONS (indx).ERROR_CODE
, name1_in => 'EMPLOYEEE_ID'
, value1_in => employee_ids (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
, name2_in => 'PCT_INCREASE'
, value2_in => pct_increase_in
, name3_in => 'NEW_SALARY'
, value3_in => salaries (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
);
END LOOP;

========================================================================

3.DML Error Logging: 這一段是一個新的作法，先用以下Package DBMS_ERRORLOG為你要處理的Table建立一個相對應的Error Log Table.

BEGIN
 DBMS_ERRLOG.create_error_log (
    dml_table_name   => 'EMPLOYEES'
  , skip_unsupported => TRUE);
END;

執行完後會有一個新Table出現 "ERR$_EMPLOYEES". 這時程式如第二個方式的Bulk Collect
Into的方式但是Exception的處理使用不同的作法. 如以下的程式碼，當有Exception發生於
Update的時候，程式碼裡面將上"LOG ERRORS REJECT LIMIT UNLIMITED;"指令，再呼叫副程式
Log_Error，這時就可以使用方才建立的Error Table將錯誤輸出而且不會影響後續的DML處理.
這裡的Rollback就是根據每一筆記錄不同於第二個作法的是每一個Statement.

======================================================================
PROCEDURE change_salary_for (
 dept_in  IN employees.department_id%TYPE
  , pct_increase_in  IN NUMBER
 , fetch_limit_in    IN PLS_INTEGER
)
IS
 bulk_errors exception;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

 CURSOR employees_cur
 IS
    SELECT employee_id, salary FROM employees WHERE department_id = dept_in;

 TYPE employee_tt
 IS
    TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER;

 employee_ids   employee_tt;

 TYPE salary_tt
 IS
    TABLE OF employees.salary%TYPE INDEX BY BINARY_INTEGER;

 salaries       salary_tt;

 PROCEDURE log_errors
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      FOR error_rec IN (SELECT * FROM err$_employees)
      LOOP
         q$error_manager.register_error (
            error_code_in   => error_rec.ora_err_number$
          , name1_in        => 'EMPLOYEEE_ID'
          , value1_in        => error_rec.employee_id
          , name2_in        => 'PCT_INCREASE'
          , value2_in        => pct_increase_in
          , name3_in        => 'NEW_SALARY'
          , value3_in        => error_rec.salary
         );
      END LOOP;

      DELETE FROM err$_employees;

      COMMIT;
   END log_errors;
BEGIN
 OPEN employees_cur;

 LOOP
    FETCH employees_cur
       BULK COLLECT INTO employee_ids, salaries LIMIT fetch_limit_in;

    FOR indx IN 1 .. employee_ids.COUNT
    LOOP
       salaries (indx) := compensation_rules.adjusted_compensation (
          employee_id_in    => employee_ids (indx)
        , pct_increase_in    => pct_increase_in
       );
    END LOOP;

    FORALL indx IN 1 .. employee_ids.COUNT()
           UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx)
       LOG ERRORS REJECT LIMIT UNLIMITED;

    log_errors ();

    EXIT WHEN employee_ids.COUNT() <>======================================================================

原文引用，請參照Link.

[PL/SQL] Oracle 11G SQL/PLSQL New Features

自從Oracle 11g 2007年 release以來, 不斷有一些新的功能被提出討論. 11G的重點在DBA管理工作的自動化, 這使得DBA有時間去做一些更有附加價值的事情而不是聚焦在重複性的資料庫維護動作. 而對開發人員, SQL和PL/SQL則是另外一個重點, 本文介紹一些新的功能和筆者的想法, 順道將網路上其他人對這些新功能的介紹做一些整理.

1. PL/SQL的語法： 新增 "Continue" 關鍵字, 讓它更像C語言. 在迴圈裡面使用"Continue",
讓程式跳過Continue以下的指令而執行下一個迴圈，如此程式碼裡可以少看到很
多"GOTO". 下面是一個例子.

begin
for i in 1..3
loop
dbms_output.put_line(』i=』||to_char(i));
if ( i = 2 )
then
continue;
end if;
dbms_output.put_line(』Only if i is not equal to 2′);
end loop;
end;

結果會長成

i=1 Only if i is not equal to 2
i=2
i=3 Only if i is not equal to 2    

2. Stored procedure 的編譯和狀態控制改善

- 除了"Enabled"和"Invalid"以外，多一種Stored Procedure的狀態就是"Disabled".
 主要是資料庫管理面上的需要，舉例, 當有Schema改變，程式還沒有要跟著改變時，
 可以將程式狀態先行換為'Disabled'，此時'Invalid'還是屬於異常狀態，需要特別
 處理，而'Disabled'則可以待開發人員確認後再處理.
- Native PL/SQL compiling
 所謂 Native就是PL/SQL在編譯後直接產生機器碼並儲存在 System tablespace, 在
 執行時也直接進入 shared memory. 而無需透過C編譯器,即所謂的 Intrepreted模
 式. 其實此功能在10G即已存在只是聽說在RAC下會有問題,所以11G作了一些改善.
- 減少PL/SQL stored procedure 變成invalidatied的機制
 減少因為DDL而導致程式變成 Invalid的機會.

3. 新的轉置SQL"PIVOT"語法
在Oracle要作轉置基本上可行但是要對SQL動很多手腳, 下面就是一個實例,可以看到SQL裡
面用了一些max, decode,sum等指令,有點小麻煩.

select sum("10") "10",sum("20") "20",
sum("30") "30",sum("40") "40"
from(
select max(decode(department_id,10,salary,null)) "10",
max(decode(department_id,20,salary,null)) "20",
max(decode(department_id,30,salary,null)) "30",
max(decode(department_id,40,salary,null)) "40"
from employees
group by department_id
) ;

到了11G你可以這樣做, SQL看起有邏輯而且簡單多了!

select *
from (select department_id,sum(salary) salary
from employees
where department_id > 0
group by department_id)
pivot (sum(salary)
for department_id in (10,20,30,40)
);

4. SQL/PLSQL 的延展性 (Scalibility) : 新的SQL hint /*+result_cache*/   
  在SQL中加入此 Hint, 讓SQL的結果被Cached在Memory中而不是Cached讀取的資料在
buffer cache. 針對執行頻繁而且結果較固定的SQL, 使用此HINT對效能應該很有幫助.

5.  改善optimizer收集statistics的效能
Oracle 11g改善dbms_stats的效能. DBA通常會定期執行此程式收集和更新Statistics,
這個動作多多少少會對database的效能有一點副作用, 這一個改善應可將此副作用降低.

6. SQL execution Plan Management
Oracle 11g 的SPM允許你更進一步控制SQL的 Execution plan,避免系統環境出現異動時,
Execution Plan跑掉.請參閱Link.

參考文件:

http://www.dba-oracle.com/oracle11g/oracle_11g_new_features.htm

http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#continue_statement

[PL/SQL] 讓人吐血的ORA-02046（Distributed Transaction)

今天中午，客訴事件又添一件，人客又在抱怨資料沒產生，給客人看的網站上查不到想看的資料！天啊，這已是這個月來的第N起啦！ 工程師查半天，程式就是跑不過，ERROR LOG中滿
滿的 ORA-02046. 這時看起來大勢已去，怒火中燒的客戶又在樓下等資料，腦中滿是問號，突
然靈機一動，將程式移到測試環境再去存取前端ERP的資料（這隻程式是一支ETL，從後端
Data Warehouse資料庫去存取前端ERP的資料庫），莫名其妙的程式就突然跑過了，這時趕緊
將產生好的資料搬到正是環境中，好跟客戶交差（為甚麼測試環境可以過，正式環境不行，至
今未解，但公司的環境因為DBA要求開發人員先行測試即將Upgrade的Oracle版本，所以兩個
環境的版本不一樣，測試環境是9i，正式環境是8i).

但是到底出了甚麼是啊？下面是官方說法：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

ORA-02046:	distributed transaction already begun
Cause:	internal error or error in external transaction manager. A server session received a begin_tran RPC before finishing with a previous distributed tran.
Action:	none

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
看到了嗎？ Action是'None'，在Google一下，網路上幾乎沒有針對這個問題有提供相關解答.
唯一有正面回應的是，這個ERROR都發生在跨DB存取的動作，可以將遠端要存取的TABLE先
複製回LOCAL端，免除掉DBLINK的動作. 但這個建議相信大多人不會採用，因為就以本公司
為例，相關ETL程式有數百隻，每支都這樣改還得了，況且複製資料還要整體考量Timing的
問題，牽扯實在太大.

那Metalink呢？有沒有解答啊？引用一下http://www.orafaq.com/forum/t/72797/0/的問答
，看看內文提到，metalink也沒有太多解答.

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝=＝＝＝＝＝
Actually, quite a few people have sent this error into Oracle, and there are a number of cases
on Metalink about this error, none of them provide much elucidation. So actually, insisting he contact Oracle might not actually do too much good. If so, being able to provide them with a
trace file may help. This is an error that often doesn't throw any alerts.

Basically, what has happened is that the specific server session has received a begin_tran
rpc BEFORE it has finished with a previous distributed transaction.

There is no real clear, set pattern to the conditions that create this error message, and in
many cases, there doesn't seem to be a workaround either.

In one case, the person's application was in autocommit mode and turning that off resolved
the issue. In one case, the error occured in SQLPlus, but not when running the same code in OWB (where the code had been created). In another case, a person said they had increased DISTRIBUTED_TRANSACTION parameter and had it stop. In another case, the problem occured in a jdbc application, but not when the same code was run from SQLPlus.

It has been associated with select statements going from a 9.2.x database to an 8.1.x database.
In most cases, there is a dblink involved, one way or another (buried in a view or somehow). Some reports asscoiate it with insert statements.

In my case, I have been told to write a pl/sql procedure to replace Oracle's multimaster replication. As expected it does an update on a table on a remote database. This update statement causes the error to occur. The earlier version of this procedure, which is as similar
as possible except that it does not use bulk collect to gather the data, does not appear to cause the error to occur.

This is a difficult error and not a lot of information available from metalink. It seems to occur right in the middle of a distributed transaction.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

那怎麼辦呢？ 工程師嘛，任務就是解決問題，誓死達成任務. 好吧！那就回過頭把問題在定
義得更清楚一點！ 底下就是更清楚的問題定義！

問題定義：
一隻每四小時執行一次的ETL程式，每次執行處理的資料量大概是三百筆上下. 主Crusor的
SQL和處理過程中都會不斷透過Database A存取Database B和Database C，最後將結果存於
Database A. 但自從三天前，每次執行此ETL都會撞到ORA-02046的問題，撞到的點都在執
行某副程式時發生，該副程式也是透過Database LINK存取Database B.

問題模擬：
將ETL由背景執行轉成前景執行，執行完後，一樣的ORA-02046出現.
先用以下SQL看看執行前後的Cursor狀態，沒有異常，遠小於Database的設定: Open cursor（Session中可以使用的Cursor, 目前設'10000').
=======================================
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || 'cursor' ||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              26
opened cursors current                  9
session cursor cache hits               0
session cursor cache count             13
cursor authentications                  1
=======================================

另外一個常見的 Distributed database的問題是參數 'Open Link', session裡所使用的DB
Link總數，看一下目前設定，這個就更不可能超過啦. 重點是若是前述兩個問題，錯誤訊息
也不應該是Ora-02046.

但是同時間也發現，ORA-02046一旦出現，後續所有跨該 DB Link的SQL都會碰到一樣的錯誤訊
息.這時問題既然存在於DB Link，那就看一下 V$DBlink，果然，此View底下的'欄位
'Open Cursors'的值一旦是慢慢變大成9時，討人厭的Ora-02046開始層出不窮. 這個欄位
Oracle的定義是
==========================================================
Whether there are open cursors for the database link
===========================================================

有看沒有懂吧！為什麼這個值一變成9，系統就會拋出Ora-02046，不解！遍尋網路，也沒有更
進一步的解釋.

這時下個指令將DB Link關閉,Ora-02046不再出現.相關跨DB LINK的SQL也可以繼續執行.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
ALTER SESSION CLOSE database link A;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

但是問題到底怎麼解決啊？看看最近一次程式的Change Log,是在主Cursor的SQL中加了一個
Function，此Function 是存在於Database B，再從Database B中去存取Database A. 先試著
將此Function搬到Database A後，SQL再直接執行呼叫此Function，問題居然解決了. 天啊！
到底是撞倒哪一個參數或限制，至今無解，但和之前談的作法一樣的是:
既然問題發生在 Distributed Transactions，就試著將Distributed Transaction的需求降低
，將跨DB的Function移至Local端.問題暫時解決，未來有機會再將此問題量化來看，看看有沒
有機會看得更清楚問題所在.

也希望ORACLE大人行行好，將此一問題講得更清楚一點，不要只放一個Action='None'，這可
讓很多人少掉很多白頭髮耶！

[PL/SQL] Logging Framework - Log 4 PL/SQL

相信JAVA的愛好者一定聽過Log4J這個Logging的架構(Framework). 程式開發和系統導入&使用的過程中, Logging是一個TroubleShooting必備的工具, 然而對PL/SQL而言, Oracle 並沒有提供相關的架構&功能, Logging大概都是自行開發的程式, 有將LOGGING模組化還是好的, 大部分我想是Hardcode,想記Log就記Log, 不同程式還記在不同TABLE,甚至高興還記在外部的Text file. 等到系統出問題要查LOG時才發現Log有記但會漏, 那時在跟老闆報告說找不到Bug在哪, 肯定被ㄞㄧ頓排頭!

所以Logging可以說是養兵千日用在一時的好幫手, 承平時候(User 沒有抱怨), Logging被視為是系統額外的負擔, 不管是程式執行時的Overhead 或 儲存空間的overhead. 但是一旦使用者抱怨來了發現資料有誤, 開發人員無論如何重建案發現場都無法模擬出這個BUG時, Logging 的資料又變成彌足珍貴. 這時一個Logging的底層(Framework)就可以幫助開發人員, 將這項工作綁在程式開發中又不會影響程式的架構. 一般而言, 一個好的LOGGING架構應該涵括以下幾點:
- 易於導入
- Log的紀錄必須能夠和程式邏輯的COMMIT/Rollback切隔開.(總不能程式一下Rollback指令連以記錄的LOG都Rollback掉)
- Log的紀錄必須能夠根據DEBUG的需求分成不同Level, 不同Level紀錄的LOG也不一樣.
- 系統的 Overhead 要低.

SourceForge 就有一項專案 Log 4 PL/SQL, 我們就來玩玩吧!!!

1. 安裝篇
自以下網頁 http://log4plsql.sourceforge.net/下載.後解壓縮, 照安裝說明執行批次檔然後就安裝成功, 哈哈, 別傻了, 基本上 open source的作者都是 Geek, 高手中的高手, 但這些Geek喜歡寫程式就是不喜歡寫文件. 所以最後還是把一堆 .SQL 檔案一個一個在TOAD中執行, 一個一個DEBUG才完成. 還好不算難裝啦.

2. 使用篇

-- 先試著呼叫 Log 4 PL/SQL 所建立的 Package(PLOG)的Procedure(Info).
Exec ULOG.PLOG.info ('mess info');

-- 第二個例子, 是著建立一個Procedure 在其中去呼叫-PLOG, 基本上要在哪個位置埋LOG, 要開發人員自己決定.
create or replace procedure TestProc is
cpt number;
begin
ulog.plog.info('this select raise ORA-01403:No Data Found');
select id into cpt from ulog.tlog where id = -1;
exception
when others then
ulog.plog.error; -- default message is SQLCODE SQLERRM
end;/

exec HR.TestProc

-- 最後看一下結果吧!
-- 從結果中, 可見記錄到的Error message.

select * from ulog.vlog;

ID	LDATE	LHSECS	LLEVEL	LSECTION	LTEXTE	LUSER
1	2009/1/8 下午 11:19:13	1121152	30	block.HR.TESTPROC	SQLCODE:100 SQLERRM:ORA-01403: 找不到資料	SYS

3. 進階篇

• 至於埋的LOG要不要作用就要看Logging level的設定, Logging level 有分以下幾種

  · isDebugEnabled

  · isInfoEnabled

  · isWarnEnabled

  · isErrorEnabled

  · isFatalEnabled

透過程式來控制什麼LEVEL要記什麼樣的 Log, 也就是將 IF - End If 加在塞LOG之前.

• 設定不一樣的啟動參數, 此工具有提供一些工具程式去改變參數, 如下
  pCTX PLOG.LOG_CTX := PLOG.init (pALERT => TRUE);

4. 架構篇

• Log 4 PL/SQL 可以紀錄LOG在以下標的物(Destination)

1. Table in Oracle Datablase
2. Oracle Datablase alert.log file
3. Oracle Datablase trace file
4. Standard output ·
5. By log4J::Log4JbackgroundProcess(JDBC, SMTP,...)
   

• 基本架構如下:

Log 4 PL/SQL 基本上是 Log 4J的延伸, 所以 Log4J的功能Log 4 PL/SQL 也可以使用.

[PL/SQL] PL/SQL 也可以很' 模組'

看過也寫過無數的PL/SQL code, 相對於我所接觸的其他語言(VB, Fortran), PL/SQL是最容易被輕忽而不做模組化的一種語言 (原因不清楚, 但我猜是PL/SQL 的 IDE不像其他語言的IDE成熟). 這也造成開發人員在接手其他人的舊程式時, 往往面對的是如意大麵(Pasta)似的程式碼, 如果連程式碼都看沒有, 更遑論後續的品質. 而改善品質, 大部分人會想到模組化, 本文就對PL/SQL的模組化, 提出一些想法和實際的作法供大家參考.

PL/SQL 模組化的三個作法:
1. 模組的公用度高 --> 將較常用的共用的程式包在同一個Package裡面, 以供其他程式呼叫.
2. 模組的公用度普通--> 如果Procedure(呼叫者)寫在 Package裡面, 則將共用的程式寫在
PACKAGE本身, 以供同一PACKAGE的其他程式呼叫. 當然若該FUNCTION宣告成Public,
則PACKAGE以外的程式亦可呼叫.
3. 模組的公用度低--> 是本文的重點, 就是若是該FUNCTION只有PROCEDURE本身會用用到.
那透過Sub-Function/Sub-Procedure 來完成, 會是更方便而且讓程式的層次分得更清楚.
以下就用實際程式來說明!



這樣子程式碼看起來是不是排列比較有邏輯而起維護性較高!

模組化不是一個口號而是可以落實的端看開發團隊如何運用囉!