Bulk bind errors

[LarrySteele]

I built script to load a table using the bulk collect...for all...save exceptions method. My exceptions statement handled the DML errors just fine. Then I added and added to the script. Even though I tested at each add, apparently I forgot to test when we have DML errors until last night. I've gone through my script and I've verified through numerous websites that my logic *appears* sound. Nevertheless, when I toss in data that will cause a DML error, the error is not recognized as DML and is identified as OTHERS.

Here's the complete script, in its entirety:

set feedback off;
set verify off;
set serveroutput on size 1000000;

-- set parameters...
def schem   = 'HRTDMART';
def tbl     = 'EE_COUNTRY';
def scrpt   = 'INSERT_EE_COUNTRY.SQL';
def dep_tbl = 'TABLE01'',''EE_LOC_CODE'',''EE_HOME';
def dep_cnt = 3;
def key1    = 'PERSID';

declare
  -- table
  type sap_tab is table of &&schem..&&tbl.%rowtype;
  sap_tbl  sap_tab := sap_tab();

  -- cursor so we can batch load our collection
  cursor csr_data is
    select p.persid,
           case when el.mail_site is null then eh.home_country
                when el.mail_site = 'FS' then eh.home_country 
                when el.mail_site = '99' then substr(el.loc_code,3,2) 
                else 'US' end as country_code
    from &&schem..pa0001 p, 
         &&schem..ee_loc_code el,
         &&schem..ee_home eh 
    where el.persid=p.persid 
      and eh.persid=p.persid;

  -- common variables 
  dep_bad_load    number :=1;
  dep_loads       number :=0;
  error_cnt       number :=0;
  error_cnt1      number :=0;
  error_idx       number;
  error_code      number;
  error_desc      varchar2(600 char);
  error_stats     number :=0;
  row_cnt         number :=0;
  bad_load        varchar2(1 char) := 'N';
  dml_errors      exception;
  pragma          exception_init(dml_errors, -24381);
  time_start      number := dbms_utility.get_time;
  time_stop       number;
  time_total      varchar2(20 char);
  script_name     varchar2(50 char) := '&&scrpt';
  table_name      varchar2(50 char) := '&&tbl';

begin
  
-- check the tables we depend on...
  select sum(bad_load), 
         sum(loads)
  into dep_bad_load,
       dep_loads
  from (
        select case bad_load when 'N' then 0 else 1 end bad_load,
               1 loads 
        from &&schem..hrdm_load_stats 
        where load_date = to_date(sysdate) 
          and table_name in ('&&dep_tbl'));

  if  nvl(dep_bad_load,0) != 0 or nvl(dep_loads,0) != &&dep_cnt then  -- source table problem
    
    -- record bad load stat and error for this table
    insert into &&schem..hrdm_load_stats
      (
        script_name,
        table_name,
        insert_count,
        error_count,
        bad_load,
        load_date,
        load_time
      )
    values
      (
        script_name,
        table_name,
        0,
        0,
        'Y',
        to_date(sysdate),
        to_char(sysdate, 'hh24:mi:ss')
      );

    insert into &&schem..hrdm_load_errors
      (
        script_name,
        table_name,
        error_code,
        error_desc,
        key_data,
        load_date
      )
    values
      (
        script_name,
        table_name,
        '-20002',
        'ORA-20002: TABLE LOAD HOLD FOR ' || table_name, 
        '',
        to_date(sysdate)
      );

    -- raise user defined error
    raise_application_error (-20002, 'TABLE LOAD HOLD FOR ' || table_name);
  end if;
    
  begin
    -- if we make it here, our source tables loaded okay

    execute immediate 'truncate table &&schem..&&tbl';

-- bulk collect (loads into memory) from cursor
    open csr_data;
      loop
        fetch csr_data
        bulk collect into sap_tbl limit 25000;
        exit when sap_tbl.count = 0;

        -- forall (bulk save from memory into our table) recording exceptions if they occur
        forall i in sap_tbl.first..sap_tbl.last save exceptions
          insert into &&schem..&&tbl values sap_tbl(i);

      -- keep track of rows inserted
        row_cnt := row_cnt + sql%rowcount;   -- just in case we didn't have an error

      end loop;
    close csr_data;

  exception
      -- record individual record errors in the load_errors table
      when dml_errors then
          row_cnt := sql%rowcount;    -- pick up the rowcount from last process sql (forall...)
          error_cnt := sql%bulk_exceptions.count;
          error_cnt1 := error_cnt1 + error_cnt;

          for e in 1..25 loop  --error_cnt loop
              error_idx := sql%bulk_exceptions(e).error_index;
              error_code := sql%bulk_exceptions(e).error_code;
              error_desc := sqlerrm(-error_code);

              insert into &&schem..hrdm_load_errors
                (
                  script_name,
                  table_name,
                  error_code,
                  error_desc,
                  key_data,
                  load_date
                )
              values
                (
                  script_name,
                  table_name,
                  to_char(-error_code),
                  error_desc, 
                  'PERSID: "' || sap_tbl(error_idx).PERSID || '"',
                  to_date(sysdate)
                );
          end loop;
      when others then
      --null;
      bad_load := 'Y';  -- unexpected/unhandled error: bad load to investigate
  end;

-- process load stats
  select count(1)
  into row_cnt
  from &&schem..&&tbl.;

  if row_cnt > 0 then
    error_stats := (error_cnt / row_cnt) * 100;
  else
    error_stats := 100;   -- if no rows inserted, then 100% load failure
  end if;

  if error_stats > 10 then    -- more than 10% failed to load
    bad_load := 'Y';
  end if;

  insert into &&schem..hrdm_load_stats
    (
      script_name,
      table_name,
      insert_count,
      error_count,
      bad_load,
      load_date,
      load_time
    )
  values
    (
      script_name,
      table_name,
      row_cnt,
      error_cnt,
      bad_load,
      to_date(sysdate),
      to_char(sysdate, 'hh24:mi:ss')
    );
  commit;

  time_stop := dbms_utility.get_time;
  time_total := to_char((time_stop - time_start)/100);
  
-- post user defined error (if it occurs) to the screen for review.  
-- otherwise post results to the screen for review 
  begin
    if bad_load = 'Y' then
      raise_application_error (-20001, 'BAD LOAD ERROR FOR ' || table_name);
    else
      dbms_output.put_line(script_name || ' loaded.  ' || 
                           row_cnt || ' rows inserted - ' || 
                           error_cnt1 || ' errors.');    
    end if;
  exception
    when others then
      if to_char(sqlcode) = -20001 then
        -- our error code
        dbms_output.put_line(sqlerrm);
      else
        -- some other error occured...
        dbms_output.put_line(script_name || ' LOAD ERROR: ' || to_char(sqlcode) || '-' || sqlerrm);
      end if;
  end;
  
exception
  when others then
      if to_char(sqlcode) = -20002 then
        -- our error code
        dbms_output.put_line(sqlerrm);
      else
        -- some other error occured...
        dbms_output.put_line(script_name || ' LOAD ERROR: ' || to_char(sqlcode) || '-' || sqlerrm);
      end if;  
  
end;
/

In the first iteration of this script (of course, that's nowhere to be found), I could inject a DML error by changing the country code for select records so a code with four characters (this is a varchar2(3) field). That would be recognized as a DML error and would go through the DML error handler to record the error.

I'd appreciate any assistance in getting Oracle to recognize my DML errors as DML errors.

Larry

 

[LarrySteele]

Oops...seems I forgot some very important information...

Here's the error I receive when I generate the DML error:

[tt]ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind[/tt]

Furthermore, the good records are no longer written to the DB.

 

[LarrySteele]

I think I figured out my logic error here...

This worked just fine when I attempted to BULK BIND to simple SQL. In that case, it bulk loaded everything into memory and was eventually processed with my FOR ALL... statement, recording errors as applicable.

I found this worked quite easily as long as the SQL dataset wasn't too big. Since I was loading the whole dataset into memory, there was a physical limit (not to mention a practical limit).

Anyway, I had a few tables that were too big for memory. With some research, found the method of using a cursor, then BULK BINDing with a limit. From what I gathered, limits can be set when loading from a cursor, but not a table.

Since my bind variable is defined as the destination %ROWTYPE, anything that could cause a DML problem in the FOR ALL insert would cause the same problem in the BULK BIND statement. In other words, the second column in sap_tbl is limited to 3 characters and trying to insert 4 will cause an exception - and this is before the FOR ALL...INSERT...SAVE EXCEPTIONS.

Quite reasonable now that I'm looking at it with a clearer(?) head.

Now, I wonder what my options are at this point...

Larry

 

[Thargy]

Larry,

I think that many moons ago I gave an example of Oracle's DML error logging and how to use it.

That might be an option.

Regards

T

 

[LarrySteele]

T - thanks, but I do believe Oracle introduced that in 10g, didn't they? I'm using 9i.

Funny thing that. I'm using 9i in *development* and our *production* servers are using 10g. Can't seem to be able to convinced *them* to splurge for the update on dev.

Here's some rambling thoughts (read: don't look for lucid thoughts here )...

Currently I'm defining the table variable based on the output table definition. Obviously the cursor is defined from the input table(s). If there are column def differences between input table(s) and output tables, then we'll cause an exception when trying to load the table variable.

Seems that the right approach would be to define the table definition after the input table(s). In this way, the table variable and cursor would have the same definition and in theory shouldn't have DML errors. If we do have column definition deltas, then they would generated DML errors when trying to insert into our table (and this is were I want to capture them).

I just changed the following:

type sap_tab is table of [b]&&schem..&&tbl[/b].%rowtype;

to

type sap_tab is table of [b]csr_data[/b]%rowtype;

(of course, moving the statement after the cursor def!

After making that move, I successfully wrote records to the output table and to the errors table when forcing a data error. I still have some more work to do as it's not recording to the load stats table, but that's beyond the scope of the original question.

Thanks for posting,
Larry

 

[Thargy]

Larry,

I sit corrected, this is indeed the 9i forum - oops.

Anyhow, I'm a firm believer in not writing something that Oracle does for free. So, can you download Oracle 10g to a windoze box, install it, and develop your DML checking routines in 10g (plagiarising/using DML error logging as required). Once developed and tested, you can then run on prod.

To me, that seems less hassle than all the work you're staring in the face. I'll be climbing the wooden hills shortly, but I'll check in tomorrow to see what progress you've made.

Good evening to you.

Regards

T

 

[LarrySteele]

T,

Thanks for the suggestion. Unfortunately that option's not available.

Things are so close... Yet still so far away.

I'm switching the country code value for my record in the SELECT statement to "1234" which will cause an "ORA-06532: Subscript outside of limit" exception and it is indeed caught by the DML exception handler. This part works well. What does not work is that as soon as it hits the exception it no longer gracefully returns to fetch another block.

If I run the script as is (with my exception generator), the script will only load 49,999 rows of the 108k. If I change the limit to 100, then it only loads 41,999. Apparently my record is somewhere between 25,001 and 50,000.

What I expected was to go through the exception handler then return to bulk collect the next group of records.

The second problem is that after the exception handling is done, I expected the script to start processing at the line the begins with [tt]-- process load stats[/tt]. If it were to do that, it would at least record and announce (via my dbms_output statements) that there was a problem.

===========================

I know this worked fine when I just did the flat bulk collect from the database rather than from the cursor. In that scenario, all records came in at once and the block of code would have exited as soon as it was complete. The difference is that previously *all* records were processed together rather than in chunks. So in both cases, it processed all records in the block and exited after processing a block that contained an exception.

I wonder what would happen if I wrapped the [tt]for all...[/tt] in its own begin...exception...end block. As a separate block, I would expect that the process would return to the calling block after completion (whether it ran into an exception or not).

Time for more testing...

 

[LarrySteele]

Something's got me stumped with error handling...

I did some research, and sites I found told me what I expected, that when an inner block has a handled exception, it handles same and returns control to outer block.

Here's sample script that shows this:

set serveroutput on
declare
  myerror exception;
begin
  begin
    if true then
      raise myerror;
    end if;
  exception
    when myerror then
      dbms_output.put_line('inner block error');
  end;
  
  dbms_output.put_line('post block message');
exception
  when myerror then
    dbms_output.put_line('outer block error');
end;
/

Not surprising, the output is:

inner block error
post block message

The way my handler is working, it would give me "inner block error", but not would not give me "post block message."

Here's what my revised block looks like:

-- bulk collect (loads into memory) from cursor
    open csr_data;
      loop
        fetch csr_data
        bulk collect into sap_tbl limit 25000;
        exit when sap_tbl.count = 0;

[b]dbms_output.put_line('Block start');[/b]

          begin
            -- forall (bulk save from memory into our table) recording exceptions if they occur
            forall i in sap_tbl.first..sap_tbl.last save exceptions
              insert into &&schem..&&tbl values sap_tbl(i);

            -- keep track of rows inserted
              row_cnt := row_cnt + sql%rowcount;   -- just in case we didn't have an error


          exception
              -- record individual record errors in the load_errors table
              when dml_errors then
                  row_cnt := sql%rowcount;    -- pick up the rowcount from last process sql (forall...)
                  error_cnt := sql%bulk_exceptions.count;
                  error_cnt1 := error_cnt1 + error_cnt;

                  for e in 1..25 loop  --error_cnt loop
                      error_idx := sql%bulk_exceptions(e).error_index;
                      error_code := sql%bulk_exceptions(e).error_code;
                      error_desc := sqlerrm(-error_code);

                      insert into &&schem..hrdm_load_errors
                        (
                          script_name,
                          table_name,
                          error_code,
                          error_desc,
                          key_data,
                          load_date
                        )
                      values
                        (
                          script_name,
                          table_name,
                          to_char(-error_code),
                          error_desc, 
                          'PERSID: "' || sap_tbl(error_idx).PERSID || '"',
                          to_date(sysdate)
                        );
                  end loop;
            end;

[b]dbms_output.put_line('Block end');[/b]

      end loop;
    close csr_data;

--      when others then
--        null;
      
      --bad_load := 'Y';  -- unexpected/unhandled error: bad load to investigate
  end;

If a block of data is good, I get "Block start" and "Block end." If the block of data is bad, I get "Block start" but do not get "Block End" even though I should (I'm pretty sure I should).

Am I missing something in my error handling???

 

[LarrySteele]

Script troubleshooting 101: READ THE ERROR MESSAGE!

The latest error I was getting was [tt]ORA-06532: Subscript outside of limit[/tt].

I kept overlooking the message because I knew I was inserting an error and just presumed that the message related to my error.

The problem was actually an unhandled error in my error counting loop:

for e in 1..25 loop  --error_cnt loop

The purpose for this loop was to limit the amount of errors I write per table load. If the source table is loaded with problem data (we've seen some really messy data come down), I don't need to see 150,000 records with the same error message. Just need enough rows so I can look for the patterns.

At this point, I needed to exit my loop when there's nothing left to process. Once I set that up, my errors loaded just fine, my stats loaded just fine, and my table loaded just fine (save for the bad records, of course).

Wow, what a way to end the last work day of the year, eh?

 

[Thargy]

Larry,

thanks for being so considerate - few folks bother to update threads with their outcomes.

Yup, I know what you mean. Tomorrow I move to a new office, so that'll be nice and relaxing, just to get me ready to kick-off in 2011.

I wish you and yours a prosperous and healthy new year.

Regards

T