At times when you are programming in PLSQL you would want to fetch multiple records into an ARRAY or some collection. One approach to do is to loop through the result of the SQL query and then keep adding each record to the ARRAY or Collection. PLSQL provides another construct to perform the same operation in a much more time efficient manner by using the BULK COLLECT INTO construct. In the first approach there has to be frequent switching between the PLSQL and the SQL engines which causes overhead but while using BULK COLLECT INTO that switching overhead is reduced considerably. To know more about this difference and to see the time variations in both the cases, I would recommend you to read this.

Using BULK COLLECT INTO construct:

Consider a STUDENT table with the following data:

I want to fetch all the Students age into a different array which I would do using BULK COLLECT INTO as shown below:

[sql]
SET SERVEROUTPUT ON;
DECLARE
–Define a Collection Type
TYPE NumberArray IS TABLE OF STUDENT.AGE%TYPE;

–Declare a variable of the Type defined above.
–This holds the student age fetched.
StudentIds NumberArray;
BEGIN
/*
Select age from the table and bulk collect them in to the
variable of collection type.
*/
SELECT age BULK COLLECT INTO StudentIds FROM STUDENT;

/*
Looping through the collection just to confirm that the
data has really been fetched into it.
*/
for i in 1..StudentIds.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(StudentIds(i));
END LOOP;
END;
[/sql]

And the output would be:

Note: I have used the SQL Worksheet tool provided with JDeveloper to execute this example.

Lot of times you have tried to use ROWNUM clause along along with the ORDER BY clause and must have been treated with a different output than you expected. You would expect the ROWNUM to be applied after the results have been ordered by the column, but instead it applies the ROWNUM and then does an order by.

Lets look at an example:
Support you had the following data in a student table:

And we want to find out the youngest student, you would immediately write:
[sql]SELECT * FROM student WHERE ROWNUM=1 ORDER BY age;[/sql]
which would give the output:

But looking at the data above John/David would should have been the youngest. As I said before, the ORDER BY clause is applied after the ROWNUM selects one row. Lets edit the query a bit and try:
[sql]SELECT * FROM (SELECT * FROM student ORDER BY age) WHERE ROWNUM=1;[/sql]
which would give us the correct output:

The idea here is to first order the rows/records by moving the order by query as a nested sql and then applying ROWNUM on the resultant data.