Hi Tom
We have a preprocessing logic written in perl before loading the data through SQL*Loader into the database. I asked myself the question - "Why Preprocessing?" i.e. I thought of eliminating SQL*Loader in favor of the External Tables; converting the perl into pl/sql. But, there comes the problem ...
We got a set of CSV files - (fMain, fX, fY). We produce a new file (fTemp) from these three files - by enriching fMain with either fX or fY depending on the values in fMain(kind of lookups).
My Idea gone like this ...
1) Three external tables on fMain, fX, fY - call it as xtMain, xtX, xtY.
2) for i in (select * from xtMain) loop
... some other logic ...
select ...
from xtX
where ... = i. ...;
... some other logic ...
select ...
from xtY
where ... = i. ...;
... some other logic ...
... etc ...
write-to-fTemp-file;
end loop;
Then only I realised my stupidity. Each SELECT inside the loop has opened and closed the associated files and hence the performance is really poor. I am not able to convert into a single SQL too because of the complex logic out there.
3) #Existing Perl code skeleton ...
my (%lookup_x, %lookup_y);
build_loopkups();
while(<fMain>){
...
v_out[2] = $lookup{value-from-fMain}
...
print fTemp, v_out;
}
I am really confused in lookup. In perl, they are using associative array kind of stuff (hashes). But we can BULK COLLECT into a nested table not associative arrays, right? Could you help me in converting this perl preprocessing into pl/sql?
February 25, 2011 - 1:27 pm UTC
why are you not just joining?????? Why is there any procedural logic here - just join xtmain to xty and xtx, using an outer join if necessary - then some judicious CASE statments in the select list to transform anything you need.