Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results in another table oracle

oracleoracle-11g

I am very much new to the DB world, so wanted to review whether I am following right approach or not.
I have two tables,

table A –> is a table with 40 columns

table B –> is a table with the 3 columns (each column of table A is represented as a row in this table.)

Example:

A:
column_1    |      column_2          |    column_3 ......... | column_40 
-----------------------------------------------------------
value1_1    |      value1_2          | value1_3......        | value1_40


B:
column_name      |column_value  | column_errorKey
----------------------------------------------------
column_1         | value1_1     | value1_1_errorKey
column_2         | value1_2     | value1_2_errorKey

What am I doing?

Validate each value of a row from table A and insert into the table B with
its value, error key and corresponding column name.

My PL SQL code is as below, (Note: SQL code has considered only two columns to minimize the code here)

INSERT WHEN  (LENGTH(column_1)  <=7) THEN
        into table_B values(
          'column_1',column_1,'NoError')
WHEN  (LENGTH(column_1)  >7) THEN
        into table_B values(
          'column_1',column_1,'invalidLength')
WHEN  (LENGTH(column_2)  <= 75) THEN
        into table_B values(
          'column_2',column_2,'NoError')
WHEN  (LENGTH(column_2)  > 75) THEN
        into table_B values(
          'column_2',column_2,'invalidLength')
( select column_1,column_2,...,column_40
          from table_A );

The validation that is happening within When the condition has only one validation but we have more validation like this for the value of each cell. I wanted to know whether I am on right approach or not.

Best Answer

We know that it is best when a single SQL statement can be written for solving a problem. However, your situation requires a SQL statement that would be very unwieldy and difficult to debug and test. One of your comments* contains the following facts:

User can enter anything between 0 to 1000 characters (Data is read from the excel, and end user do not want any restriction on the excel). But on our application, we have to show the errors of the excel.

(* see: https://stackoverflow.com/questions/48839473/oracle-plsql-validate-a-row-and-insert-each-cell-value-of-row-as-a-new-row-with )

My suggestions are: {1} Create a table ("checks") that contains all necessary conditions (checks) including error messages, comments etc. {2} Using PL/SQL and BULK COLLECT to read all rows from this table, and subsequently generate dynamic SQL statements that perform batches of tests (checks) and write their results to a log table (your "table B").

{1}

create table checks (
  condition varchar2(256)
, colname varchar2(256)
, errmessage varchar2(256) default 'no error'
);

begin
insert into checks ( condition, colname ) values ( 'LENGTH( column_1 ) <= 7', 'column_1') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_2 ) <= 75', 'column_2') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_3 ) <= 17', 'column_3') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_4 ) <= 17', 'column_4') ;
-- etc
-- etc
-- etc
insert into checks ( condition, colname, errmessage ) 
values ( 'LENGTH( column_38 ) > 44', 'column_38', 'invalid length' ) ;
insert into checks ( condition, colname, errmessage ) 
values ( 'LENGTH( column_39 ) > 44', 'column_39', 'invalid length' ) ;
insert into checks ( condition, colname, errmessage ) 
values ( 'LENGTH( column_40 ) > 44', 'column_40', 'invalid length' ) ;
end;
/

{2}

declare
  type checks_t is table of checks%rowtype ;
  tchecks checks_t ;
  sqlstring   varchar2(1000) := '' ;
begin
  select condition, colname, errmessage 
  bulk collect into tchecks
  from checks ;

  for i in tchecks.first .. tchecks.last
  loop
    sqlstring := 'insert into b ( column_name, column_value, column_errorKey ) '
              || 'select '
              || ' ( select ''' || tchecks( i ).colname || ''' from dual ), '
              || 'substr( ' 
              ||  tchecks( i ).colname || ', 1, 256 ) ,'
              || ' case '
              || ' when ' || tchecks( i ).condition
              || ' then ''' || tchecks( i ).errmessage
              || ''' else ''condition evaluates to FALSE'' end '
              || ' from A' ;
    execute immediate sqlstring ;
    sqlstring := '' ;
  end loop;
end;
/

There is quite a lot of example code in the dbfiddle. However, the first 5 sections are just about test data for "table A", which is supposed to resemble data stored in the spreadsheet (40 columns x 16 rows). With this setup, table B will end up having 1280 rows after the PL/SQL anonymous block has been executed (80 checks are run on each row of table A).

Being a bit wary about the performance of the script, I've done some testing on a smallish VM (Oracle 12c). Results as follows:

-- table A with 50 rows -> 4000 generated INSERTs into B (80 checks per row)
-- PL/SQL procedure successfully completed.
Elapsed: 00:00:00.050

-- table A with 234256 rows -> 18740480 generated INSERTs into B
-- PL/SQL procedure successfully completed.
Elapsed: 00:02:18.305

SQL> select count(*) from B;
COUNT(*)  
18740480 

Elapsed: 00:00:25.945

Due to limitations of Oracle 11g (no identity columns) and dbfiddle (could not create a sequence), there are no ID columns or PK constraints anywhere in this example. Also, the error messages (in the checks table) can be a bit misleading, but all that can be "easily" adjusted .

Related Question