关于shell中的pl/sql脚本错误排查与分析（r4笔记第21天)

今天有个同事问我一个问题，他说运行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,这样在调用的时候就可以明确的指定需要使用哪个文件了。 问题的校验过程是枯燥繁琐的，但是当明白了问题的原因之后，才发现都是有一些潜在的问题造成的。