Bulk loading in Oracle - %ROWTYPE and %TYPE

I decided to finally jump in to doing some bulk loading in Oracle a bit ago and ran in to a weird problem with dbms_output.put_line. Basically it was complaining that I was passing the wrong arguments to put_line. I searched around on Google for help, and found quite a bit on bulk loading, but none seemed to address the problem I was having. Basically, how to reference the actual data.

My original code was using %ROWTYPE, but all the examples I could find were sending that collection off to another procedure. That procedure wasn't outlined anywhere, so I had no idea how they were displaying the data. Below are two different PL/SQL scripts. The first uses %ROWTYPE and the second uses %TYPE. Both have a working dbms_output.put_line so you can see how to reference the data.

The data is from two different tables, ticket_headers_all and user_settings. The first table is just a ticket system containing a header_id, summary, and a few other fields. The user_settings table has a user_id and some other fields. If requested, I'll move these over to the familiar EMP/DEPT schemas instead.

%ROWTYPE

DECLARE
 -- Create the container for our data
 TYPE tickets_att IS TABLE OF ticket_headers_all%ROWTYPE
  INDEX BY binary_integer;

 -- And create a variable of the container type
 l_tickets tickets_att;

BEGIN
 -- First we load the collection. Consider LIMIT if you have a lot of data.
 SELECT * BULK COLLECT INTO l_tickets
 FROM ticket_headers_all;

 FOR ticket_index IN l_tickets.first .. l_tickets.last
 LOOP
  -- Output the summary field
  dbms_output.put_line(l_tickets(ticket_index).summary);
 END LOOP;
END;

%TYPE

DECLARE
 -- Declare the collection
 TYPE users_att IS TABLE OF user_settings.user_id%TYPE
  INDEX BY binary_integer;

 -- Declare a variable of the collection type
 l_users users_att;

BEGIN
 SELECT user_id BULK COLLECT INTO l_users
 FROM user_settings;

 FOR user_index IN l_users.first .. l_users.last
 LOOP
  -- Print all the user_ids
  dbms_output.put_line(l_users(user_index));
 END LOOP;
END;

Catches
There are a few things to be aware of. These statements will throw an exception if there are no results. You can get around this pretty easily by adding an if clause around the FOR loops to check the count. For example, if(user_index.Count > 0). There are also different types of loops that you can use, some better than others depending on your data.

As alluded to in the comment, you should consider using LIMIT to restrict how much memory is used by your bulk collect. Without a limit, it's going to use as much memory as it can. With a small table, this may not be a big issue for you, but tables do grow so you should include a default size (like 1000 rows).

Hopefully this helps some of you. It's a very basic problem and easy to fix, but if you don't know what to search for, it can be hard to figure out why put_line is throwing an error. As to the cause of the error itself? put_line doesn't handle a row of data, which is what I was sending it. Woops.