CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11goracle12c
Hareesh
Hareesh

Reputation: 734

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

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.

Upvotes: 0

Views: 1374

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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 |

Upvotes: 1

Related Questions