Ask the Expert

Cursors: %type and %rowtype explained

Please explain in detail cursors and the method to be used to fetch information with respect to %TYPE and %ROWTYPE variables.

    Requires Free Membership to View

A cursor is simply a returned result set from a query. In PL/SQL, you can step through each row of a that result set and perform some operations on that data. The %TYPE directive will be used to declare a variable of the same type as the column of the table. This is useful when the table's column changes its datatype. You don't have to change your code to match! The %ROWTYPE directive is similar, except that it declares a record that corresponds to one row of the table. Hopefully, a simple example will help.

   -- Declare two variables as the first and
   -- last name of the students. This can be
   -- found in the STUDENTS table. 
   first   students.first%TYPE;
   last    students.last%TYPE;
   -- Declare a record variable which will
   -- correspond to the columns of one row of the
   -- STUDENTS table.
   student students%ROWTYPE;
   -- Declare a cursor to fetch first & last
   CURSOR c1 IS select first,last from students;
   -- Declare a cursor to fetch all columns
   CURSOR c2 IS select * from students;
   -- open c1 cursor
   OPEN c1;
   -- loop through the result set and display output
      FETCH c1 INTO first,last;
      DBMS_OUTPUT.PUT_LINE('First: ' || first);
      DBMS_OUTPUT.PUT_LINE('Last: ' || last);
   -- close c1 cursor
   CLOSE c1;

   -- open c2 cursor
   OPEN c2;
   -- loop through the result set and display output
      FETCH c2 INTO student;
      DBMS_OUTPUT.PUT_LINE('First: ' ||
      DBMS_OUTPUT.PUT_LINE('Last: ' || student.last);
   -- close c2 cursor
   CLOSE c2;

For More Information

  • What do you think about this answer? E-mail the editors at with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: