Start Db cursor updating in same table oracle

Db cursor updating in same table oracle

rows of data the transaction is committed and the cursors released thus forcing a switch of rollback segments and hopefully avoiding the ORA-01555 error. CLOSE_CURSOR(cursor_name); sql_com:='INSERT INTO temp_tab SELECT rownum, rowid FROM '||tab_name; cursor_name:=DBMS_SQL. PARSE(cursor_name,sql_com,dbms_sql.v7); ret:=DBMS_SQL. PUT_LINE('Rowcount:'||TO_CHAR(rowcount)||' Maxrows:'||TO_CHAR(maxrows)); OPEN proc_row(rowcount,maxrows); FETCH proc_row into temp_id; LOOP EXIT WHEN proc_row%NOTFOUND; sql_com:='UPDATE '||tab_name||' SET entry_ts='||chr(39)||new_date||chr(39)||' WHERE rowid='||chr(39)||temp_id||chr(39); cursor_name:=DBMS_SQL. PARSE(cursor_name,sql_com,dbms_sql.v7); ret:=DBMS_SQL. PUT_LINE(to_char(rowcount)); END LOOP; CLOSE proc_row; COMMIT; maxrows:=rowcount rows; IF rowcount=maxcount 1 THEN EXIT; END IF; END LOOP; END; / Figure 15: Example procedure to perform conditional update in batch mode.

Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data. A simple or compound DML trigger that fires at row level can access the data in the row that it is processing.

For details, see "Correlation Names and Pseudorecords". With a column list, the trigger fires only when a specified column is updated.

create or replace procedure update_test_table3(new_owner varchar2, old_owner varchar2, new_date date, new_ddl date) as cursor get_owner_rec (old_owner varchar2) is select rowid, owner, created, last_ddl_time from test_table where owner=old_owner; rec1 get_owner_rec%ROWTYPE; begin for rec1 in get_owner_rec(old_owner) loop update test_table set owner=new_owner, created=new_date, last_ddl_time=new_ddl where rowid=rec1.rowid; end loop; end; / Figure 18: Using ROWID to Speed Processing Using ROWIDs processing time was a consistent 27 seconds on the average vice the 30 second average of the code using the FOR UPDATE and WHERE CURRENT clauses.


 
Dzisiaj jest:
05-Dec-2016 06:21