Updating fetch in plsql


17-Jan-2017 23:27

'Already Exists'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line( R_C1.variable); END; END LOOP; END; MERGE INTO MAIN dst USING ( SELECT regions. REGION_NAME description FROM regions ) src ON = WHEN MATCHED THEN UPDATE SET dst.code_number = src.description WHEN NOT MATCHED THEN INSERT (id, code_number) VALUES (src.id, src.description) --Update the rows UPDATE MAIN SET ID=regions.REGION_ID, CODE_NUMBER=regions.[description] FROM MAIN JOIN regions ON MAIN. REGION_ID; --Insert the new ones INSERT INTO MAIN(ID, CODE_NUMBER) SELECT regions.

changed_buff.count INSERT INTO changed VALUES changed_buff( i ); END; --- EDIT --- version with cursor for small sets of data ----- Yes, for small sets of data bulk collecting doesn't give significant increase of the speed, and plain cursor with for.is sufficient in such a case.

changed_buff.count INSERT INTO changed VALUES changed_buff( i ); END; / can contain ten thousands and more rows, then this procedure might be slow, becasue it will consume a huge amount of PGA memory.

In such a case, an another approach with bulk collectiong in chunks is required.

An UPDATE in the cursor loop will update 1 row and 19 columns at once.

The Update is dependent on results of 19 procedures which are quite complex.Visit PL/SQL Challenge to read a complete explanation of the answers to this quiz. If the SELECT statement identifies more than one row to be fetched, Oracle Database will raise the TOO_MANY_ROWS exception.