Data_Validation_In_PL/SQL

[tabjula]

Hi All,

I have a requirement like to validate the data in PL/SQL script dynamically.

I have 4 tables

TEST_TBL: Data available in this table
TEST_VALID_TBL: Contains field names of TEST_TBL and condition
VALID: Need to insert valid records
INVALID: Need to insert invalid records

I have to insert data into valid table when validation are full filled otherwise it should be insert invalid table .

Validation are based on TEST_VALID_TBL

While checking the data validations FIELD_NAME should be passed dynamically,because i have four columns in TEST_TBL
but at present I am validating only 3 columns in feature it may be add more columns to validate.

Please do the needful asap.

Tables Are:

Code: Select all

CREATE TABLE TEST_TBL(ID NUMBER,LOGTI FLOAT,ATTI FLOAT,SUB_TYPE VARCHAR2(20));

INSERT INTO TEST_TBL(ID,LOGTI,ATTI,SUB_TYPE) VALUES(1,345.466,577.86,'Y');
INSERT INTO TEST_TBL(ID,LOGTI,SUB_TYPE) VALUES(2,345.466,'Y');
INSERT INTO TEST_TBL(ID,LOGTI,SUB_TYPE) VALUES(3,678.789,'N');
INSERT INTO TEST_TBL(ID,LOGTI,SUB_TYPE) VALUES(4,98.9,'N');
INSERT INTO TEST_TBL(ID,LOGTI) VALUES(4,7899);

CREATE TABLE TEST_VALID_TBL(FIELD_NAME VARCHAR2(20),DATA_TYPE VARCHAR2(20),MANDATORY CHAR(1));

INSERT INTO TEST_VALID_TBL(FIELD_NAME,DATA_TYPE,MANDATORY) VALUES('ID','NUMBER','Y');
INSERT INTO TEST_VALID_TBL(FIELD_NAME,DATA_TYPE,MANDATORY) VALUES('LOGTI','FLOAT','Y');
INSERT INTO TEST_VALID_TBL(FIELD_NAME,DATA_TYPE,MANDATORY) VALUES('ATTI','FLOAT','Y');

CREATE TABLE VALID(ID NUMBER,LOGTI FLOAT,ATTI FLOAT,SUB_TYPE VARCHAR2(20));

CREATE TABLE INVALID(ID NUMBER,LOGTI FLOAT,ATTI FLOAT,SUB_TYPE VARCHAR2(20),REMAKS VARCHAR2(300));

Regards,
Dileep

[Tim...]

Hi.

I'm not really sure how I would approach this. Actually, I think the first thing I would do is ask the person who asked for it if is was really a sensible idea.

Trying to write these types of generic validations rules engines are:

1) A pain in the ass.
2) Very time consuming.
3) Likely to need constant maintenance as the requirement changes.
4) Hard to support long term. When the original coder leaves, everyone gets scared to change them.
5) VERY inefficient.

Personally I would suggest you do the following:

1) Put mandatory constraints on mandatory fields. If the data fails to insert you use an exception handler to capture the data and log it in the invalid data table.
2) Use either check constraints or triggers to validate more complex validations. If the validation fails, raise and exception of the row insertion fails and log as before.

That will be a lot more efficient. Easier to do and will be easier to maintain.

Cheers

Tim...

[tabjula]

Hi Tim,
thanks for replay.

in my case constrains are not possible because if first column and fourth column is null then vilate the constrains when try to insert fist colum, but i need to log first and fourth column.
I don't have only mandatory columns but alos i have some codition with bussiness logic.

plese help me on this.

regards,
dileep.

[Tim...]

Hi.

I don't have the time to write a generic routine for you. Even if I did I probably wouldn't as you would then not be able to support it or maintain it. The fact you are having to ask someone else to do it suggests it is too complicated for you to maintain it! What's more it would run like a dog.

You would be better to write a specific routine to check this data, rather than trying to make it generic. For example:

Code: Select all

DECLARE
  l_valid   BOOLEAN;
  l_remarks invalid.remaks%TYPE;
BEGIN
  FOR cur_rec IN (SELECT * FROM test_tbl) LOOP
    l_valid := TRUE;
    l_remarks := NULL;

    -- Test ID.
    -- Don't need to test if it is a NUMBER. It must be as it is stored in a NUMBER column.
    IF cur_rec.id IS NULL THEN
      l_valid := FALSE;
      l_remarks := l_remarks || 'ID IS NULL,';
    END IF;

    -- Test LOGTI.
    -- Don't need to test if it is a FLOAT. It must be as it is stored in a FLOAT column.
    IF cur_rec.logti IS NULL THEN
      l_valid := FALSE;
      l_remarks := l_remarks || 'LOGTI IS NULL,';
    END IF;

    -- Test ATTI.
    -- Don't need to test if it is a FLOAT. It must be as it is stored in a FLOAT column.
    IF cur_rec.atti IS NULL THEN
      l_valid := FALSE;
      l_remarks := l_remarks || 'ATTI IS NULL,';
    END IF;

    IF l_valid THEN
      INSERT INTO valid (id, logti, atti, sub_type)
      VALUES (cur_rec.id, cur_rec.logti, cur_rec.atti, cur_rec.sub_type);
    ELSE
      INSERT INTO invalid (id, logti, atti, sub_type, remaks)
      VALUES (cur_rec.id, cur_rec.logti, cur_rec.atti, cur_rec.sub_type, l_remarks);
    END IF;
  END LOOP;
  COMMIT;
END;
/

If you did this, you would get the following results.

Code: Select all

SQL> select * from valid;

        ID      LOGTI       ATTI SUB_TYPE
---------- ---------- ---------- --------------------
         1    345.466     577.86 Y

SQL> select * from invalid;

        ID      LOGTI       ATTI SUB_TYPE             REMAKS
---------- ---------- ---------- -------------------- ---------------
         2    345.466            Y                    ATTI IS NULL,
         3    678.789            N                    ATTI IS NULL,
         4       98.9            N                    ATTI IS NULL,
         4       7899                                 ATTI IS NULL,

SQL>

This would be the correct result based on the information you've provided.

That type of approach is simple to code, easy to maintain as it is obvious to anyone reading it how it works!

Note. I've purposely not used bulk-binds, which would improve performance, as I have no idea if you understand them and I have no idea of the numbers of records you are likely to want to validate.

Cheers

Tim...