Validation procedure to check for columns not null in a table if satifies "not null" then no need to

3648470

I have a emp table in which i have a do a validation check after loading data into table.

Emp Table:-

eid int,

ename varchar,

desig varchar,

sal number,

validation_error varchar

for Example after imporing/loading data if i have eid not null, ename not null,  desig not null, sal not null, then it should log the error into the validation error column...

o/p:-

empid,   ename   desig    sal        validation_error

1             -             IT        2000     Ename should not be null
2             -             -         4000      Ename should not be null | desig should not be null
3               -             -          -          Ename should not be null | desig should not be null | sal should not be null
-             -              -          -            Empid should not be null | Ename should not be null | desig should not be null | sal should not be null

As am learning sql on my own, am unable to get it.pls help. thanks.

Hans Steijntjes

OK, place the update in a procedure then, as in:

create procedure validate_emp

as

begin

  update emp

  set validation_error = substr(     case when empid is null then ' | EmpId should not be null' else '' end

                                  || case when ename is null then ' | ename should not be null' else '' end

                                  || case when desig is null then ' | desig should not be null' else '' end

                                  || case when sal is null then ' | Sal should not be null' else '' end

                                 ,4

                                  );

end validate_emp;

you can then call this procedure from a shell script;

GregV

Hi,

A NOT NULL constraint is meant to meet this requirement. Just declare one for each column that's not supposed to be null.

3648470

As i have to import more than 3 million records, i dont want to use not null constraint in the create table statement itself...Dono the datatype and due to requirements, i need to write the proc for this ...Hope you understand.

Hans Steijntjes

Try something like:

update emp

  set validation_error = substr(     case when empid is null then ' | EmpId should not be null' else '' end

                                  || case when ename is null then ' | ename should not be null' else '' end

                                  || case when desig is null then ' | desig should not be null' else '' end

                                  || case when sal is null then ' | Sal should not be null' else '' end

                                 ,4

                                  )

Instead of storing validation_error in the table, you could also have created a virtual column as in:

alter table emp add ( validation_error as

    ,substr(    case when empid is null then ' | EmpId should not be null' else '' end

             || case when ename is null then ' | ename should not be null' else '' end

             || case when desig is null then ' | desig should not be null' else '' end

             || case when sal is null then ' | Sal should not be null' else '' end

            ,4

           ) );

GregV

Is this requirement for one table only? Suppose you need to do that for many tables with many columns, are you going to hardcode all the IF/CASE statements? Or, would you use a table to setup all the rules? In both cases this is going to be very cumbersome.

For your requirement, you'll need an UPDATE statement on the whole table, something like:

UPDATE emp

SET validation_error = case when empid is null and ename is null and desig is null and sal is null then ...

                                              when empid is null and ename is null and desig is null then ...

                                              when empid is null and ename is null then ...

                                              when empid is null then ..

                                    end

you see how complicated it is, not even talking about the performance aspect.

3648470

Hi Hans, direct up date is not correct to my requirement, i also wrote update stmt..

I need like procedure which can satisfy the requiement. this procedure will be automated using shell script after validating the data. Thanks.

Hans Steijntjes

OK, place the update in a procedure then, as in:

create procedure validate_emp

as

begin

  update emp

  set validation_error = substr(     case when empid is null then ' | EmpId should not be null' else '' end

                                  || case when ename is null then ' | ename should not be null' else '' end

                                  || case when desig is null then ' | desig should not be null' else '' end

                                  || case when sal is null then ' | Sal should not be null' else '' end

                                 ,4

                                  );

end validate_emp;

you can then call this procedure from a shell script;

3648470

Hi Cookiemonster76,

am using 10g version, moreover using virutal column, it will reject records during the import.

My reqmt  is for multiple tables ....update statement will take more time as we have some millions of records.

In one short of procedure i need to update multiple tables having "not null" to be captured in the validation_error column which is there in multiple tables(around 45 tables). Please assist.

procedure should take 3 parameters as  table_name, column_name, not null then it should give the required output. Thanks.

Cookiemonster76

If you're on 11g or higher then you could just use a virtual column:

SQL> create table Emp (eid number,  2                    ename varchar(100),  3                    desig varchar(100),  4                    sal number,  5                    validation_error varchar(1000) GENERATED ALWAYS AS (rtrim((CASE WHEN eid IS NULL THEN 'Eid Should not be null | ' END  6                                                                              ||CASE WHEN ename IS NULL THEN 'ename Should not be null | ' END  7                                                                              ||CASE WHEN desig IS NULL THEN 'desig Should not be null | ' END  8                                                                              ||CASE WHEN sal IS NULL THEN 'sal Should not be null | ' END  9                                                                              )10                                                                              , ' | '11                                                                              )12                                                                        ) VIRTUAL13                  );Table createdSQL> insert into emp (eid, ename, desig, sal) values (1, null, null, 2);1 row insertedSQL> insert into emp (eid, ename, desig, sal) values (null, 'a name', 'a desig', null);1 row insertedSQL> insert into emp (eid, ename, desig, sal) values (3, null, null, null);1 row insertedSQL> select * from emp;      EID ENAME      DESIG            SAL VALIDATION_ERROR---------- ---------- ---------- ---------- --------------------------------------------------------------------------------        1                                2 ename Should not be null | desig Should not be null          a name    a desig              Eid Should not be null | sal Should not be null        3                                  ename Should not be null | desig Should not be null | sal Should not be nullSQL>

Anytime you need to work something out that depends solely on the contents of the current row you can use a virtual column.

Hans Steijntjes

You could consider using triggers, as in:

create or replace trigger emp_validation_trg

before insert or update

on emp

for each row

begin

  :NEW.validation_error := SUBSTR(case when :NEW.empid is null then ' | EmpId should not be null' else '' end

                                  || case when :NEW.ename is null then ' | ename should not be null' else '' end

                                  || case when :NEW.desig is null then ' | desig should not be null' else '' end

                                  || case when :NEW.sal is null then ' | Sal should not be null' else '' end

                                ,4

                                  ) ;

end;

The value for the field will then be determined during the load process.

3648470

My reqmt  is for multiple tables ....

In one short of procedure i need to update multiple tables having "not null" to be captured in the validation_error column which is there in multiple tables(around 45 tables).

Trying from my side also, but unable to get the o/p...

Am looking for procedure which should take 3 parameters as  table_name, column_name, not null then it should give the required output. Thanks Hans.