CODEWITHSUNDEEP
oracle-databaseplsql
user3258052
user3258052

Reputation: 59

Validate Column values while insert in PL/SQL

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

Upvotes: 0

Views: 861

Answers (1)

René Nyffenegger
René Nyffenegger

Reputation: 40499

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.

Upvotes: 2

Related Questions