Re: Array Processing in PL/SQL

PL/SQL doesn't support array processing, but if you are using Version 7.3.x.x then PL/SQL 2.3 does support tables of records, which is effectively a lot *like* an array.

If you are on a previous release, then try looking at ORACLE PL/SQL Programming by Steven Feurstrein from O'Reilly & Associates.

One of the examples is how to create limited array emulation using packages. By limited, I mean you can only, in this example at least, define one type of array, i.e. varchar2, but then that *appears* to be all that you need at the moment.
(the code and explanation are too long for here, and I expect they would prefer you bought the book, rather than me re-distributing it) -:)

Good luck with it either way.

Mike

In article <3309E6C0.1504_at_iol.ie>, Chrysalis <cellis_at_iol.ie> writes
>Ed Jennings wrote:
>>
>> I have a table that contans 20+ million rows. I need to perform
>> an edit check on two columns, to verify data integrity. I can't
>> do it at load time because of data latency in loading the lookup
>> table. At the time of usage, I want to perform this check. I wrote
>> a stored procedure, but it takes hours. Using the trace facility
>> I determined that the SP is doing one fetch per row. Is there a way
>> to use array processing within the SP, or do I need to search for
>> an alterntive? Does anyone have any suggestions?
>>
>> Ed Jennings
>> Oracle DBA
>> EPS, Inc.
>> --
>> ~~~~~~~~~~~~~~~~~~~~~~~~~
>> jennings_at_dca.net
>
>A) No. PL/SQL does not support array processing.
>
>B) Try defining the checks as one or more table- or column-level
>constraints with the DISABLE option set during table creation.
>Then ENABLE the constraint after loading the data. This will be much
>more efficient (and uses set processing, which is even more efficient
>than array processing). It also has the advantage that you can trap the
>rows in error in a system table, which can then be used as the target
>table for a repair utility written, for example, in FORMS.
>Even if you have some already validated data in the table before you add
>the new rows, it will be more efficient to DISABLE the constraint(s)
>before loading the new rows and to re-ENABLE the constraint(s)
>afterwards, provided you are loading more than, say, 20% new rows.
>
>C) If your constraints cannot be so specified, then you will have to use
>a host program.

-- 
Mike Proctor                    Database Consultants International (UK)

Mike_Proctor_at_realworld.com      Oracle vision.
                                Only Oracle.
                                All the time.