Mr Wonderful
MIS
This code in SQL Developer will return via dbmc_output to screen . What I need is the return into a data result set so can display in tablix
DECLARE
max_length INTEGER;
BEGIN
-- loop through column names in all_tab_columns for a given table
FOR t IN (SELECT table_name, data_type, column_name
FROM all_tab_cols
where OWNER=UPPERpSCHEMA_NAME)
and DATA_TYPE IN ('BLOB', 'LOB', 'CLOB')
and table_name not like '%_VIEW_%'
and table_name not like 'V_%'
) LOOP
EXECUTE IMMEDIATE
-- store maximum length of each looped column in max_length variable
'select nvl(max(length('||t.column_name||')),0) FROM '||t.table_name
INTO max_length;
IF max_length > 200000 THEN -- SQL Server NVARCAHRMAX = 2147483647
dbms_output.put_line( t.table_name ||' '|| t.data_type ||' '||t.column_name||' '||max_length ); --print the tableName, columnName and max length
END IF;
END LOOP;
END;
If you can't control it then don't stress about it!