Oracle 存儲過程檢查表是否存在：動態查詢 ALL_TABLES 與自定義異常實戰

Oracle 存儲過程檢查表是否存在

批次排程、ETL 作業常需在執行前確認目標表存在，避免報錯中斷。本文提供可直接部署的檢查過程，支援「Schema.表名」或「表名」（全庫搜尋），不存在時拋出自定義異常 -20010，供上層捕捉處理（如等待重試、發送告警）。

完整過程代碼

CREATE OR REPLACE PROCEDURE p_check_table (
  p_table_name IN VARCHAR2  -- 格式: 'SCHEMA.TABLE' 或 'TABLE'
) AS
  v_cnt       NUMBER;
  v_full_name VARCHAR2(200) := UPPER(p_table_name);
  v_owner     VARCHAR2(100);
  v_table     VARCHAR2(200);
  v_pos       NUMBER;
BEGIN
  -- 解析 Schema 與表名
  v_pos := INSTR(v_full_name, '.', 1, 1);
  IF v_pos > 0 THEN
    v_owner := SUBSTR(v_full_name, 1, v_pos - 1);
    v_table := SUBSTR(v_full_name, v_pos + 1);
  ELSE
    v_owner := NULL;
    v_table := v_full_name;
  END IF;

  -- 查詢 ALL_TABLES (當前使用者可見的所有表)
  IF v_owner IS NULL THEN
    SELECT COUNT(*) INTO v_cnt FROM all_tables WHERE table_name = v_table;
  ELSE
    SELECT COUNT(*) INTO v_cnt
    FROM all_tables
    WHERE table_name = v_table AND owner = v_owner;
  END IF;

  -- 不存在則拋出自定義異常
  IF v_cnt = 0 THEN
    RAISE_APPLICATION_ERROR(-20010, p_table_name || ' does not exist, please wait...');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    -- 可選：記錄日誌後再拋出
    RAISE;
END p_check_table;
/

使用範例

-- 指定 Schema
BEGIN
  p_check_table('SCOTT.EMP');
  DBMS_OUTPUT.PUT_LINE('Table exists, proceed...');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    -- 這裡可加入 DBMS_LOCK.SLEEP(60) 等待重試邏輯
END;
/

-- 僅表名 (跨 Schema 搜尋)
BEGIN
  p_check_table('EMP');
END;
/

設計考量

進階：整合重試邏輯的封裝過程

CREATE OR REPLACE PROCEDURE p_wait_table (
  p_table_name IN VARCHAR2,
  p_max_wait_sec IN NUMBER DEFAULT 300,
  p_interval_sec IN NUMBER DEFAULT 10
) AS
  v_elapsed NUMBER := 0;
BEGIN
  LOOP
    BEGIN
      p_check_table(p_table_name);
      RETURN;  -- 存在則直接返回
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -20010 THEN
          IF v_elapsed >= p_max_wait_sec THEN
            RAISE_APPLICATION_ERROR(-20011, 'Timeout waiting for table ' || p_table_name);
          END IF;
          DBMS_LOCK.SLEEP(p_interval_sec);
          v_elapsed := v_elapsed + p_interval_sec;
        ELSE
          RAISE;  -- 非預期錯誤向上拋出
        END IF;
    END;
  END LOOP;
END p_wait_table;
/

排程開頭呼叫 p_wait_table('STG.ORDER_DAILY', 600, 30); 即可優雅等待上游建表完成。

小結

以 ALL_TABLES + RAISE_APPLICATION_ERROR 建立可重用的表存在性檢查元件，再封裝重試等待邏輯，讓批次作業具備自愈與可觀測能力，是穩健 ETL 架構的基石之一。