oracle查询表内所有字段长度,如何高效的取得一个用户下所有表的每个字段值的最大长度？...

--下面是我自己写的,感觉效率不高,请高手们帮忙改改

CREATE OR REPLACE PROCEDURE p_tab_length(p_user VARCHAR2) AS

CURSOR cur_tables IS

SELECT table_name

FROM   all_tables

WHERE  owner = p_user AND

table_name <> 'T_TAB_LENGTH'

GROUP  BY table_name;

v_sql        VARCHAR2(20000);

v_table_name VARCHAR2(100);

v_length     NUMBER;

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE T_TAB_LENGTH';

OPEN cur_tables;

LOOP

FETCH cur_tables

INTO v_table_name;

EXIT WHEN cur_tables%NOTFOUND;

FOR i IN (SELECT table_name,

column_name,

column_id

FROM   all_tab_columns

WHERE  owner = p_user AND

table_name = v_table_name

ORDER  BY column_id)

LOOP

v_length := 0;

v_sql    := 'SELECT MAX(LENGTHB(' || i.column_name || ')) FROM ' || p_user || '.' ||

v_table_name; --此处将表改为绑定变量要报错,可能如何调整呢?

EXECUTE IMMEDIATE v_sql

INTO v_length;

INSERT INTO t_tab_length

(table_name,

cols,

column_id,

cols_len)

VALUES

(i.table_name,

i.column_name,

i.column_id,

v_length);

COMMIT;

END LOOP;

END LOOP;

CLOSE cur_tables;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;