關于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 

dbms_output.put_line('this is a test for procedure two);

---more procedures defined here

begin                 --類似main方法的部分

flag:='a';             --聲明的變量通過shell變量傳入

if(flag='a') then

proc1;                --調用存儲過程

end if;

if(flag='b') then

proc2;

/

存儲過程大概有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:

這樣就會很明顯的發現錯誤之處在于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,這樣在調用的時候就可以明确的指定需要使用哪個檔案了。

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