Skip to Main Content
  • Questions

  • PL/SQL Best approach to validate fields of the table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Srikant.

Asked: October 01, 2016 - 11:46 am UTC

Last updated: October 03, 2016 - 12:08 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Team,

We have one requirement in PL/SQL to validate the records of the table. The fields that needs to checked for the partiular check will be mentioned in the Metadata table.
For ex:

Table_Name Field_name Check Column_Length Primary_Key
Sales Order NOT_NULL 30 Y
Sales Name LENGTH 40 N
Sales Amount INTEGER 76 N
Product ID NOT_NULL 10 Y

Which is the best approach to check the fields of the table from the below two or suggest or any other approac:

1) Column level check : Creating dynamic query to based on the input table name and open a cursor and insert these bad records into error table.
For eg: select Order from Sales where Order is NULL; -- query will return all the fields that are null
select NAME from Sales where length(Name)<>10; -- query will return all the fields not having length 10.

Only challenge will be to create dynamic query as per the type of check required and the amount of data.
In this method, if the table has huge data will there be any performance issue?

2)Row by Row check: Or else read each and every record row by row and check each and every field, i.e top to down approach.



Thanks,
Srikant

and Connor said...

row by row is a disaster waiting to happen.

With the column level approach you can combine checks, because in reality, its the *row* you need to find, and *then* later decide which conditions failed.

So you could something like:

insert into error_table
select r.rowid
from   my_table r
where 
(
  ( length(Name)<>10 ) or
  ( Order is NULL ) or
  ...
  ...
)
and rownum <= 1000


The last predicate is a means of saying "if I get more than 1000 errors, then something *really* bad has happened, so I'll bail out here"

This way you get to do most validation with a single pass through the table.

Hope this helps.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Srikant, October 01, 2016 - 2:13 pm UTC

Thanks Connor!
It's a good idea to find out the culprit rows first and then check later which conditions failed.

Related question

Srikant, October 02, 2016 - 4:44 am UTC

Hi Connor,

I wanted to ask one related question.
Can we create "generic" dynamic query for validation which will work for all the checks (rules)? I mean reading rule from metadata table and creating query based on that.

Suppose that if new rules are added or deleted from the meta data table then the procudure is able to handle that?


Regards,
Srikant
Connor McDonald
October 02, 2016 - 8:23 am UTC

Yes, that is what I mean. You would read from the metadata to produce a validation SQL, which would then be run via (say) 'execute immediate'. So as the rules change over time, you dont need to alter the code.

follow up

A reader, October 02, 2016 - 11:21 am UTC

Hi Connor,

Thanks for replying!

I had suggested the below metadata table structure to my team, but they rejected this approach saying that I am storing (RULE column) the entire where clause inside the table (hardcoding).
To which my response to them was "how will I build dynamic query, without hardcoding I will have to use multiple IFs clauses and the procedure will be no longer be dynamic".

My suggested structure (building sql statement is easy as I have to concatinate):

Table Field RULE Column_Length Primary_Key
-------------------------------------------------------------------
Sales Order OEDER IS NULL 30 Y
Sales Name LENGTH(NAME)=40 40 N
Sales Amount INTEGER 76 N
Product ID ID IS NULL 10 Y

Their Structure (building sql is difficult):

Table Field RULE Column_Length Primary_Key
-------------------------------------------------------------------
Sales Order NOT_NULL 30 Y
Sales Name LENGTH 40 N
Sales Amount INTEGER 76 N
Product ID NOT_NULL 10 Y




Regards,
Srikant
Connor McDonald
October 03, 2016 - 12:08 am UTC


There is perhaps a risk element in having full predicate clauses (as per your table) because someone could do some sql injection.

But either does not seem too hard to generate SQL from ?

select 
  case 
    when rule = 'NOT_NULL' then ' or ('||field||' is null )'
    when rule = 'LENGTH' then ' or (length('||field||') != '||column_length||' )'

etc

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.