关于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,这样在调用的时候就可以明确的指定需要使用哪个文件了。

问题的校验过程是枯燥繁琐的，但是当明白了问题的原因之后，才发现都是有一些潜在的问题造成的。