How to get all columns which are nearly as long as the maximum allowed size

Question

Is it possible to get all columns (varchar2, char, nvarchar2), where the data is as long (or nearly as long - 2 characters less) than the allowed maximum size of the column in an Oracle Database?

With the following statement, I get the maximum allowed size for each column:

select table_name, column_name, data_type, data_length 
from user_tab_columns 
where data_type in ('VARCHAR2', 'CHAR', 'NVARCHAR2') 
order by data_type;

Now I want to find every column where max(length(column))+2 >= data_length

For example:

The output of my statement is following:

TableA  | ColumnA |  VARCHAR2 |  30
TableA  | ColumnB |  VARCHAR2 |  30
TableB  | ColumnA |  VARCHAR2 |  50
TableB  | ColumnB |  VARCHAR2 |  50

Now I have to run

SELECT MAX(LENGTH(ColumnA)) FROM TableA;
SELECT MAX(LENGTH(ColumnB)) FROM TableA;
SELECT MAX(LENGTH(ColumnA)) FROM TableB;
SELECT MAX(LENGTH(ColumnB)) FROM TableB;

with following results:

20 (is not important, because maximum allowed length is 30)
30 (is important, because maximum allowed length is 30)
30 (is not important, because maximum allowed length is 50)
50 (is important, because maximum allowed length is 50)

Is this possible to find all of them with a script or a statement?

Oh yeah, now it makes plain sense – J. Chomel Mar 17 '17 at 09:19 — J. Chomel, Mar 17 '17 at 09:19

score 4 · Accepted Answer · answered Mar 17 '17 at 09:16

Yes it's possible with PL/SQL:

make a FOR LOOP on your columns,

print output of the max data length; you can add your comparison in the prepared statement v_qry:

declare
  v_qry varchar2(4000) := '';
  v_res number;
begin
  for x in (
    select table_name, column_name, data_type, data_length 
      from user_tab_columns 
     where data_type in ('VARCHAR2', 'CHAR', 'NVARCHAR2') 
   order by data_type
  ) loop
     -- prepare your statement
     v_qry := 'select  MAX(LENGTH('||x.column_name||')) FROM '||x.table_name||' ';
     -- execute
     execute immediate v_qry into v_res;
     -- print result
    dbms_output.put_line('in:'||x.table_name||'.' ||x.column_name||':'||v_res||':compared to:'|| x.data_length);
  end loop;
end;
/

NB: it can take time depending on your tables sizes

With on of my tables (MY) it gives:

in:MY.C_UTI_MAJ:6:compared to:6
in:MY.C_UTI_CRE:6:compared to:6
in:MY.C_TYP_PARAM:20:compared to:20
in:MY.PARAM:16:compared to:20
in:MY.DESCRIPTION:245:compared to:255

Thank you, that is exactly what I was looking for! – user5317046 Mar 17 '17 at 09:54 — user5317046, Mar 17 '17 at 09:54

score 1 · Answer 2 · edited Mar 17 '17 at 09:55

Please consider the encoding you are using in your database.

For example, if you are using UTF8 (AL32UTF8: 4 bytes for one char) you should compare results of LENGTH() with the column CHAR_LENGTH in USER_TAB_COLUMNS, and results of LENGTHB() with DATA_LENGTH column in USER_TAB_COLUMNS.

And consider too that CHAR data type fields will always be padded with spaces: so LENGHT() will give you the length defined for datatype (you can use RTRIM() before LENGTH() ).

How to get all columns which are nearly as long as the maximum allowed size

2 Answers2