Inserting updating records using pl sql cursor
The row limiting clause introduced in the Oracle version 12c, Fetch First ..
Rows Only does not seem to work with the FOR UPDATE clause.
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.
Instead, it gives you a cryptic message such as "procedure created with compilation errors".
This clause becomes meaningless if we use a single table SELECT clause as the minimum Oracle has provided us with the WHERE CURRENT OF clause for both DELETE and UPDATE statements inside a cursor’s range to make changes to the last fetched row(s) from the cursor with an ease.
When we want to update or delete the cursor fetched row(s) from the database, we don’t have to form In the below code listing, the employees joined before the year 2000 are archived and during the cursor process, no other session is permitted to make any changes to those employees using the FOR UPDATE clause.
For example: CREATE TABLE T3 ( a INTEGER, b INTEGER ); CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER) AS BEGIN b := 4; INSERT INTO T3 VALUES(a, b); END; .
run; DECLARE v NUMBER; BEGIN addtuple3(10, v); END; .