Fetching from a cursor variable, PL-SQL Programming

Fetching from a Cursor Variable

The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown:

FETCH {cursor_variable_name | :host_cursor_variable_name}

INTO {variable_name[, variable_name]... | record_name};

In the next example, you fetch rows from the cursor variable emp_cv into the

user-defined record emp_rec:

LOOP

/* Fetch from cursor variable. */

FETCH emp_cv INTO emp_rec;

EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched

-- process data record

END LOOP;

Any variables in the related query are evaluated only when the cursor variable is opened. To change the product set or the values of variables in the query, you should reopen the cursor variable with the variables set to their new values. Though, you can use a different INTO clause on separate fetches with similar cursor variable. Each fetch retrieve another row from the similar result set.

The PL/SQL ensures the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each of the column value returned by the query related with the cursor variable, there should be a parallel, type-compatible field or variable in the INTO clause. The number of fields or variables should also equal the number of column values. Or else, you get an error.

The error occurs at the compile time, when the cursor variable is strongly typed or at run time, if it is weakly typed. At run time, the PL/SQL raises the predefined exception ROWTYPE_MISMATCH before the first fetch. Therefore, if you trap the error and execute the FETCH statement using a different INTO clause, then no rows are lost.

Posted Date: 10/4/2012 4:04:32 AM | Location : United States







Related Discussions:- Fetching from a cursor variable, Assignment Help, Ask Question on Fetching from a cursor variable, Get Answer, Expert's Help, Fetching from a cursor variable Discussions

Write discussion on Fetching from a cursor variable
Your posts are moderated
Related Questions
Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Updating by insertion Syntax : INSERT INTO ENROLMENT VALUES (SID ('S4'), 'Devinder', CID ('C1'));

Remote Operations: As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database: PROCEDURE

Hi,am developing a library system and relating all the table is somehow complex,could you kindly assist me

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

Using a Join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 JOIN CONTRACT ON EMPLOYEE

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with