1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Data_Validation_In_PL/SQL

Discussion in 'SQL PL/SQL' started by tabjula, Feb 19, 2013.

  1. tabjula

    tabjula Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    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.



    Regards,
    Dileep
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is a forum, populated by volunteers not a paid support organization. I understand your impatience but you can't order us to solve your problem. Please tell us why you need to validate insert statements through PL/SQL when NOT NULL and check constraints, as well as foreign keys, would be a better method of validation? PL/SQL is transactional, constraints are not. Looking at the script you attached you have three columns listed as 'MANDATORY', yet the table that contains these columns has none of them declared as NOT NULL. Why is this? Processing PL/SQL blocks to 'validate' insert statements is time consuming and resource intensive for large numbers of concurrent sessions where constraints do not place such a burden on the database or server. How are you validating a query passed to this code? You state in your 'requirements' that FIELD_NAME must be passed as an argument -- are you planning on constructing the insert statement 'on the fly' based on the FIELD_NAME values passed to the block? If so how will you collect the actual data to populate the field names provided?

    This is a difficult, if not nearly impossible, task. Did you assign this to yourself? The code can get so complex as to be almost unsupportable, and as I mentioned before it will be a burden on the server and the database when more than one user is connected and running this code.
     
  3. tabjula

    tabjula Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi david,

    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.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    According to the sample data you provided you do and they are the first three columns in your table. Based on that information the following example is provided:

    Code (SQL):
    SQL> --
    SQL> -- Create tables from code provided
    SQL> --
    SQL> -- Populate them as well
    SQL> --
    SQL> CREATE TABLE TEST_TBL(ID NUMBER,LOGTI FLOAT,ATTI FLOAT,SUB_TYPE VARCHAR2(20));
     
    TABLE created.
     
    SQL>
    SQL> INSERT INTO TEST_TBL(ID,LOGTI,ATTI,SUB_TYPE) VALUES(1,345.466,577.86,'Y');
     
    1 ROW created.
     
    SQL>
    SQL> INSERT INTO TEST_TBL(ID,LOGTI,SUB_TYPE) VALUES(2,345.466,'Y');
     
    1 ROW created.
     
    SQL>
    SQL> INSERT INTO TEST_TBL(ID,LOGTI,SUB_TYPE) VALUES(3,678.789,'N');
     
    1 ROW created.
     
    SQL>
    SQL> INSERT INTO TEST_TBL(ID,LOGTI,SUB_TYPE) VALUES(4,98.9,'N');
     
    1 ROW created.
     
    SQL>
    SQL> INSERT INTO TEST_TBL(ID,LOGTI) VALUES(4,7899);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> CREATE TABLE TEST_VALID_TBL(FIELD_NAME VARCHAR2(20),DATA_TYPE VARCHAR2(20),MANDATORY CHAR(1));
     
    TABLE created.
     
    SQL>
    SQL> --
    SQL> -- These three columns appear to be required for an insert to succeed
    SQL> --
    SQL> INSERT INTO TEST_VALID_TBL(FIELD_NAME,DATA_TYPE,MANDATORY) VALUES('ID','NUMBER','Y');
     
    1 ROW created.
     
    SQL>
    SQL> INSERT INTO TEST_VALID_TBL(FIELD_NAME,DATA_TYPE,MANDATORY) VALUES('LOGTI','FLOAT','Y');
     
    1 ROW created.
     
    SQL>
    SQL> INSERT INTO TEST_VALID_TBL(FIELD_NAME,DATA_TYPE,MANDATORY) VALUES('ATTI','FLOAT','Y');
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> CREATE TABLE TAB_VALID(ID NUMBER,LOGTI FLOAT,ATTI FLOAT,SUB_TYPE VARCHAR2(20));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE TAB_INVALID(ID NUMBER,LOGTI FLOAT,ATTI FLOAT,SUB_TYPE VARCHAR2(20),REMARKS VARCHAR2(300));
     
    TABLE created.
     
    SQL>
    SQL> --
    SQL> -- Create a procedure to 'validate' the data
    SQL> --
    SQL> -- Not the best way to ensure required data is inserted
    SQL> --
    SQL> CREATE OR REPLACE PROCEDURE insert_valid_data(p_field1 IN varchar2, p_val1 IN varchar2, p_field2 IN varchar2,
      2                                                p_val2 IN varchar2, p_field3 IN varchar2, p_val3 IN varchar2)
      3  IS
      4          sqlstmt varchar2(4000);
      5          --
      6          -- Create a string of the concatenated column names
      7          -- provided to the procedure
      8          --
      9          -- This is to verify that all required columns
     10          -- are present for successful inserts
     11          --
     12          colstr  varchar2(4000):=UPPER(p_field1||p_field2||p_field3);
     13          invalct NUMBER:=0;
     14          srch    NUMBER;
     15          pos     NUMBER:=1;
     16
     17          --
     18          -- Return all mandatory columns for insert statements
     19          --
     20          cursor get_mandatory_cols IS
     21          SELECT UPPER(field_name) field_name, LENGTH(field_name) fldlen
     22          FROM test_valid_tbl
     23          WHERE mandatory = 'Y';
     24
     25  BEGIN
     26
     27          --
     28          -- Verify that each mandatory column appears
     29          -- in the list of fields provided to the procedure
     30          --
     31          FOR mc IN get_mandatory_cols loop
     32                  srch:=instrc(colstr, mc.field_name, pos);
     33                  IF srch = 0 THEN
     34                          invalct:=invalct+1;
     35                  END IF;
     36                  pos:=pos+mc.fldlen;
     37          END loop;
     38
     39          --
     40          -- If all required columns are not present log the
     41          -- row into the invalids table
     42          --
     43          -- Otherwise insert a valid record
     44          --
     45          IF invalct > 0 THEN
     46                  sqlstmt:='insert into tab_invalid ('||p_field1||','||p_field2||','||p_field3||') values ('||p_val1||','|| p_val2||','|| p_val3
    )';
     47                  execute immediate sqlstmt;
     48          else
     49                  sqlstmt:='
    INSERT INTO tab_valid ('||p_field1||','||p_field2||','||p_field3||') VALUES ('||p_val1||','|| p_val2||','|| p_val3||
    ;
     50                  execute immediate sqlstmt;
     51          end if;
     52
     53          commit;
     54
     55  exception
     56          when others then
     57                  dbms_output.put(dbms_utility.format_error_stack);
     58                  dbms_output.put_line(dbms_utility.format_error_backtrace);
     59  end;
     60  /
     
    Procedure created.
     
    SQL>
    SQL> show errors

    No errors.

    SQL>
    SQL> --
    SQL> -- Valid insert
    SQL> --
    SQL> exec insert_valid_data('
    ID','9','LOGTI','9','ATTI','9');
     
    PL/SQL procedure successfully completed.
     
    SQL> --
    SQL> -- Invalid insert #1
    SQL> --
    SQL> exec insert_valid_data('
    ID','9','LOGTI','9','SUB_TYPE','43');
     
    PL/SQL procedure successfully completed.
     
    SQL> --
    SQL> -- Invalid insert #2
    SQL> --
    SQL> exec insert_valid_data('
    ID','9','ATTI','9','SUB_TYPE','9');
     
    PL/SQL procedure successfully completed.
     
    SQL>
    SQL> --
    SQL> -- Verify inserts occurred correctly
    SQL> --
    SQL> select * from tab_valid;
     
            ID      LOGTI       ATTI SUB_TYPE
    ---------- ---------- ---------- --------------------
             9          9          9
     
    SQL> select * from tab_invalid;
     
            ID      LOGTI       ATTI SUB_TYPE
    ---------- ---------- ---------- --------------------
    REMARKS
    --------------------------------------------------------------------------------
             9          9            43

             9                     9 9
     
    SQL>
    This is for three columns passed to the procedure; passing more columns would require more code and possibly a different method of processing.