Cursor updating table oracle

Rated 4.54/5 based on 686 customer reviews

In PL/SQL, the context area is controlled by Cursor.

A cursor contains information on a select statement and the rows of data accessed by it.

For example: When you execute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected.

If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement.

These cursors should be defined in the declaration section of the PL/SQL block.

Thus, any TCL operation on the cursor record set has to be done only after fetching all the rows from the cursor context area using a loop process similar to the above listing example.

The row limiting clause introduced in the Oracle version 12c, Fetch First ..

Once we open a cursor having a FOR UPDATE clause, all the rows returned by the SELECT statement are locked for our changes until a commit or a rollback is placed to release the lock.

After a TCL operation is performed, the cursor pointer gets reset and the cursor will be no longer accessible, thus results in an error when fetched further as shown below.

Leave a Reply