關於shell中的pl/sql指令碼錯誤排查與分析

今天有個同事問我一個問題，他說執行shell指令碼的時候丟擲了ORA 錯誤，但是對於錯誤的原因沒有思路，想讓我幫他看看。
我檢視了下，指令碼的結構比較清晰。
指令碼是有一個shell指令碼，一個sql檔案組成，shell指令碼作為基本的流程控制，sql檔案中是pl/sql指令碼。
大體明白了shell指令碼的部分，沒有做過多的追究，就開始瞭解pl/sql指令碼的內容了。
首先在pl/sql中宣告瞭大量的procedure，類似shell中的function，大概有10多個procedure
然後在最後使用一個類似main函式的pl/sql塊來判斷，什麼場景呼叫什麼procedure
指令碼結構類似

declare
flag varchar2(100); --宣告的變數
procedure proc1 is 
begin
dbms_output.put_line('this is a test for procedure one');  --儲存過程的內容
end;
procedure proc2 is 
begin
dbms_output.put_line('this is a test for procedure two);
end;
---more procedures defined here
begin                 --類似main方法的部分
flag:='a';             --宣告的變數透過shell變數傳入
if(flag='a') then
proc1;                --呼叫儲存過程
end if;
if(flag='b') then
proc2;
end if;
end;
/

儲存過程大概有10多個，所以抓住重點來看整個shell指令碼就比較清晰了，要不直接上來就看儲存過程的細節，馬上就迷茫了。
明白了儲存過程的整體實現思路，來看丟擲的錯誤，錯誤是一個老套的ORA錯誤。
ORA-00942: table or view does not exist
根據錯誤的資訊，出錯的地方是在第一個儲存過程proc1
這個儲存過程的內容就很豐富了，裡面會呼叫動態sql建立view，建立臨時表。
細數下來，建立view,function,table的操作大概有6,7處。
如何儘快地排查出倒底是在哪個環節出錯還是比較棘手的。
比如一個呼叫動態pl/sql建立view, 建立的於假設為
create or replace view test_view as select xxxxx,xxxx,xxxxx, xxx from table1,table2,table3,table4 
where xxxxxxx xxxxx  xxxx
對於大量的這種操作一種比較快捷的方式就是使用explain plan來校驗。
因為有些pl/sql塊不能隨便執行，不能隨便建立view,table等，所以透過explain plan能夠快速的校驗出哪些表可能存在問題或者無法訪問等等。
如果存在，那麼很快就會解析生成執行計劃。影響是很小的。
SQL> explain plan for select test.object_id,t.object_id from test ,t where test.object_id=t.object_id;
Explained.

如果出錯，就會很明顯的得到錯誤的出處。
explain plan for select test.object_id,t.object_id from test ,ttttt t where test.object_id=t.object_id
                                                              *
ERROR at line 1:
ORA-00942: table or view does not exist
這樣就會很明顯的發現錯誤之處在於ttttt不可訪問或者不存在。
明白了這點，問題的檢查會很有條理，可以略過一些複雜的pl/sql過濾條件細節，一般from之後的表名都不會是動態的。可以很方便地進行校驗。
但是讓人奇怪的是檢查了一圈，沒有發現問題。最後無奈之下就嘗試在指令碼中臨時加入一些資訊日誌，然後精確地定位出錯的問題才發現原來是檔案路徑的問題，
比如在庫檔案的根路徑在 /u01/app/plsql/test.sql
但是在開發目錄下執行指令碼的時候路徑是/u02/app/plsql/test.sql
這樣在shell指令碼中呼叫使用@test.sql的呼叫方式來執行pl/sql塊就很可能就是庫檔案的路徑而不是當前的開發目錄下了。
這種問題可能比較隱晦，出了問題確實不好查詢，可以使用絕對路徑來完成，絕對路徑可以根據shell變數來靈活的配置指定。
比如庫檔案路徑為我們定義變數LIB_CORE_PATH= /u01/app/plsql
定義開發路徑為 LIB_DEV_PATH=/u02/app/plsql,這樣在呼叫的時候就可以明確的指定需要使用哪個檔案了。

問題的校驗過程是枯燥繁瑣的，但是當明白了問題的原因之後，才發現都是有一些潛在的問題造成的。