| Design to Validate Staging records (merged) [message #638564] |
Mon, 15 June 2015 08:10  |
 |
jagadeesh.nraj
Messages: 3
Registered: June 2015
Location: Bangalore
|
Junior Member |
|
|
Hi guys,
Im working on a design to validate staging data,
I have a configuration table which is mapping table which holds the field/column name of the staging table and the data type, length of data, format of the data to be validated
Im using bulk collect to get the data into a table type collection and then validate row wise.
My query is how do i dynamically select columns from the table type collection so that i dont have to write if else condition as staging table will have 30-50 columns in general.
Any advice on the same.
pseudo code
1) following are the bulk collect
select *
bulk collect into l_staging_table_type
from temp_staging_table;
select *
bulk collect into l_param_table_type
from temp_config_param_table;
2) loop the staging collection
for i in 1..l_staging_table_type.last loop
validate (l_staging_table_type(i),l_param_table_type);
end loop;
3)procedure validate (l_staging_table_type temp_staging_table%rowtype,
l_param_table_type
|
|
|
|
|
|
| Re: Designing PL/SQL to Validate Staging data [message #638584 is a reply to message #638564] |
Mon, 15 June 2015 10:19  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jagadeesh.nraj wrote on Mon, 15 June 2015 18:40
I have a configuration table which is mapping table which holds the field/column name of the staging table and the data type
Why? What purpose does it solve by storing the metadata when Oracle already provides the information? Heard about V$ views?
|
|
|
|
| Re: Designing PL/SQL to Validate Staging data [message #638585 is a reply to message #638564] |
Mon, 15 June 2015 10:28 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jagadeesh.nraj wrote on Mon, 15 June 2015 18:40
pseudo code
1) following are the bulk collect
select *
bulk collect into l_staging_table_type
from temp_staging_table;
select *
bulk collect into l_param_table_type
from temp_config_param_table;
2) loop the staging collection
for i in 1..l_staging_table_type.last loop
validate (l_staging_table_type(i),l_param_table_type);
end loop;
3)procedure validate (l_staging_table_type temp_staging_table%rowtype,
l_param_table_type
Is this a real problem? Or is it some kind of imaginative/practice/homework/interview question? Don't mind, but seems to be a serious design flaw.
|
|
|
|
|
|
| Design to Validate Staging records [message #638702 is a reply to message #638564] |
Thu, 18 June 2015 13:01 |
|
jagadeesh.nraj
Messages: 3
Registered: June 2015
Location: Bangalore
|
Junior Member |
|
|
Hi Guys,
I have a query in design implementation of PL/SQL. I was designing a external feed validation for staging data. have a parameter table where technical validation for each field of the feed is configured. Staging data is collected in a Staging table type collection, now the design I thought of implementing was to loop through the parameter data and pick up the column/field from the collection based on the parameter field configured. that would reduce a lot of code if let say i have 200 columns in my staging table say a running sequence Col1,Col2,... and so on. I was wondering how do i dynamically pick up the column from the collection and do the validation accordingly.
------ Staging collection
select *
bulk collect in l_staging_rec
from staging_tab
for i in (select config_field_name,data_type,data_format,data_length,Mandatory
from param_config_validation
where feed_type = 'XZY')
loop
for j in 1..l_staging_rec.count loop
/* Validate data is a proc for validation subroutine*/
validate_data(l_staging_rec(j).col||(i),i.config_field_name,i.data_format,i,data_length,l.Mandatory);
end loop;
end loop;
I thought through to implement this design but collection column/field cannot be dynamically selected (l_staging_rec(j).col||(i)) this wont compile.
So any ideas on the same line guys.
Thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: Designing PL/SQL to Validate Staging data [message #638785 is a reply to message #638780] |
Sat, 20 June 2015 18:29 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hello - I'm not sure I understand the requirement. Is it like this:
You are receiving files of data (perhaps comma separated values, with CR/LF as the record delimiter?) that have differing formats. For example, one file might have the time half past two in the afternoon represented as 14:30 and another file mught have it as 02:30PM. Also, different files may have different number of columns of different types and lengths. And you need to perform some form of validation against the data.
Is that right?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
