Search code examples
sqloracle-databasearrayscursor

How to populate a single-dimension array with result of a query?

I have to populate the result of a query into an array in my pl/sql proc.

For example, I have a employeeId empArr (TYPE empArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;) and i want to populate this array with the result of a sql statement : 

    select empId where dept = 'accounts'.

Is there a way to do this ? Or would you suggest using cursors to do the job ?

Thx Cshah

Solution 
  • DECLARE
  TYPE empArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  myEmpArr  empArr;

BEGIN

  SELECT empID
    BULK COLLECT INTO myEmpArr
    FROM empTable
    WHERE dept='accounts';

  -- Do your stuff

END;
/