Home » SQL & PL/SQL » SQL & PL/SQL » Validation of column values of records in PL/SQL table (Oracle 10g)
Validation of column values of records in PL/SQL table [message #345166] Tue, 02 September 2008 08:21 Go to next message
kumarprabhash@gmail.com
Messages: 2
Registered: September 2008
Junior Member
Hello All,

I have got a scenario for which I need your help.

I am fetching and bulk collecting records into a pl/SQl table. Record Contains the following fields. As a sample, I have given the values of the fields:
--------------------------------------------------------------
Meta_:Product:Col1:Col2:Col3:Col4......:Col18:Region_Id:Status
--------------------------------------------------------------
 1   : PR001 : 5  :  5 : 5  : 5 .......: 5   : APAC    : Null
--------------------------------------------------------------

Here I need to do the following:
For every record, I need to validate the Product Column Value, All Col(1-18) Values and Region_Id VAlue. As part of validation, I have written relevant functions to validate Product, Col(1-18) and Region_Id.

Product validation will return me - Valid or Invalid
Col(1-18) validation will return me - Valid, Invalid, Null
Region_Id validation will return me - Valid, Invalid

Now, Question is - 

For every record, I need to validate Product,Col(1-18),Region_id and then push the records into exception table which are not valid

--------------------------------------------------------------
Meta_Info_Id:Product:Col1:Col2:Col4......:Col18:Region_Id:Status
--------------------------------------------------------------
      1     : PR001 : 5  :  5 : 5  : 5 .......: 5   : APAC:
--------------------------------------------------------------
Status column will have error message as - Product is null
                                           Col1 is invalid
                                           Col17 is null
                                           Col18 is invalid
Assuming that - Product column has some issue
              - Col1, Col17, Col17 has issues

Idea, on the above as how to proceed will help me a lot.
Re: Validation of column values of records in PL/SQL table [message #345167 is a reply to message #345166] Tue, 02 September 2008 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Re: Validation of column values of records in PL/SQL table [message #345400 is a reply to message #345166] Wed, 03 September 2008 07:04 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I would think you could just iterate over the records in your plsql table, calling your validation functions as you go? Then store the exceptions in a second plsql collection and do a forall insert into your exceptions table at the end.

Another option might be to wrap your validation functions inside a custom analytic function and do the validation as part of the query that you use to bulk collect into the plsql collection to begin with.
Previous Topic: PL/SQL Dynamic SQL & bind variable question
Next Topic: lag function
Goto Forum:
  


Current Time: Sat Dec 10 18:42:56 CST 2016

Total time taken to generate the page: 0.18032 seconds