SQL & PL/SQL

Hi guys,

Still learning PL/SQL so please take it easy on me.

Anyway i was wondering if someone might be able to point out where i'm going wrong here, i want to use BULK COLLECT for processing many rows in my code and i've read this method may give me performance gains over using a CURSOR FOR LOOP due to the nature in which rows are retrieved from the database.

The problem i have is that i can manage to fetch my rows from my cursor using BULK COLLECT, however once i have a particular row i'd like to reference some of that data for further processing. I'll try break this down :

I have a cursor defined to retrieve all rows from an employee table and defined a type;

CURSOR c_emps
IS
   SELECT *
   FROM employee;
   --
   TYPE c_emps_aat IS TABLE OF c_emps%ROWTYPE INDEX BY PLS_INTEGER;

I then have a variable which will hold my collection (have a feeling this is where i might be going wrong)

l_emps c_emps_aat;

Further down into my code, i open the cursor and start a loop, i then fetch the contents of the cursor using BULK COLLECT into my variable

OPEN c_emps
LOOP
--
   FETCH c_emps
   BULK COLLECT INTO l_emps LIMIT 200;

Now this is the part that is going wrong, i'd like to do some further processing and testing of each row retrieved by using one of the columns within the collection.
For example, lets assume the table 'employee' my cursor selects from has a column called 'employee_number', i'd like to perhaps use this information to decide what my next processing path may be, for example if the row i'm processing has a particular employee number.

So i thought i might be able to reference this information using the collection name with a period and then the column as i would when using a cursor, for example :

IF l_emps.employee_number IS NULL
THEN
--
   {logic}
--
ELSE
--
   {logic}
--
END IF;

When i try compile the procedure, it get the following compilation error :

Error(141,13): PL/SQL: Statement ignored
Error(144,96): PLS-00302: component 'EMPLOYEE_NUMBER' must be declared

Thanks in advance for any help.

Kind regards

Glen

Edited by: Glen Palmer on Jul 5, 2012 8:04 AM

Edited by: Glen Palmer on Jul 5, 2012 8:05 AM