Fetching from a cursor variable, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Fetching from a cursor variable

Defining autonomous transactions, Defining Autonomous Transactions To ...

Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

Miller-urey''s experimental procedure, MILLER-UREY' S EXPERIMENTAL PROCEDU...

MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

Declaring subprograms, Declaring Subprograms   You can declare subprog...

Declaring Subprograms   You can declare subprograms in any PL/SQL subprogram, block, or package. But, you should declare subprograms at the end of the declarative part after a

Entering and exiting - autonomous transaction, Entering and Exiting If...

Entering and Exiting If you enter the executable part of an autonomous routine, the major transaction suspends. When you exit the routine, the major transaction resumes. To ex

Join and and in sql, JOIN and AND in SQL In this Section is all about ...

JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

For-loop -iterative control, FOR-LOOP While the number of iterations thr...

FOR-LOOP While the number of iterations through a WHILE loop is unknown till the loop completes, then the number of iterations through a FOR loop is known before the loop is ent

Using raise_application_error - user-defined exceptions, Using raise_applic...

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

Understanding nested tables, Understanding Nested Tables Within the data...

Understanding Nested Tables Within the database, the nested tables can be considered as one-column database tables. The Oracle stores the rows of a nested table in no specific o

Subprograms, Subprograms The PL/SQL has two types of subprograms known ...

Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

%found - explicit cursor attributes, %FOUND Subsequent to a cursor or ...

%FOUND Subsequent to a cursor or cursor variable is opened but before the first fetch, the %FOUND yields NULL. Afterward, it yields TRUE when the last fetch returned a row, or

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd