Home » SQL & PL/SQL » SQL & PL/SQL » Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i (oracle)
Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668300] Sat, 17 February 2018 06:43 Go to next message
hareesh996
Messages: 4
Registered: February 2018
Junior Member
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.
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668303 is a reply to message #668300] Sat, 17 February 2018 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65961
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the business need?

Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668306 is a reply to message #668303] Sat, 17 February 2018 09:41 Go to previous messageGo to next message
hareesh996
Messages: 4
Registered: February 2018
Junior Member
1. Read excel file
2. Validate cells and store the cell values and corresponding error messages(So that end user can work on these errors later time).
3. Show uploaded data on the application, with the validation messages.
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668307 is a reply to message #668306] Sat, 17 February 2018 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
hareesh996 wrote on Sat, 17 February 2018 07:41
1. Read excel file
2. Validate cells and store the cell values and corresponding error messages(So that end user can work on these errors later time).
3. Show uploaded data on the application, with the validation messages.
From where did Excel enter this problem which originally post with data source being Oracle tables?
Pure Excel file is a proprietary formatted binary file that plain Oracle does not know how to interpret properly.
How does Oracle know or decide if content of any cell is "valid" or not?
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668308 is a reply to message #668306] Sat, 17 February 2018 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65961
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Save you Excel file into a CSV one (if this is not already a CSV file you call Excel file) then use SQL*Loader to load it, log and bad file will give you the problem and which records lead to them.

Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668309 is a reply to message #668300] Sat, 17 February 2018 11:04 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
The term "cell" is really not applicable to a relational database. An RDBMS manages tables of rows (or "tuples" if you want to be precise) consisting of columns.
You seem to be processing table A such that each column of each row becomes its own row in table B. Is that right? So if table A has one million rows of forty columns, table B will end up with forty million rows? If that is what you are doing (I shall not ask why!) you will need to add a column to your table B: some sort of row identifier, so that that you can tell which forty rows in table B were generated from any row in table A.
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668328 is a reply to message #668309] Sun, 18 February 2018 23:45 Go to previous messageGo to next message
hareesh996
Messages: 4
Registered: February 2018
Junior Member
table A--> is a gtt table (session), has the data from the excel.
table B --> is a actual table, that will have the data with its associated validation result
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668331 is a reply to message #668328] Mon, 19 February 2018 01:05 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I am very much new to the DB world,
Yes, I can see that. An RDBMS is not a spreadsheet. You may need to go through some basic tutorials on relational design. Or at least look up the term "normalization".
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668341 is a reply to message #668331] Mon, 19 February 2018 03:02 Go to previous messageGo to next message
quirks
Messages: 81
Registered: October 2014
Member
Just in case you insist in doing it in your DB:

First lets prepare some test data (because you did not provide some:
WITH
    TESTDATA(COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6) AS
        (SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL)
SELECT *
  FROM TESTDATA

This statement generates random Strings each time you execute it. Now I can use the "SELECT * FROM TESTDATA" part to explain how it works.

First you need to unpivot the data:
SELECT *
  FROM TESTDATA
       UNPIVOT
           (COLUMN_VALUE FOR COLUMN_NAME IN ("COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6"))
You can do pivot and unpivot in excel too, so I assume you are familiar with the concept.

Now, that you have for each "Cell" a seperate row you can apply the check rules.
SELECT COLUMN_NAME
      ,COLUMN_VALUE
      ,CASE
           WHEN COLUMN_NAME = 'COLUMN_1' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 7) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_2' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 75) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_3' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 20) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_4' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 3) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_5' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 12) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_6' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 9) THEN 'NoError' ELSE 'invalidLength' END
       END
           AS COLUMN_ERRORKEY
  FROM TESTDATA
       UNPIVOT
           (COLUMN_VALUE FOR COLUMN_NAME IN ("COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6"))

As you see, you can not exactly determine which row is giving an error. So I insert a row identifier (if you follow John Watsons advice to go through some basic tutorials you'll learn that these identifiers are called "primary keys"). In the final statement I include some. So You can figure out (by referencing the Identifier and the column name) which "cell" is malicious.
WITH
    TESTDATA(PRIMARY_KEY, COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6) AS
        (SELECT 1
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT 2
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT 3
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT 4
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT 5
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT 6
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL
         UNION ALL
         SELECT 7
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
               ,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
           FROM DUAL)
SELECT PRIMARY_KEY AS TESTDATA_ID
      ,COLUMN_NAME
      ,COLUMN_VALUE
      ,CASE
           WHEN COLUMN_NAME = 'COLUMN_1' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 7) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_2' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 75) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_3' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 20) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_4' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 3) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_5' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 12) THEN 'NoError' ELSE 'invalidLength' END
           WHEN COLUMN_NAME = 'COLUMN_6' THEN
               CASE WHEN (LENGTH(COLUMN_VALUE) <= 9) THEN 'NoError' ELSE 'invalidLength' END
       END
           AS COLUMN_ERRORKEY
  FROM TESTDATA
       UNPIVOT
           (COLUMN_VALUE FOR COLUMN_NAME IN ("COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6"))
Now you only need to INSERT these values in your "Table B". I'm confident that you can google this.

Welcome to the DB world. The learning curve is steep, but you won't regret it.

[Updated on: Mon, 19 February 2018 03:06]

Report message to a moderator

Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668350 is a reply to message #668341] Mon, 19 February 2018 05:03 Go to previous messageGo to next message
hareesh996
Messages: 4
Registered: February 2018
Junior Member
Thank you for the detailed explanation. I implemented using the unpivot feature.
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668351 is a reply to message #668350] Mon, 19 February 2018 05:24 Go to previous message
quirks
Messages: 81
Registered: October 2014
Member
I'm glad I could help.
Previous Topic: Need help with insert in Oracle
Next Topic: Finding exact row in case of matching
Goto Forum:
  


Current Time: Wed Nov 14 22:17:01 CST 2018