oracle整库统计库表各个字段数据最大长度--存储过程

CREATE OR REPLACE PROCEDURE TABLES_COL_MAX AS
  COUNT_SQL    VARCHAR2(2000);
  P_TABLE_NAME VARCHAR2(2000);
  P_COL_NAME   VARCHAR2(2000);
  P_COL_TYPE   VARCHAR2(2000);
  P_DATA_LEN   VARCHAR2(2000);
  EXCEPT_SQL VARCHAR2(2000);
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE T_ROWS_MAX';
  EXECUTE IMMEDIATE 'CREATE TABLE T_ROWS_MAX (TABLE_NAME varchar2(200)  ,COL_NAME varchar2(200),COL_TYPE varchar2(50), LENMAX number)';
  FOR CUR IN (select uc.TABLE_NAME, uc.COLUMN_NAME, uc.data_type,uc.data_length
                from user_tab_columns uc) LOOP
    P_TABLE_NAME := CUR.TABLE_NAME;
    P_COL_NAME   := CUR.COLUMN_NAME;
    P_COL_TYPE   := CUR.data_type;
    P_DATA_LEN   := CUR.data_length;
    IF P_COL_TYPE <>'BLOB' OR P_COL_TYPE <>'CLOB' THEN
      BEGIN
        COUNT_SQL    := 'insert into  T_ROWS_MAX(TABLE_NAME,COL_NAME,COL_TYPE,LENMAX)  SELECT ''' ||
                    P_TABLE_NAME || '''AS  TABLE_NAME , ''' || P_COL_NAME ||
                    '''AS  COL_NAME, ''' || P_COL_TYPE ||
                    '''AS  COL_TYPE, MAX(LENGTH('||P_COL_NAME||'
                    )) AS LENMAX  FROM ' || P_TABLE_NAME;
        EXECUTE IMMEDIATE COUNT_SQL;
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
        EXCEPT_SQL    := 'insert into  T_ROWS_MAX(TABLE_NAME,COL_NAME,COL_TYPE,LENMAX)  SELECT ''' ||
                    P_TABLE_NAME || '''AS  TABLE_NAME , ''' || P_COL_NAME ||
                    '''AS  COL_NAME, ''' || P_COL_TYPE ||
                    '''AS  COL_TYPE, '|| P_DATA_LEN ||'  AS LENMAX  FROM ' || P_TABLE_NAME;

        EXECUTE IMMEDIATE EXCEPT_SQL;
        COMMIT;
      END;
     END IF;
  END LOOP;

END TABLES_COL_MAX;