2

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

Improve this question
3
  • 1
    But max(length(col)) will give you max existing length, that's not necessarily the MAX_LENGTH of your column. I think @road242 is right, the only way of get a column size is using dictionary. Commented Dec 15, 2014 at 13:23
  • 1
    Why are you trying to write a validation using the data dictionary instead of letting the table's definition do the validation for you? Commented Dec 15, 2014 at 15:17
  • Because, i want to catch that before hand. As once the exception is thrown(even if it is handled manually) it then skips the operations to be scheduled later(take a scenario when only few loop counts occurred and then the exceptions thrown), then loop ends at first exception occurrence. Commented Dec 16, 2014 at 5:04

3 Answers 3

Reset to default
4

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Yeah, with this i can get the length of course. But it's not of that much use, as for every column, i will have to write such big code and get the value to set into a variable and then pass the variable in proc. Also i can't write the TABLE NAME in proc, coz proc is reuable and used my about 32 tables. So made a common proc for all tables.
why? you can use table name + column name as input parameter, so you would have to write this procedure only once.
You are right, but unfortunately, in Oracle, table name can't be dynamic. Not a single schema name can be dynamic in Oracle, and have to be hardcoded. The limitation of oracle.
working for me. See edited answer, with procedure to show max-length of a column (works as pasted)
no table name or schema name is dynamic here, the statement just retrieves metadata using existing oracle-system-tables
|
1

Try creating your procedure like this:

create or replace procedure Checking_size(column_name varchar2,columnvalue varchar2,state out varchar2) is begin execute immediate 'declare z '||column_name||'%type; begin z:=:param2; end;' using columnvalue; state:='OK'; exception when value_error then state:='NOT OK'; end;

As you can see i simulate an error assignment. If columnvalue length is bigger than the column i pass as column_name it will throws value_error exception and return NOT OK, else return OK. For example, if your_table.your_column refer to a column with length (3) then return NOT OK. 

declare state varchar2(10); begin Checking_size('your_table.your_column','12345',state); dbms_output.put_line(state); end;
Improve this answer

Comments

0

If the list of tables is not much you can specify the MIN Value using CHECK Constraint on the table.

Any DML on the table would automatically fail if it exceeds length assigned to that column.

CREATE TABLE suppliers ( supplier_id numeric(4), supplier_name varchar2(50), CONSTRAINT check_supplier_id CHECK (length(supplier_name) > 5 ) );
Improve this answer

2 Comments

Yes, but the check is to be done before DML. That's why initially the data is stored in a Temp table and then after checking the DML is to be executed. The result should be get caught beforehand any exception! Because for client the exception should be triggered on very serious issues and not this kind.
how is the EMP_CURSOR.EMP_ID cursor build

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.