Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SSRS Dataset query all Oracle Tables/Columns;Return the maximum length of data values in each column

Status
Not open for further replies.
Jun 10, 2011
17
US


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=UPPER:)pSCHEMA_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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top