Get list of unique columns and actual length (Oracle)

Question

I am looking towards unifying a number of columns across my data warehouse

My source system is Oracle based, and I would like to define my columns in my warehouse in such a way that a given column name can only have one data type and length

I am looking towards getting info on actual length used in the columns in my source system, and can identity the column name, datatype and length through this script

SELECT DISTINCT
    column_name, 
    data_type, 
    data_length 
    
FROM 
    all_tab_columns 

ORDER BY
    column_name

This does not however result in actual MAX(LENGTH()) of the individual columns

Is this possible to obtain, perhaps through a loop function?

Does this answer your question? [Get column value length, not column max length of value](https://stackoverflow.com/questions/13064621/get-column-value-length-not-column-max-length-of-value) — Marmite Bomber, Jan 05 '21 at 08:23
My suggestion is to use data_length from all_tab_columns for simple reason that your OLTP table allows data up to that length. If you create a column with current max length and tomorrow if lengthier new data comes then you will need DDL statements. — Anup Thomas, Jan 05 '21 at 09:45
Hi Anup I have thought about this as well the only drawback is that we have a lot of columns that are set at the max value resulting in unnessecary large column lengths — Henrik Poulsen, Jan 05 '21 at 10:25
Do you want the maximum **defined** data length or the longest populated value? Are you bothered about scale or precision for numeric columns? — APC, Jan 05 '21 at 13:58
Hi APC, I'm looking for longest populated value, on varchar data type primarily — Henrik Poulsen, Jan 06 '21 at 09:58

score 0 · Answer 1 · answered Jan 05 '21 at 08:23

Something like this, perhaps? Just an idea, modify it as you wish.

SQL> set serveroutput on;
SQL> declare
  2    l_str    varchar2(500);
  3    l_maxlen number;
  4  begin
  5    for cur_r in (select table_name, column_name, data_type
  6                  from user_tab_columns
  7                  where table_name in ('EMP', 'DEPT')
  8                  order by table_name, column_name
  9                 )
 10    loop
 11      l_str := 'select max(length(' || cur_r.column_name || '))' ||
 12               ' from ' || cur_r.table_name;
 13      execute immediate l_str into l_maxlen;
 14
 15      dbms_output.put_line(cur_r.table_name ||'.'|| cur_r.column_name ||
 16                           ' - ' || cur_r.data_type ||': '|| l_maxlen);
 17    end loop;
 18  end;
 19  /
DEPT.DEPTNO - NUMBER: 2
DEPT.DNAME - VARCHAR2: 10
DEPT.LOC - VARCHAR2: 8
EMP.COMM - NUMBER: 4
EMP.DEPTNO - NUMBER: 2
EMP.EMPNO - NUMBER: 4
EMP.ENAME - VARCHAR2: 6
EMP.HIREDATE - DATE: 8
EMP.JOB - VARCHAR2: 9
EMP.MGR - NUMBER: 4
EMP.SAL - NUMBER: 4

PL/SQL procedure successfully completed.

SQL>

Get list of unique columns and actual length (Oracle)

1 Answers1