Oracle 預存程序大型 SQL 除錯技巧：DBMS_OUTPUT 列印動態 SQL 內容

編寫預存程序時，動態組合的大型 SQL (如超過 32767 字元、多表 Join、複雜條件) 無法在 PL/SQL Developer 等工具的「Test」視窗直接檢視。本文提供一招實用技巧：利用 DBMS_OUTPUT.PUT_LINE 將組合完成的 SQL 字串輸出至除錯視窗，快速驗證語法與邏輯。

核心做法

CREATE OR REPLACE PROCEDURE proc_complex_query IS
  v_sql  CLOB;  -- 或 VARCHAR2(32767)
BEGIN
  -- 動態組合 SQL
  v_sql := 'SELECT ... FROM ... WHERE ...';  -- 複雜邏輯拼接

  -- 【關鍵步驟】輸出完整 SQL 供檢視
  DBMS_OUTPUT.PUT_LINE('=== Generated SQL ===');
  DBMS_OUTPUT.PUT_LINE(v_sql);
  DBMS_OUTPUT.PUT_LINE('=====================');

  -- 執行動態 SQL (範例)
  -- EXECUTE IMMEDIATE v_sql;
  -- 或 OPEN cursor FOR v_sql;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
    RAISE;
END;
/

操作步驟 (以 PL/SQL Developer 為例)

1. 在程序碼中插入 DBMS_OUTPUT.PUT_LINE(...) 輸出 SQL 變數。
2. 編譯程序 (無錯誤)。
3. 在 Object Browser 右鍵程序 → Test (或 Run)。
4. 執行完成後，切換到 DBMS Output 視窗 (View → DBMS Output)，即可看到完整 SQL 字串。
5. 複製該 SQL 到 SQL Window 執行、解析執行計畫、調優。

進階技巧

• 大型 SQL 分段輸出：DBMS_OUTPUT 單行限制 32767 字元 (12c+ 可至 1M)，超長 SQL 需分段：

  FOR i IN 1..CEIL(LENGTH(v_sql)/32000) LOOP
    DBMS_OUTPUT.PUT_LINE(SUBSTR(v_sql, (i-1)*32000+1, 32000));
  END LOOP;

• 加上參數值：若使用 Bind Variable，同步輸出參數名與值，便於重現。

  DBMS_OUTPUT.PUT_LINE('Bind: p_dept_id=' || p_dept_id || ', p_date=' || p_date);

• 關閉輸出：正式部署前記得移除或以 DEBUG_FLAG 控制，避免效能與安全風險。

替代方案對比

結語

「印出 SQL 看內容」是動態 SQL 開發最朴素也最有效的除錯手法。善用 DBMS_OUTPUT，讓看不見的 SQL 變可見，修正語法錯誤、優化執行計畫、驗證參數綁定，事半功倍。