字段类型检测脚本-CSDN博客

前段时间一起上线，在批量大作业运行的时候发生了错误，PL/SQL代码错误“字符串长度超出设定值”。这个错误一般是运行中字符串varchar2长度超过的定义长度。但是，经过代码定位，发现是是两个数据表在赋值的时候，某个被赋值变量定义长度不够。

根据业务要求，两个数据表中具有相同业务含义的字段类型长度应该是一样的。出现的原因很可能是因为一个数据字段变化后，对应的数据表字段没有及时同步。唯一可以借助对比业务含义相同的依据就是字段后3位一样。

当时情况下，是通过开发人员经验定位错误及时解决。之后，笔者觉得有需要借助数据脚本，检测和发现这种错误。

脚本名称：GetDiffField.sql

输入参数：source_name：源数据表名称；

target_name: 目标数据表名称；

common_position: 相同业务字段起始位置；

功能：对比source_name和target_name两个数据表的所有对应字段，其中具有相同业务含义的字段之间，如果字段类型不一致，就报出提示信息。定义相同业务字段是通过common_position表示起始位置。两个字段，如果字段名从（common_position）位到结尾的相同，那么就认为两个字段业务含义相同。例如：

数据表T：iwbtkt，数据表P：ipdtkt；输入参数：source_name=T，target_name=P，common_position=4，就可以输出结果。

脚本如下：（脚本会附加在blog的文件下载区）

Script_name: GetDiffField.sql

Purpose: Find the fields difference between tables

Author: liuziyu

Data: 2011-2-5

set serveroutput on size 10000;

set timing on;

declare

type all_tabcol_list is table of all_tab_cols%rowtype index by binary_integer;

type all_tabcolvar_list is table of all_tab_cols%rowtype index by varchar2(100);

all_soucol_infos all_tabcol_list;

all_tarcol_infos all_tabcol_list;

all_tarcolvar_infos all_tabcolvar_list;

i number;

pos number;

all_colsou_info all_tab_cols%rowtype;

all_coltar_info all_tab_cols%rowtype;

vc_temp varchar2(100);

begin

--获取source table的column

select *

bulk collect into all_soucol_infos

from all_tab_cols a

where a.owner=user and a.table_name=upper('&source_table');

--是否有效的source table检验

if (all_soucol_infos.count=0) then

dbms_output.put_line('Source Table is invalid !');

return;

end if;

--获取target table的column

select *

bulk collect into all_tarcol_infos

from all_tab_cols b

where b.owner=user and b.table_name=upper('&target_table');

--是否有效的target table检验

if (all_tarcol_infos.count=0) then

dbms_output.put_line('Target Table is invalid !');

return;

end if;

pos := &common_pos;

--转化

for i in 1..all_tarcol_infos.count loop

vc_temp := substr(all_tarcol_infos(i).column_name,POS,length(all_tarcol_infos(i).column_name)-POS+1);

all_tarcolvar_infos(vc_temp) := all_tarcol_infos(i);

--dbms_output.put_line(vc_temp);

end loop;

--循环查找

for i in 1..all_soucol_infos.count loop

--存在相同的列信息

vc_temp := substr(all_soucol_infos(i).column_name,POS,length(all_tarcol_infos(i).column_name)-POS+1);

if (all_tarcolvar_infos.exists(vc_temp)) then

all_coltar_info := all_tarcolvar_infos(vc_temp);

all_colsou_info := all_soucol_infos(i);

--比较类型信息

if (all_colsou_info.data_type!=all_coltar_info.data_type or

all_colsou_info.data_length!=all_coltar_info.data_length or

nvl(all_colsou_info.data_PRECISION,-1)!=nvl(all_coltar_info.data_PRECISION,-1) or

nvl(all_colsou_info.DATA_SCALE,-1)!=nvl(all_coltar_info.DATA_SCALE,-1) or

all_colsou_info.NULLABLE!=all_coltar_info.NULLABLE) then

--发现异常

dbms_output.put_line(lpad('-',100,'-'));

dbms_output.put_line('Diffs Find In '||vc_temp||': ');

dbms_output.put_line('SOURCE : '||all_colsou_info.table_name||' '||all_colsou_info.column_name

||' Data Type: '||all_colsou_info.data_type||' Length: '

||all_colsou_info.data_length||' PRECI: '||all_colsou_info.data_PRECISION||' SCALE: '

||all_colsou_info.DATA_SCALE||' NULLABLE: '||all_colsou_info.NULLABLE);

dbms_output.put_line('TARGET : '||all_coltar_info.table_name||' '||all_coltar_info.column_name

||' Data Type: '||all_coltar_info.data_type||' Length: '

||all_coltar_info.data_length||' PRECI: '||all_coltar_info.data_PRECISION||' SCALE: '

||all_coltar_info.DATA_SCALE||' NULLABLE: '||all_coltar_info.NULLABLE);

dbms_output.put_line(lpad('-',100,'-'));

dbms_output.put_line(' ');

end if;

end loop;

end;

脚本使用

两种方法调用：

1、使用sqlplus调用

步骤：

ü 打开sqlplus程序：

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 2月 5 20:55:22 2011

SQL> conn scott/tiger@orcl;

已连接。

ü 运行程序脚本并输入参数，指向脚本对应目录：

SQL> @e:\GetDiffField.sql

输入 source_table 的值: emp

原值 20: where a.owner=user and a.table_name=upper('&source_table');

新值 20: where a.owner=user and a.table_name=upper('emp');

输入 target_table 的值: t

原值 32: where b.owner=user and b.table_name=upper('&target_table');

新值 32: where b.owner=user and b.table_name=upper('t');

输入 common_pos 的值: 1

原值 40: pos := &common_pos;

新值 40: pos := 1;

ü 脚本输出结果：

-------------------------------------------

Diffs Find In MGR:

SOURCE : EMP MGR Data Type: NUMBER Length: 22 PRECI: 4 SCALE: 0 NULLABLE: Y

TARGET : T MGR Data Type: VARCHAR2 Length: 10 PRECI: SCALE: NULLABLE: Y

---------------------------------------------

--------------------

Diffs Find In ENAME:

SOURCE : EMP ENAME Data Type: VARCHAR2 Length: 10 PRECI: SCALE: NULLABLE: Y

TARGET : T ENAME Data Type: VARCHAR2 Length: 122 PRECI: SCALE: NULLABLE: Y

---------------------------------------------------------------------

Diffs Find In EMPNO:

SOURCE : EMP EMPNO Data Type: NUMBER Length: 22 PRECI: 4 SCALE: 0 NULLABLE: N

TARGET : T EMPNO Data Type: NUMBER Length: 22 PRECI: 4 SCALE: 0 NULLABLE: Y

-------------------------------------------------------------------------------

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.04

2、PL/SQL Developer调用

如果是使用PL/SQL Developer开发工具进行调用。可以考虑使用command窗口。Command窗口是仿造sqlplus的一个IDE环境，在易用性上好于sqlplus。可以借用command窗口的Editor页签。将脚本语句粘贴到页签上，执行语句即可。

执行中，一些输入参数是通过对话框输入实现的。功能是相同。

最后，使用程序来简化我们的工作是作为一个高效开发人员的必备技能和思路。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-686817/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-686817/