Arrays in Oracle PL/SQL to store rows

oracle10g, plsql

I'm trying to perform something in Oracle/PLSQL where I want to select some stuff from the db via a cursor and store it into an array of rows, which I can later process. Searched on google but to no avail. Will appreciate any help here.

What I have in mind

  1. Use cursor to read through a db
  2. store rows which fits a particular criterial into an array of rows
  3. sort the array of rows
  4. print the items in the array of rows using dbms_output.put_line

Best Solution

Take a look at PL/SQL Collections. You can create a collection of a PL/SQL record, where the record is defined as the %ROWTYPE of your table. Then you load that table & perform operations on it.

For example:

DECLARE CURSOR emp_cursor IS SELECT employee_id, employee_name from employees; TYPE employeeArrayType IS TABLE OF a_cur%ROWTYPE; employeeArray employeeArrayType;BEGIN  OPEN emp_cursor;  LOOP    FETCH emp_cursor BULK COLLECT INTO employeeArray LIMIT 100;    EXIT WHEN emp_cursor%NOTFOUND;  END LOOP;  CLOSE emp_cursor;  -- Now you can do work with employeeArrayEND;

The code declares my cursor and collection, then loads the collection using a FETCH..BULK COLLECT process in a loop (which is advisable with large amounts of data).

However, Codo's comment applies here - it's much easier if you filter and sort your data as part of the WHERE clause & ORDER BY clause of your SQL query. When you have a database engine behind you there's not very many cases where it would be better to do set operations in a procedural language. A few ETL processes may require it, but for your question, I really would recommend doing your work in SQL then working with the final resultset.

That being said, sorting PL/SQL collections of records is not as easy as one would like. For an overview of ways to sort a collection, take a look at the AMIS technology blog (and here for part 2).