1 CREATE OR REPLACE PROCEDURE TABLES_COL_MAX AS
2 COUNT_SQL VARCHAR2(2000);
3 P_TABLE_NAME VARCHAR2(2000);
4 P_COL_NAME VARCHAR2(2000);
5 P_COL_TYPE VARCHAR2(2000);
6 P_DATA_LEN VARCHAR2(2000);
7 EXCEPT_SQL VARCHAR2(2000);
8 BEGIN
9 EXECUTE IMMEDIATE 'DROP TABLE T_ROWS_MAX';
10 EXECUTE IMMEDIATE 'CREATE TABLE T_ROWS_MAX (TABLE_NAME varchar2(200) ,COL_NAME varchar2(200),COL_TYPE varchar2(50), LENMAX number)';
11 FOR CUR IN (select uc.TABLE_NAME, uc.COLUMN_NAME, uc.data_type,uc.data_length
12 from user_tab_columns uc) LOOP
13 P_TABLE_NAME := CUR.TABLE_NAME;
14 P_COL_NAME := CUR.COLUMN_NAME;
15 P_COL_TYPE := CUR.data_type;
16 P_DATA_LEN := CUR.data_length;
17 IF P_COL_TYPE <>'BLOB' OR P_COL_TYPE <>'CLOB' THEN
18 BEGIN
19 COUNT_SQL := 'insert into T_ROWS_MAX(TABLE_NAME,COL_NAME,COL_TYPE,LENMAX) SELECT ''' ||
20 P_TABLE_NAME || '''AS TABLE_NAME , ''' || P_COL_NAME ||
21 '''AS COL_NAME, ''' || P_COL_TYPE ||
22 '''AS COL_TYPE, MAX(LENGTH('||P_COL_NAME||'
23 )) AS LENMAX FROM ' || P_TABLE_NAME;
24 EXECUTE IMMEDIATE COUNT_SQL;
25 COMMIT;
26 EXCEPTION
27 WHEN OTHERS THEN
28 EXCEPT_SQL := 'insert into T_ROWS_MAX(TABLE_NAME,COL_NAME,COL_TYPE,LENMAX) SELECT ''' ||
29 P_TABLE_NAME || '''AS TABLE_NAME , ''' || P_COL_NAME ||
30 '''AS COL_NAME, ''' || P_COL_TYPE ||
31 '''AS COL_TYPE, '|| P_DATA_LEN ||' AS LENMAX FROM ' || P_TABLE_NAME;
32
33 EXECUTE IMMEDIATE EXCEPT_SQL;
34 COMMIT;
35 END;
36 END IF;
37 END LOOP;
38
39 END TABLES_COL_MAX;
