Using a Cursor to Retrieve Data

Once a cursor has been opened, it can be used to retrieve data from the database. This is done using the FETCH statement. The FETCH statement retrieves the next row from the results set produced by the OPEN statement and writes the data returned to the specified host variables (or to addresses specified in an SQLDA structure). For example:

     perform until sqlcode not = 0  
         EXEC SQL
            FETCH Cur1 INTO :first_name
         display 'First name: ' fname
         display 'Last name : ' lname
         display spaces

When the cursor reaches the end of the results set, a value of 100 is returned in SQLCODE in the SQLCA data structure and SQLSTATE is set to "02000".

As data is fetched from a cursor, locks can be placed on the tables from which the data is being selected.


The ORACLE precompiler directive, MODE, affects the value put into SQLCODE when no data is found. For more information on the use of the MODE precompiler directive, refer to the Programmer's Guide to the ORACLE Precompilers.