Pcanywhere error updating record block
Pcanywhere error updating record block - Partnervermittlung nordrhein westfalen
In this column, I take a look at each of these approaches and describe their strengths and weaknesses.
In the revised CHANGE_SALARY_FOR procedure in Listing 2, I perform the required salary adjustment operations in the following steps: Code Listing 2: CHANGE_SALARY_FOR using FORALL and SAVE EXCEPTIONS CREATE OR REPLACE PROCEDURE change_salary_for ( dept_in IN employees.department_id%TYPE , pct_increase_in IN NUMBER , fetch_limit_in IN PLS_INTEGER ) IS bulk_errors exception; PRAGMA EXCEPTION_INIT (bulk_errors, -24381); CURSOR employees_cur IS SELECT employee_id, salary FROM employees WHERE department_id = dept_in; TYPE employee_tt IS TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER; employee_ids employee_tt; TYPE salary_tt IS TABLE OF employees.salary%TYPE INDEX BY BINARY_INTEGER; salaries salary_tt; BEGIN OPEN employees_cur; LOOP FETCH employees_cur BULK COLLECT INTO employee_ids, salaries LIMIT fetch_limit_in; FOR indx IN 1 .. COUNT LOOP salaries (indx) := compensation_rules.adjusted_compensation ( employee_id_in = pct_increase_in ); END LOOP; FORALL indx IN 1 .. COUNT SAVE EXCEPTIONS UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx); EXIT WHEN employees_cur%NOTFOUND; END LOOP; EXCEPTION WHEN bulk_errors THEN FOR indx IN 1 .. COUNT LOOP q$error_manager.register_error ( error_code_in = 1. Use the FORALL statement to update all the rows just fetched with the new salary.
The salary adjustment procedure (the implementation of which is not shown in this column because it is irrelevant) has the following header: My nested block implementation of this functionality is shown in the CHANGE_SALARY_FOR procedure in Listing 1.
With this approach, I fetch one row at a time from the employees table, using a cursor FOR loop.
Microsoft Dynamics CRM 2015 will alert you when there are script errors and prompt you to manually choose to send the error report. The error notifications will now be automatically be sent to Microsoft and the script error pop-up will no longer appear.
These error messages, although helpful for Microsoft, may not necessarily apply to your everyday user.
Now let’s move on to SAVE EXCEPTIONS versus LOG ERRORS (DML error logging).
In other words, you have decided you need to use BULK COLLECT and FORALL, and you need to continue past exceptions.
I place all the code within the loop’s body inside a nested block, which includes an exception section.
Code Listing 1: CHANGE_SALARY_FOR using nested block PROCEDURE change_salary_for ( dept_in IN employees.department_id%TYPE , pct_increase_in IN NUMBER ) IS CURSOR employees_cur IS SELECT employee_id, salary FROM employees WHERE department_id = dept_in; BEGIN FOR rec IN employees_cur LOOP BEGIN rec.salary := compensation_rules.adjusted_compensation ( employee_id_in = In the executable section of this nested block, I adjust the compensation and then perform the update for that single row.
This procedure demonstrates how I can continue past an exception by using a nested block to stop the propagation of an exception, handle it, and then continue executing statements within the enclosing block’s executable section.
That “traditional”nested block approach is very readable and quite elegant in its formulation, but it retrieves and then updates data row by row.
You don’t have to declare collection types to perform the bulk SQL operations and write all that extra code to deal with the “bulk”errors you might encounter.