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

Question

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 2 columns (each column of table A is represented as 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 for, (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 is I am in the right approach or is another better way we have.

"so wanted to review whether I am following right approach" . The whole thing seems like the wrong approach. In databases we validate the data on insertion, by defining columns with the correct datatype and length e.g. column 1 varchar2(7) not null, and enforcing more complicated rules with primary and unique keys, check constraints and foreign keys on valid values in other tables. We get all this stuff virtually for free. You should consider whether you have fully used your database's built-in capabilities before your start coding some horrible unmaintainable monstrosity. — APC
– APC, Commented Feb 17, 2018 at 9:23
@APC , we had thought about that but our requirement is different. 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. — Hareesh
– Hareesh, Commented Feb 17, 2018 at 9:36

Kaushik Nayak · Accepted Answer · 2018-02-17 09:43:04Z

1

As suggested by APC, the best approach is to change your DB design.

You could probably use UNPIVOT and a single INSERT INTO SELECT . The select statement would look like something below.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TableA(
   column_1 VARCHAR(13) 
  ,column_2 VARCHAR(25) 
  ,column_3 VARCHAR(22) 
  ,column_4 VARCHAR(11) 
);
INSERT INTO TableA(column_1,column_2,column_3,column_4) VALUES ('value1_1','value1_2','value1_3','value1_40');

Query 1:

SELECT column_name
    ,column_value
    ,CASE 
        WHEN LENGTH(COLUMN_VALUE) <= 7
            THEN 'NoError'
        ELSE 'invalidLength'
        END AS column_errorKey
FROM TableA
UNPIVOT(column_value FOR column_name IN (
            COLUMN_1
            ,COLUMN_2
            ,COLUMN_3
            ,COLUMN_4
            ))

Results:

| COLUMN_NAME | COLUMN_VALUE | COLUMN_ERRORKEY |
|-------------|--------------|-----------------|
|    COLUMN_1 |     value1_1 |   invalidLength |
|    COLUMN_2 |     value1_2 |   invalidLength |
|    COLUMN_3 |     value1_3 |   invalidLength |
|    COLUMN_4 |    value1_40 |   invalidLength |

edited Feb 17, 2018 at 9:43

answered Feb 17, 2018 at 9:27

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hareesh Over a year ago

thank you, We will try this out. As I said, I do not know whether this also right (Just to be in case ), and I am not telling this is wrong. Just to be on safer side, I just upvoted the answer. To continue the discussion on this, I am not accepting this as the right answer.

Kaushik Nayak Over a year ago

@Hareesh : Thank you for the upvote.Yes, you may always choose what suits your needs.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related