Cursor for updating in oracle
I spend an inordinate proportion of design time of an ETL system worrying about the relative proportion of rows inserted vs updated.
I want to test on a level playing field and remove special factors that unfairly favour one method, so there are some rules: TEST (Update Source) - 100K rows TEST (Update target) - 10M rows Name Type Name Type ------------------------------ ------------ ------------------------------ ------------ PK NUMBER PK NUMBER FK NUMBER FK NUMBER FILL VARCHAR2(40) FILL VARCHAR2(40) Not many people code this way, but there are some Pro*C programmers out there who are used to Explicit Cursor Loops (OPEN, FETCH and CLOSE commands) and translate these techniques directly to PL/SQL.
If this rollback didn't cause the error, inconsistent results would abound.
The solution to your coding dilemma is to open the cursor before the savepoint, as in the following: declare 2 cursor c is select * from test; 3 l_rec test%rowtype; 4 begin 5 update test2 set b = 22; 6 open c; 7 savepoint my_savepoint; 8 loop 9 fetch c into l_rec; 10 exit when c%notfound; 11 rollback to 12 savepoint my_savepoint; 13 end loop; 14 end; 15 / PL/SQL procedure successfully completed. The result set pointed to by cursor C is consistent with respect to a point in time prior to the savepoint, so rolling back to the savepoint will not affect that cursor at all.
The Oracle PL/SQL language was designed to be a portable, high-performance transaction processing language that is tightly integrated with the SQL language.
It is rare, indeed, to find a PL/SQL program that does not either read from or make changes to tables in a database.
This section is targeted as a good starting point for those who are new to PL/SQL.What I love about writing SQL Tuning articles is that I very rarely end up publishing the findings I set out to achieve. We have a table containing years worth of data, most of which is static; we are updating selected rows that were recently inserted and are still volatile. For the purposes of the test, we will assume that the target table of the update is arbitrarily large, and we want to avoid things like full-scans and index rebuilds.With this one, I set out to demonstrate the advantages of PARALLEL DML, didn't find what I thought I would, and ended up testing 8 different techniques to find out how they differed. The methods covered include both PL/SQL and SQL approaches.Oracle doesn't know if it did—Oracle doesn't keep track. If that cursor remained open, it could well see records that don't, didn't, and won't ever exist.So, the rollback to savepoint would have wiped out records that your cursor in theory should and would be able to see. The general rule is: any rollback to a savepoint that happened before the open of the cursor will cause this error.
Search for cursor for updating in oracle:
Tables are made up of rows of data, each consisting of one or more columns, so it stands to reason that Oracle Database would make it as easy as possible to work with those rows of data inside a PL/SQL program.