Continuing Inserts in Oracle When Exception Is Raised

Continuing Inserts in Oracle when exception is raised

Using PLSQL you can perform each insert in its own transaction (COMMIT after each) and log or ignore errors with an exception handler that keeps going.

Oracle: Continuing Inserts with failing deferred constraints

Well, it sounds like those columns are deferrable for a good reason, because you can't know which rows will fail the constraint until you've inserted all of them.

I'm not sure if it's a best practice, but what I would do is: after inserting all your records into my_table, before you commit, log and delete all the rows that would fail the constraint check, e.g.:

spool bad_rows.txt;
select * from my_table where parent_id not in (select id from my_table);
spool off;
delete from my_table where parent_id not in (select id from my_table);

I can't tell from your question whether this is a one-time data migration, or a regular thing. If you plan to do it often, you might consider creating an exception table to catch the offending rows, then disable the constraints before loading, re-enable them with the EXCEPTIONS clause (and maybe SET CONSTRAINTS ALL IMMEDIATE; to check for violations), then delete the offending rows from the table (using their rowids) before issuing a commit. I think you should be able to automate it fairly well.

I don't do this sort of thing often enough to answer the second part of your question well. I can say that some of your options include (a) using SQL*Loader to do fast direct-path loads, which has pretty good error handling, and (b) creating a remote database link between the MS SQL Server db and the Oracle db, and inserting the data directly, to avoid data formatting issues. Hopefully someone else can provide a better answer about good migration tooling.

How to continue while loop after exception raised in SQL

By playing around with my code I figured out what was wrong.

I needed to add in the following line into my exception block of code:

v_line := NULL; v_sr_no := v_sr_no + 1; 

Final code:

  DECLARE    
  v_blob_data       BLOB;    
  v_blob_len        NUMBER;    
  v_position        NUMBER;    
  v_raw_chunk       RAW(10000);    
  v_char      CHAR(1);    
  c_chunk_len   number       := 1;    
  v_line        VARCHAR2 (32767)        := NULL;    
  v_data_array      wwv_flow_global.vc_arr2;    
  v_rows number;    
  v_sr_no number := 1;  
  v_first_line_done boolean := false;  
  v_error_cd number :=0;  
  v_quote_pos1 NUMBER;  
  v_quote_pos2 NUMBER;  
  v_enclosed_str VARCHAR(200);
  v_errmsg VARCHAR2(4000);
BEGIN
  delete from TEMP_MM_UPDATE where username = :P1_USER_ID;

  -- Read data from wwv_flow_files</span>    
  select    
    blob_content    
    into v_blob_data    
    from wwv_flow_files    
    where name = :P2_FILE_UPLOAD; 

  v_blob_len := dbms_lob.getlength(v_blob_data);    
  v_position := 1; 

  -- Read and convert binary to char</span>  
  WHILE ( v_position <= v_blob_len )    
  LOOP 
    begin  
        v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);    
        v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk)));    
        v_line := v_line || v_char;    
        v_position := v_position + c_chunk_len;

      -- When a whole line is retrieved </span>   
      IF v_char = CHR(10) THEN
        LOOP  
          --Make sure there's something to replace  
          IF INSTR(v_line, '"', 1, 1) = 0 THEN  
            EXIT; -- If nothing to replace, exit loop and don't try  
          END IF;  
          --Find the position of the first and second quotes in the line of text  
          v_quote_pos1 := INSTR(v_line, '"', 1, 1);  
          v_quote_pos2 := INSTR(v_line, '"', 1, 2);  
          --Extract the inner string  
          v_enclosed_str := SUBSTR(v_line, v_quote_pos1 + 1, v_quote_pos2 - v_quote_pos1 - 1);  
          --perform the replacement  
          v_line := SUBSTR(v_line, 0, v_quote_pos1 - 1) || REPLACE(v_enclosed_str, ',', '<') || SUBSTR(v_line, v_quote_pos2 + 1);  
        END LOOP; 

        -- Convert comma to : to use wwv_flow_utilities </span>  
        v_line := REPLACE (v_line, ',', ':');  
        v_line := REPLACE (v_line, '<', ',');  
        v_line := REPLACE (trim(v_line), '-', NULL);  
        --v_line := REPLACE (trim(v_line), '"', NULL);  
        -- Convert each column separated by : into array of data </span>    
        v_data_array := wwv_flow_utilities.string_to_table (v_line);  
        --Check to see if the row of column headers has already been parsed through  
        IF(v_first_line_done != true)THEN   
          v_first_line_done := true;  
          --Check column order in spreadsheet  
          IF(v_data_array(1)    LIKE '%Username%' AND
              v_data_array(2)  LIKE '%NDN%' AND
              v_data_array(3)  LIKE '%PCFN%') THEN   
            v_error_cd := 0;  
            v_line := NULL;  
          ELSE  
            v_error_cd := 1;  
          END IF;  
        --If first line is done and the column order is correct then  
        ELSIF(v_first_line_done = true AND v_error_cd = 0) THEN   
          -- Insert data into target table </span>    
          EXECUTE IMMEDIATE 'insert into TEMP_MM_UPDATE   
          (USERNAME,
           RPT_FLAG,
           PCFN)
          values (:1,:2,:3)'   
           USING   
            v_data_array(1),   
            v_data_array(2),   
            v_data_array(3);    
           -- Clear out    
            v_line := NULL; v_sr_no := v_sr_no + 1; 
        END IF;  
      END IF;
    exception
      WHEN OTHERS then
        v_errmsg := SQLERRM;
        insert into temp_mm_update (username,error_desc)
        values (:P1_USER_ID, v_errmsg);
v_line := NULL; v_sr_no := v_sr_no + 1; 
  END;
  END LOOP;

  DELETE FROM WWV_FLOW_FILES where name = :P2_FILE_UPLOAD;
  DELETE FROM TEMP_MM_UPDATE WHERE USERNAME IS NULL AND PCFN IS NULL;  
  IF(v_error_cd = 1) THEN  
    INSERT INTO temp_mm_update (USERNAME, ERROR_DESC)  
    VALUES (:P1_USER_ID, 'Error. Please check column order in spreadsheet.');  
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    insert into temp_mm_update (username,error_desc)
    values (:P1_USER_ID, 'No Data Found.');
  WHEN OTHERS then
    v_errmsg := SQLERRM;
    insert into temp_mm_update (username,error_desc)
    values (:P1_USER_ID, v_errmsg);
END;

PL/SQL inserting a new record exception handling

Your variable fname is empty. Fill it:

SELECT first_name
  INTO fname
  FROM temp_emp
 WHERE employee_id = mngrid;

Use for the insert the same mngrid. Why is 103?

What is

LENGTH(EMPLOYEE_ID)+1

First: this will convert employee_id in string, get the length from the string and add 1. Do you really want this?. And second: you cannot use column name in values(). Create sequence (change 1 with your value to start):

CREATE SEQUENCE TEMP_EMP_SEQ START WITH 1;

and than use it in your procedure

temp_emp_seq.nextval

.

CREATE OR REPLACE PROCEDURE insert_emp (mngrid IN temp_emp.manager_id%TYPE) IS
   ex_hugemp   EXCEPTION;
   emp_counter NUMBER;
   fname       temp_emp.first_name%TYPE;
BEGIN
   SELECT first_name
     INTO fname
     FROM temp_emp
    WHERE employee_id = mngrid;

   SELECT COUNT (*)
     INTO emp_counter
     FROM temp_emp
    WHERE temp_emp.manager_id = mngrid;

   IF emp_counter > 7 THEN
      RAISE ex_hugemp;
   ELSE
      INSERT INTO temp_emp (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
         VALUES( temp_emp_seq.nextval,'KAY','HORSTMAN',NULL,NULL,TO_DATE('28-05-2013','dd-mm-yyyy'),'IT_PROG',24000,NULL,mngrid,10);

      DBMS_OUTPUT.put_line ('It was inserted a new employee for the manager   ' || fname);
   END IF;
EXCEPTION
   WHEN ex_hugemp THEN
      DBMS_OUTPUT.put_line ('Manager ' || fname || '  has maximium number of employees    working under him.');
END;
/

Continue the iteration when DUP_VAL_ON_INDEX

The problem is that you are trying to insert all rows in each iteration. In your case you want to insert one row only inside the loop (row-by-row processing), so that if it fails only one row fails and the next one can be inserted.

CREATE OR REPLACE PROCEDURE procedure1 AS
   CURSOR TESTTABLE124_CUR IS
      SELECT * FROM TESTTABLE123;
BEGIN
   FOR i IN TESTTABLE124_CUR LOOP
      BEGIN
         INSERT INTO TESTTABLE124 VALUES (i.col1, i.col2...i.coln);      
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('duplicate value');         
      END;
   END LOOP;
   -- COMMIT; don't commit inside procedures
END procedure1;

Alternatively, you could only insert the rows that won't raise the exception (that would be more efficient):

CREATE OR REPLACE PROCEDURE procedure1 AS
BEGIN
   INSERT INTO TESTTABLE124
      (SELECT * 
         FROM TESTTABLE123 t_old
        WHERE t_old.pk NOT IN (SELECT t_new.pk FROM TESTTABLE124 t_new));
   -- COMMIT; don't commit inside procedures
END procedure1;

Replace pk with the appropriate unique column names.

Exception dose not working in execute immediate insert in oracle

That's because you are not running a select command, it is an insert command (insert select) which means that if the select won't return rows it just doesn't insert anything and no error is thrown for that. You should check whether the insert command has affected any rows. The way you do that in Oracle is checking the SQL%ROWCOUNT immediate after the execution, if it turns to be 0 then you do your job raising an exception. It would be something like:

DECLARE
   customException EXCEPTION;
   PRAGMA EXCEPTION_INIT( customException, -20001 );

....
BEGIN
     EXECUTE IMMEDIATE 'INSERT INTO emp_tb (empno,ename,job,mgr,hiredate,deptno,dname,loc) 
                       ('||v_query|| ')';
    IF (SQL%ROWCOUNT) = 0 then
       raise_application_error( -20001, 'This is a custom error' );
    end if;
EXCEPTION
   WHEN customException THEN
      dbms_output.put_line('No data Found...');  
END;

It's a long time without programming in Oracle PLSql So somethings there on the provided code may not compile but it is all there look into it in the internet and you will be good.

---

Related Topics