CODEWITHSUNDEEP
oracle-database
cool_taps
cool_taps

Reputation: 350

How we restrict length on column at the time of Insert?

I have one Column named "EMP_GROUP" in table with length of varchar2(30). I'm developing an application where many resource are sends data into our tables. SO I want to restrict this column with length 30. Currently when data comes from the different resource, it will allow to enter records more than length 30.

Please help me here that How would I restrict this column which only allow to enter 30 chars and if data comes which has length more than 30, it will not allow.

Upvotes: 0

Views: 3273

Answers (2)

Drumbeg
Drumbeg

Reputation: 1934

As you perform your INSERT, you can just SUBSTR the value you want to insert. 

INSERT INTO my_table ( emp_group )
VALUES ( SUBSTR( p_my_value, 1, 30 ) );

However, as Jeffrey points out, you want a single point for these inserts within your application; a concept you should apply to any DML you have behind an API. Using PL/SQL within the Oracle database as a data access layer seems a reasonable way to achieve this.

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

You will always get ORA-12899: value too large for column unless you truncate the value before the insert is attempted.

The way to do this is to hide your tables behind an API of some sort - e.g. all inserts are done via a procedure call which will do the insert; alternatively, you can create a view over the table and use an instead-of trigger to handle the truncate.

Upvotes: 1

Related Questions