Cursor for updating records

Rated 3.87/5 based on 963 customer reviews
salary_in); END; Of course, as new columns are added to the table, their contents will not automatically be displayed by this procedure.For full explanations of both of these answers, visit plsqlchallenge.com, register or log in, and click the Closed/Taken tab in Play a Quiz.

Suppose I want to write a program to display the last names of all employees.For a 9.2.0.5 database, I have been asked to add few columns and update them with new values from another table. To accomplish this, we researched 3 solutions: update via insert, merge statement and pl/sql with bulk collect. LAST SAVE EXCEPTIONS update (select col1, col2 from base_table ) set row = update_cols_tab(i) where rowid = row_id_tab(i); commit; exit when base_tab.count < 5000; end loop; commit; close base_table_cur; close new_data_cur; EXCEPTION WHEN dml_errors THEN FOR i IN 1 .. With this method, you don’t execute any updates, instead you create a new table with the old and new data. LAST LOOP row_id_tab(i) := new_data_tab(i).row_id; update_cols_tab(i).col1 := new_data_tab(i).new_col1; update_cols_tab(i).col2 := new_data_tab(i).new_col2; end loop; FORALL i IN base_tab. Here are the steps: DECLARE -- source table cursor (only columns to be updated) CURSOR base_table_cur IS SELECT rowid, col1, col2 FROM base_table; type base_type IS TABLE OF base_table_cur%ROWTYPE INDEX BY PLS_INTEGER; base_tab base_TYPE; -- new data CURSOR new_data_cur IS SELECT row_id, new_col1, new_col2 FROM new_data; type new_data_type IS TABLE OF new_data_cur%ROWTYPE INDEX BY PLS_INTEGER; new_data_tab new_data_TYPE; type row_id_type IS TABLE OF ROWID INDEX BY PLS_INTEGER; row_id_tab row_id_TYPE; TYPE rt_update_cols IS RECORD ( new_col1 new_data.new_col1%type, new_col2 new_data.new_col1 %type ); TYPE update_cols_type IS TABLE OF rt_update_cols INDEX BY PLS_INTEGER; update_cols_tab update_cols_type; dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT(dml_errors, -24381); BEGIN open base_table_cur; open new_data_cur; loop fetch base_table_cur bulk collect into base_tab limit 5000; fetch new_data_cur bulk collect into new_data_tab limit 5000; FOR i in base_tab. Here are the answers to the PL/SQL Challenge questions in last issue’s “Error Management” article: Answer 1: Choices (a), (c), and (d) all raise ORA-00001, also known as DUP_VAL_ON_INDEX inside PL/SQL code.

Leave a Reply