欄位型別檢測指令碼

 

前段時間一起上線，在批次大作業執行的時候發生了錯誤，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 if;

    

  end loop;

end;

/

 

指令碼使用

 

兩種方法呼叫：

 

1、使用sqlplus呼叫

 

步驟：

 

ü        開啟sqlplus程式：

 

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

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

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頁籤。將指令碼語句貼上到頁簽上，執行語句即可。

 

執行中，一些輸入引數是透過對話方塊輸入實現的。功能是相同。

 

 

最後，使用程式來簡化我們的工作是作為一個高效開發人員的必備技能和思路。