Get Maximum Length allowed in column | Oracle

Question

How to get the Max and Min length allowed in column of varchar2. I have to test for incoming data from the temp table which is coming from some remote db. And each row value is to be tested for specific columns that it has maximum or minimum value which can be set into the column.

So I was to get the column specs using its schema details. I did make a proc for that:

PROCEDURE CHK_COL_LEN(VAL IN VARCHAR2,             
                       MAX_LEN IN NUMBER :=4000,
                       MIN_LEN IN NUMBER :=0,
                       LEN_OUT OUT VARCHAR2)
        IS
            BEGIN
               IF LENGTH(VAL)<MIN_LEN THEN
                    LEN_OUT := 'ERROR';
                    RETURN;
               ELSIF LENGTH(VAL)>MAX_LEN THEN
                    LEN_OUT := 'ERROR';
                    RETURN;
               ELSE
                    LEN_OUT := 'SUCCESS';
                    RETURN;
               END IF;
            END;
END CHK_COL_LEN;

But the problem is, it is not reusable and is a bit hardcoded. I have to explicitly send MAX and MIN value for each value along with the data to be checked.

So at the proc call, it's something like:

CHK_COL_LEN(EMP_CURSOR.EMP_ID, 5, 1, LEN_ERROR_MSG);

I instead want something like: (If something like this exist!)

CHK_COL_LEN(EMP_CURSOR.EMP_ID, 
               EMP.COLUMN_NAME%MAX_LENGTH, 
               EMP.COLUMN_NAME%MIN_LENGTH, 
               LEN_ERROR_MSG)

Thanks in advance.

EDIT

select max(length(col)) from table;

This is a solution, but again I will have to run this query each time to set the two variables for MAX and MIN value. And running extra two queries for each value and then setting 2 variables will cost be significant lose in performance when in have about 32 tables, each with 5-8 varchar2 columns and average rows of about 40k-50k in each table

Answer 1

You can query the table 'user_tab_columns table' to retrieve metadata information of a specific table:

SELECT 
   COLUMN_NAME, DATA_LENGTH, DATA_PRECISION 
FROM 
   user_tab_columns 
WHERE 
   t.table_name IN ('<YOURTABLE>');

with this information you can query the metadata directly in your stored procedure:

...
SELECT 
   CHAR_LENGTH INTO max_length 
FROM 
   user_tab_columns 
WHERE 
   table_name = '<YOURTABLE>' AND COLUMN_NAME = '<YOURCOLUMN>';

...

Exmple Procedure to get max length of table/column:

create or replace PROCEDURE GET_MAX_LENGTH_OF_COLUMN(    
                       tableName IN VARCHAR2,
                       columnName IN VARCHAR2,
                       MAX_LENGTH OUT VARCHAR2)
        IS
            BEGIN

            SELECT CHAR_LENGTH INTO MAX_LENGTH 
            FROM user_tab_columns 
            WHERE table_name = tableName AND COLUMN_NAME = columnName;            

END GET_MAX_LENGTH_OF_COLUMN;