Loading data - SQL: is there any error? [var]

Series about loading data using SQL and LOG ERRORS continued. The more get you in the wood, the more interested is becomes : D

Loading data - SQL

Loading data - SQL: is there any error? [GTT, tab]
Previously, I used the Global Temporary Table and the regular table to verify if an error occured. Today, I will use global PL/SQL variables for the same job.

I remind you that I use the standard HR scheme. For the purposes of the test, I create an empty myEmp table as a copy of the Employees table. Then I reduce length of the last_name column to 9 characters (there are 4 records with last_name > 9 in the employees table) to force an error. We have 107 records in the source table (in my example), of which we expect 4 errors.

So how do you check whether loading data raised an error using?

1.  In the package body I have declared the gnCnt variable to record the number of records. You can also declare it in the specification, if you prefer :)

2. I created the function upd_cnt (pcnt number) to update the global variable gnCnt. The function sets the variable gnCnt to the value obtained in the input parameter pnCnt.

function  upd_cnt(pncnt number)  return number is

begin

    -- all rows counter
    gnCnt := pncnt; 

    -- counter for test
    gtest := nvl(gtest,0) + 1;

       
    return 0;
    exception when others then dbms_output.put_line(sqlerrm);

end;

3. I used INSERT ALL statement to load the data. For the first record I call the function upd_cnt with the parameter number of records. The function always returns 0 . I use it to always make a row insert.

insert all

    when rownum = 1 and upd_cnt (cnt) = 0 then into myEmp ( 

        employee_id (...)

4. I calculate the number of records in the SELECT clause of the INSERT ALL statement using the analytic function count (*) over

select rownum , count(*) over () cnt,  employee_id (...)

5. The remaining lines are loaded without any weirdness.

 else

    into myEmp( employee_id,

6. Inserted rows:
   ncnt_ins := sql%rowcount.

7. Erros = count of all rows - inserted rows

gnCnt - ncnt_ins

Of course, I use a clause

log errors reject limit unlimited

Now run:

begin

    delete from myEmp;
    delete from err$_myEmp;
    commit;    

    test_log_err.gtest := 0;
   
end;
/

Function upd_cnt executed 1 time/s.

all rows: 107  , Inserted: 103, Errors: 4

PL/SQL procedure successfully completed.

And voila! Done! 107 all records, 103 records inserted, 4 errors.

select count(*) from Employees;

  COUNT(*)

----------

       107

select count(*) from myEmp;

 COUNT(*)

----------

       103

select count(*) from err$_myEmp

 COUNT(*)
----------

         4

I still need to explain the gTest variable. This variable is set in the upd_cnt function to prove that the function will run only once. PL/SQL functions in SQL are very inefficient, but one run should not have a significant impact on performance.

However, we must remember that if we insert data into more than one table - we will not be able to calculate the number of errors for each table separately. We will be able to calculate only the total number of inserted and incorrect records. However, if you do not need such details, the solution is simple and easy to apply.

Code:

create or replace package test_log_err is
--gnCnt number ; -- all rows counter
gTest number; --counter forc checking how many times function is executed

function  upd_cnt ( pncnt number) return number ;
procedure ins;
end;
/

-----------------------------------------------------------------------------
create or replace package body test_log_err is

gnCnt number ; -- all rows counter
-----------------------------------------------------------------------------
function  upd_cnt(pncnt number)  return number is
begin
    -- all rows counter
    gnCnt := pncnt;
    
    -- counter for test
    gtest := nvl(gtest,0) + 1;
       
    return 0;
    exception when others then dbms_output.put_line(sqlerrm);
end;
-----------------------------------------------------------------------------
procedure ins is

 ncnt_ins number;

begin
 
 insert all
    when rownum = 1 and upd_cnt (cnt) = 0 then into myEmp ( 
        employee_id,
        first_name,
        last_name,
        email,
        phone_number,
        hire_date,
        job_id,
        salary,
        commission_pct,
        manager_id,
        department_id) values ( employee_id,
        first_name,
        last_name,
        email,
        phone_number,
        hire_date,
        job_id,
        salary,
        commission_pct,
        manager_id,
        department_id)
        log errors reject limit unlimited
    else
    into myEmp( employee_id,
        first_name,
        last_name,
        email,
        phone_number,
        hire_date,
        job_id,
        salary,
        commission_pct,
        manager_id,
        department_id) values ( employee_id,
        first_name,
        last_name,
        email,
        phone_number,
        hire_date,
        job_id,
        salary,
        commission_pct,
        manager_id,
        department_id)
        log errors reject limit unlimited

 select rownum , count(*) over () cnt,  employee_id,
            first_name,
            last_name,
            email,
            phone_number,
            hire_date,
            job_id,
            salary,
            commission_pct,
            manager_id,
            department_id
    from employees ;
    
    ncnt_ins := sql%rowcount;
    
    dbms_output.put_line('Function upd_cnt executed '|| gTest || ' time/s.');
    dbms_output.put_line ('all rows: '||nvl(gnCnt,0) ||'  , Inserted: ' ||ncnt_ins||', Errors: '||to_char(nvl(gnCnt,0) - ncnt_ins)); 
end;
end;
/