This is a Perl newbie question.

I have an Oracle table that has 4 columns. I need to query and fetch all rows for a given plannercode.

Here's the question...Do I fetch the rows into an array or into a hash?

probably an array.

Can I ask why you said array? Will I be able to reference each of the table fields and their values?

Using an array is very common, but you could use a hash too. Or an array of hashes or an array of arrays or .......... you get the picture.

I have no idea what you are doing so I can't really add much more than that. Plus I really am not so hot with the database stuff anyway, but I see lots of code and stuffing database results into array is a very common practice.

Perhaps I should tell you what I want to do before anyone can give me some advice. As I mentioned earlier, I have an Oracle table that has 4 columns. I need to query this table and load all records into either an array or a hash for additional processing. Here's a sample of the table:

I need to be able to loop through these records and check the various values and do stuff accordingly. For example, if PARAM = 'abc' and value = 'A' do ..., if PARAM = 'abc' and value = 'B' do ..., if PARAM = 'loc' and value = 'A123' do ... I think you get the picture.

With the above in mind, do I use an array or a hash to access each record's values?

I wouldn't use either an array or a hash based on the problems as you've prsented it. I'd write the script so that it operated on the database itself to avoid (or at least limit) the problems with concurrency, etc.

If you load the table into the array, what happens to the changes that other users make while your script is running? Do you ignore their changes, and over-write them? What happens if your script can only update part of the rows in the table? Do you roll back the update, or just ignore the problem?

This just smells like a problem waiting to consume hundreds of hours to me!

-PatP

I appreciate the feedback. Planners are putting data into this table by plannercode using Access, then they run my script to process just their records. I am only extracting the rows for a given planner (the one running my script). Does this change things? Once I am done processing all their records, I will update the processflag for that planner.

I still see it as being bad, but not the potential disaster I was originally thinking it could be.

It is possible to restructure your script so that it becomes a bunch of UPDATE statements that use their WHERE clauses to limit what rows they affect? This gives you most of the benefits of scripting, without the concurrency problems that come from pulling large parts of a table to a client for manipulation.

-PatP

OK, let me give you some more info on what I'm trying to accomplish. The planners are using the Access app to basically tell me what criteria to use when I pull and then update data from other Oracle tables.

For example, if the planner enters a parameter of LOC and a value of "A123", then I run off and query a series of tables where LOC = "A123". If the parameter = 'abc' and the value = 'A', I run off and query/update these tables where ABCCODE = 'A'. If they enter 'abc' and value of 'A' and 'B', then I run and select/update rows where ABCCODE in ('A','B').

If those will be the only type of questions, *and* the combined PARAM+VALUE columns form a primary key (or at least a column pair without duplicates) it could be worthwile to store the table as a hash (with these two columns as the hash key). This will speed up retrieving the row with a certain (PARAM,VALUE) content.

If this assumption does not hold, I see no advantage of using a hash over an array, so I would suggest storing as an array (of quadruple references).

Or probably better, as Pat Phelan suggests, don't store the result table in Perl but process the rows immediately one by one as they are fetched.

It's the "two columns as the hash key" part that I don't know how to do.

That's indeed tricky since a hash key has to be a scalar.
One solution would be to use a reference to a list of length 2.
But that's not a good idea for a hash key since it doesn't allow efficient search on the contents of those two values.

So you will have to somehow construct a scalar from those two fields.
Most simply by concatenating them, say with a "!" as separator:
To store the entry: $my_hash{$param . '!' . $value} = [$planner, $process_flag];
(I.e., you store a reference as "value" part, but a concat as "key" part.)
To retrieve the entry: ($planner,$process_flag) = @{$my_hash{$param . '!' . $value}};

Thanks Peter. How do I store the data into the hash using the fetchrow_hashref ?

Your columns are (planner, param, value, processflag, so to get the whole table into a hash struct, you need something like
where %my_hash is the hash list where the table is to be stored, and a tab character is used as separator in the hash key (instead of a "!" sign)

'planner' and 'param' are not unique identifiers for the hash keys, so the first instance of planner/param:

will be over written by the second instance:

and so on for all planner/param rows. I think this structure might work better:

or: