Perl DBI question

[ssmith001]

I'm rather new at Perl and I have a question for which I need some advice. I don't know if this is more of a Perl question or an Oracle question, but here goes... My program accesses an Oracle database and I am trying to minimize the number of times my perl code calls Oracle.

I run a query that returns a location number, item number, and ABC_code.

LOC ITEM ABC_code
A123 847AR A
A123 847AS B
A123 847AT B
A875 944TT A
A875 944XX A

If the ABC_code is 'A', then I need to query a qty value from one set of tables. If it's 'B', I query a different set of tables. As you can see, each row I read needs to go back to the database and run a query. I believe this would take a lot of overhead.

From Perl, what I think I could do would be to query and return all the data for 'A' items, then do this again for all 'B' items, then put both result sets into an array, sort, sum, etc. As I see it, this would then only be done with 3 Oracle calls.

After I queried all the necessary tables to calculate the QTY value, the end result would look like this:

LOC ITEM QTY
A123 847AR 10
A123 847AS 20
A123 847AT 38
A875 944TT 40
A875 944XX 19


From an Oracle perspective, it would be best if there was some way I could return the results above from a single query, that is, query and calculate QTY values for 'A' and 'B' items, all from the same query, then return all the rows back to Perl.

Does anyone have any advice on a path I should follow?

 

[KevinADC]

>> From an Oracle perspective, it would be best if there was some way I could return the results above from a single query, that is, query and calculate QTY values for 'A' and 'B' items, all from the same query, then return all the rows back to Perl.


Sounds like an Oracle question for sure.

 

[tchatzi]

If your tables are properly indexed and don't have billions of lines (literally) then forget about over heading Oracle. She doesn't mind at all.

I work with Perl, Oracle and Oraperl (as a module) everyday. And no matter how many queries I do in a script and specially in loops, it will only take a couple of seconds to complete.

So I suggest you make many simple queries to the db and manage your results with perl instead of making a very complicate query to the db which will take Oracle more to calculate, it will take you time to construct and it will be difficult to maintain if you need to change something in the future.


``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI