使用大型複雜查詢從數百萬行中跟踪“無效數字錯誤”

背景

我正在修復我繼承的一個大型查詢。該查詢由 200 多行 DML 語句組成。

搜尋查詢數據庫，將數據限制為一個月。大多數月份都可以正常工作，但有幾個月會產生ORA-01722: Invalid Number錯誤。

我懷疑問題是以下之一：

1. 定義為 VARCHAR 的列包含字母字元，但在需要數字的地方使用
2. 列為空，並且在需要數字的地方使用

如果我能找到導致問題的行，我可以更改查詢以適當地處理所述數據。但是，我不知道如何隔離該行。我試圖將查詢分成幾部分並查看生成空值的位置，但整個查詢取決於許多外部連接。我已經能夠將範圍隔離到幾天，但這不允許我隔離有問題的行。

此外，查詢已被定義為儲存過程。在過程結束時，我定義了一個錯誤處理程序來擷取所有錯誤並顯示一條消息：

EXCEPTION
   WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20001, error_message || parameter_string_values, TRUE);

但是，該過程會引發Invalid Number錯誤並且不會被底部的錯誤處理程序擷取。

問題

1. 我可以設置任何方法或數據庫變數來幫助隔離此錯誤嗎？
2. 為什麼ORA-01722: Invalid Number錯誤處理程序沒有擷取錯誤？

你的懷疑 #2 不可能是真的，因為空值不會導致 ORA-01722。

Q1：你可以試試

Decode (Translate (AlphanumericColumn, 'x0123456789', 'x'),
 Null, To_Number (AlphanumericColumn),
 SomeIdentifyingNumericReplacementValueForNonNumericContent
)

在所有字母數字列上，無論任何一個被用作數值，然後在輸出中掃描辨識數字替換值，如果仍然辨識，它甚至可能為空，以發現有問題的行。

Q2：可能是有問題的轉換發生在錯誤處理塊之外，特別是在開始之前聲明部分的一個初始化程序中，或者在您的 When Others 之前的另一個異常處理程序中。如果為 true，您應該將現有程式碼包裝到另一個內部 declare-begin-end 塊中，同時將異常處理程序保持在最外層。

引用自：https://dba.stackexchange.com/questions/83499