Validate Column values while insert in PL/SQL

Question

i have the following example

create table Test (
 C1 nvarchar(50) not null,
 C2 number,
 C3 date);

insert into Test(C1,C2,C3) select (v1,v2,v3)from emp ;

i want to add validations not to insert null values for C1, not to insert string for C2, insert dates only for C3.

any idea to do that in pl/sql?

thanks

Oracle will already do this for you. If your bulk insert attempts to put a NULL into TEST.C1 an exception will be raised. Even if the code catches and ignores the error, as soon as an attempt is made to COMMIT the changes an exception will be raised AGAIN - and if THAT is ignored and the program exits with uncommitted changes, those changes will be rolled back by the database engine. Ditto and likewise for the other columns - but note that if you try to put a "string" value into C2 which can be successfully converted to a NUMBER, the insert will still work. Same for dates. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Feb 3, 2014 at 3:09

René Nyffenegger · Accepted Answer · 2014-01-31 17:41:47Z

2

There is no need to do anything with your table definition, Oracle will throw an error if you try to insert "wrong" data.

I have also spotted that you should use the select statement without parantheses:

insert into Test (ca, c2, c3) select v1, v2, v3 from emp;

Maybe, what you really want is to only insert those records that fit the table constraints. In that case you can use dbms_errlog along with the log errors into clause.

The records that don't fit your criterias will then be inserted in another table for further review.

See this sql fiddle.

edited Jan 31, 2014 at 17:41

answered Jan 31, 2014 at 16:29

René Nyffenegger

40.9k35 gold badges180 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3258052 Over a year ago

thank u very much, this will help. but the main problem that i want to insert bulk data from emp table to test table in a single insert, if one column at least violate the constraint i want to prevent the user from insert and return error messgaes. any help please?

René Nyffenegger Over a year ago

Have you looked at the sql fiddle that I provided?

Collectives™ on Stack Overflow

Validate Column values while insert in PL/SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related