Exception Handling and Raising in While Loop

Ok I have a loop I am going through and if it hits certain conditions I am Raising an Exception(bypass_curr_rec) and I want to loop to the next record after I do a little logging of the record. SO if I check for a value and its not there or doesn't meet my criteria I want to raise an exception(bypass_curr_rec ), log the record and force the loop to go to the next record. Can I force this to happen with 'LOOP'

Heres an example: And FYI, if something doesn't make sense its because I took out a lot of things to just try and get my answer solved so if you see something that doesn't look right, and has nothing to do with my question, please just ignore......Bold equals my points of interest

--Declararation for Error Handler for BYPASSED Records
bypass_curr_rec EXCEPTION;

BEGIN

l_STARTTIME := SYSTIMESTAMP;
l_ENDORSE_CD_NBR := '290303';

EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_BASE_PCOV_TAB';

l_StartSelectionDate := ADD_MONTHS(Current_Date,-72);

--GET SOURCE OF DATA HERE
Select *
Bulk Collect Into l_temp_CR
from COV
WHERE COV_EXPDT > l_StartSelectionDate AND (STATUS != 'V')
ORDER BY CARRID ASC, COV_EFFDT ASC;

l_TOTAL_CR_FOUND_COUNT := l_temp_CR.count;

i := l_temp_CR.first;
WHILE (i IS NOT NULL) LOOP
   
    BEGIN
        polNbr :=  l_temp_CR(i).POLNBR || ' ' ;

        If RegExp_INSTR(TO_UPPER(PolNbr),'ASSIGN') Then
         --BYPASS THE REC
          Insert Into PCOV_TEST_MESSAGE_TAB
          RAISE bypass_curr_rec;

        else
          dateTest := l_temp_CR(i).COV_EXPDT;
          cvg_id :=  l_temp_CR(i).CVG_ID;
          covEffDate :=  l_temp_CR(i).COVEFFDT;
          pCARRID := l_temp_CR(i).CARRID;
          lcfInd  := l_temp_CR(i).LCF_IND;
        end if;
       
        l_InsuredsName := '' ;
        Select INSURED_ENTITY.INSURED_NAME into l_InsuredsName
        from INSURED_ENTITY
        where CVG_ID = cvg_id AND POLNBR = polNbr AND COV_EFFDT = covEffDate AND ROWNUM = 1;
       
        If TRIM(LEN(l_InsuredsName)) = 0 THEN
         --BYPASS THE RECORD
          Insert Into PCOV_TEST_MESSAGE_TAB
          RAISE bypass_curr_rec

        end if;
       
        --Check the Date
        If TO_CHAR(TO_DATE(dateTest,'MM/DD/YYYY'), 'MM/DD/YYYY') Then
            covExpDate := l_temp_CR(i).COV_EXPDT;
        Else
         --BYPASS THE RECORD
          Insert Into ERR_MESSAGE_TABLE
          RAISE bypass_curr_rec

        End If;
   
    END IF;

     EXCEPTION
      -- Does not handle raised exception
WHEN bypass_curr_rec THEN
               l_temp_CR(i).next; --Is this necessary???
               LOOP --WILL THIS GET ME TO GO TO THE NEXT RECORD?????

      WHEN OTHERS THEN
                 
      WHEN OTHERS THEN
            EXIT;        
   END;
END LOOP;
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Why declare the exception and raise it?

Why not just do the insert and let it continue?
sdstuberCommented:
just modify your exception handler for the bypass_curr_rec to do nothing,  i.e.  the NULL statement.  


EXCEPTION
            -- Does not handle raised exception
            WHEN bypass_curr_rec
            THEN
                NULL;
            WHEN OTHERS
            THEN
                EXIT;


based on your incomplete code,  the only place you increment your "i" index is in the exception.  if so, then put that back and it should be fine.
or,  increment somewhere else in your loop
jknj72Author Commented:
Because there is more logic that is going on that's not here. The first criteria is...

If RegExp_INSTR(TO_UPPER(PolNbr),'ASSIGN') Then

Why would I want to run the rest of the logic if that record is an Exception ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jknj72Author Commented:
Ok sdstuber....So just by incrementing I will go back to top of the loop to the next record because thats all Im trying to do...
slightwv (䄆 Netminder) Commented:
You might want to log all issues with the row?

To move to the next loop iteration, take a look at CONTINUE:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/continue_statement.htm#LNPLS01360
sdstuberCommented:
another option,  change your IF/ELSE hierarchy so you don't enter the rest of the code.

or -  use GOTO.

your current use of the exception handling is simply doing a GOTO without using that word.  Which is the same awkward coding practice, but possibly worse because it's disguising a GOTO as an error which makes the code misleading
jknj72Author Commented:
Slight - I am I just took out all the code to make it more readable....I will take a look at Continue and let you know. I just wanted to call like a Next or LOOP or something to process the next record but Ill take a look...
Thanks

sdstuber -That's exactly what I wanted to do something like a GOTO but I didn't know that was an option...But in reality isn't the Exception working as the GOTO? I just want to get to the next record?

Oh I just read the last part of your answer....Is there a better way to be doing this?...

Both of you - You guys are obviously Oracle gurus so im all ears...
I can give you guys the code that I have and if you could let me know what you would suggest. I just started a new job and if I can get this done efficiently it will make me look good ;)
slightwv (䄆 Netminder) Commented:
Here is a simplified example of what I think you are trying to do and how I would probably do it.

Your approach:
drop table tab1 purge;
create table tab1 (col1 char(1));

insert into tab1 values('a');
insert into tab1 values('b');
insert into tab1 values('c');
commit;


declare
	type mytab is table of char(1);
	l_temp_cr mytab;
	i number;
	bypass_curr_rec EXCEPTION;
begin
	select col1 bulk collect into l_temp_cr from tab1;

	i := l_temp_cr.first;
	while(i is not null) loop
		begin

			if l_temp_cr(i) = 'b' then
				raise bypass_curr_rec;
			else
				dbms_output.put_line('Past first check: ' || l_temp_cr(i));
			end if;

			if l_temp_cr(i) = 'a' then
				raise bypass_curr_rec;
			else
				dbms_output.put_line('Past second check: ' || l_temp_cr(i));
			end if;


			dbms_output.put_line('Passed all checks: ' || l_temp_cr(i));
			i := l_temp_cr.next(i);

		exception when bypass_curr_rec then
			i := l_temp_cr.next(i);
			continue;
		end;
	end loop;
end;
/

Open in new window


Here is how I would do it:
declare
	type mytab is table of char(1);
	l_temp_cr mytab;
begin
	select col1 bulk collect into l_temp_cr from tab1;

	for i in l_temp_cr.first .. l_temp_cr.last loop
			if l_temp_cr(i) = 'b' then
				continue;
			else
				dbms_output.put_line('Past first check: ' || l_temp_cr(i));
			end if;

			if l_temp_cr(i) = 'a' then
				continue;
			else
				dbms_output.put_line('Past second check: ' || l_temp_cr(i));
			end if;


			dbms_output.put_line('Passed all checks: ' || l_temp_cr(i));
	end loop;
end;
/

Open in new window

slightwv (䄆 Netminder) Commented:
Actually, I wouldn't use an in-memory table and bulk collect:
begin
	for i in (select col1 from tab1) loop

			if i.col1 = 'b' then
				continue;
			else
				dbms_output.put_line('Past first check: ' || i.col1);
			end if;

			if i.col1 = 'a' then
				continue;
			else
				dbms_output.put_line('Past second check: ' || i.col1);
			end if;


			dbms_output.put_line('Passed all checks: ' || i.col1);
	end loop;
end;
/

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
Basically, in PL\SQL loops to allow the loop to continue to the next record if an exception is encountered on a record, you need a nested block structure with a local exception handler something like this:

procedure ...
begin
   ...
  while [some condition is true] loop
    begin
       [your code that may cause an exception]
    exception
      when others then
        [what ever you want to do when the exception is encountered]
    end;
  end loop;
end procedure;
jknj72Author Commented:
Slight - Im using a lot of the values in the table but what your saying is I can do something like this instead?

BEGIN
--GET SOURCE DATA HERE
      for i in (Select * from COVERAGE WHERE COV_EXPDT > l_StartSelectionDate AND (STATUS != 'V')
                          ORDER BY CARRID ASC, COV_EFFDT ASC) loop

                l_TOTAL_CR_FOUND_COUNT := i.count;
                polNbr :=  i.POLNBR || ' ' ;

         If RegExp_INSTR(TO_UPPER(polNbr),'ASSIGN') Then
          --BYPASS THE REC
           Insert Into PCOV_TEST_MESSAGE_TAB
           Continue;
         else
           dateTest := i.COV_EXPDT;
           cvg_id :=  i.CVG_ID;
           covEffDate :=  i.COVEFFDT;
           pCARRID := i.CARRID;
           lcfInd  := i.LCF_IND;
         end if;

         -Get Insureds Name
         l_InsuredsName := '' ;
         Select INSURED_ENTITY.INSURED_NAME into l_InsuredsName
         from INSURED_ENTITY
         where CVG_ID = cvg_id AND POLNBR = polNbr AND COV_EFFDT = covEffDate AND ROWNUM = 1;
         
         If TRIM(LEN(l_InsuredsName)) = 0 THEN
          --BYPASS THE RECORD
           Insert Into PCOV_TEST_MESSAGE_TAB
           Continue;
         end if;
.....
johnsoneSenior Oracle DBACommented:
To me, it looks like your exception handler is at the end of the loop.  If the exception handle is at the end of the loop and there is no code in the loop after it, then there is no need for a CONTINUE or GOTO.  You are at the bottom of the loop and it will continue with the next iteration after exiting the exception block.
jknj72Author Commented:
Continue will bring me to the next record starting from the top and going through all the criteria but with the next record? I don't have to do an increment of any kind?
slightwv (䄆 Netminder) Commented:
>> but what your saying is I can do something like this instead?

Yes.  At least I think so.

You can reference every column value using the cursor loop that you can with the in-memory table but without the overhead.

You'll probably need to test both methods to see which one is actually faster but my money is on the cursor loop.
sdstuberCommented:
here's an example of what I meant by changing your IF/ELSE


BEGIN
    l_starttime := SYSTIMESTAMP;
    l_endorse_cd_nbr := '290303';

    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_BASE_PCOV_TAB';

    l_startselectiondate := ADD_MONTHS(CURRENT_DATE, -72);

      --GET SOURCE OF DATA HERE
      SELECT *
        BULK COLLECT INTO l_temp_cr
        FROM cov
       WHERE cov_expdt > l_startselectiondate AND (status != 'V')
    ORDER BY carrid ASC, cov_effdt ASC;

    l_total_cr_found_count := l_temp_cr.COUNT;

    i := l_temp_cr.FIRST;

    WHILE (i IS NOT NULL)
    LOOP
        BEGIN
            polnbr := l_temp_cr(i).polnbr || ' ';

            IF REGEXP_INSTR(UPPER(polnbr), 'ASSIGN')
            THEN
                --BYPASS THE REC
                INSERT INTO pcov_test_message_tab
                     VALUES (whatever);
            ELSE
                datetest := l_temp_cr(i).cov_expdt;
                cvg_id := l_temp_cr(i).cvg_id;
                coveffdate := l_temp_cr(i).coveffdt;
                pcarrid := l_temp_cr(i).carrid;
                lcfind := l_temp_cr(i).lcf_ind;

                l_insuredsname := '';

                SELECT insured_entity.insured_name
                  INTO l_insuredsname
                  FROM insured_entity
                 WHERE cvg_id = cvg_id
                   AND polnbr = polnbr
                   AND cov_effdt = coveffdate
                   AND ROWNUM = 1;

                IF TRIM(l_insuredsname) IS NULL
                THEN
                    --BYPASS THE RECORD
                    INSERT INTO pcov_test_message_tab
                         VALUES (whatever);
                ELSE
                    --Check the Date
                    IF TO_CHAR(TO_DATE(datetest, 'MM/DD/YYYY'), 'MM/DD/YYYY')
                    THEN
                        covexpdate := l_temp_cr(i).cov_expdt;
                    ELSE
                        --BYPASS THE RECORD
                        INSERT INTO err_message_table
                             VALUES (whatever);
                    END IF;
                END IF;
            END IF;

            i := l_temp_cr(i).NEXT;
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line('Record: ' || i || ' ' || SQLERRM);
                EXIT;
        END;
    END LOOP;
END;

Open in new window

johnsoneSenior Oracle DBACommented:
In the original sample code, I don't see where you are iterating to the next record.  Obviously you need to do it, but it depends on how the code is structured whether it needs to be done in the exception handler or not.  All I am trying to say is that there is no need for a command to jump back to the top of the loop if the exception handler is at the end.
sdstuberCommented:
>>>  I don't see where you are iterating to the next record.

it's in the exception handler, it's only pseudo-code, but the idea is there

WHEN bypass_curr_rec THEN
                l_temp_CR(i).next;
jknj72Author Commented:
Johnsone - Well I wasn't sure if I was doing it right but I used next thinking it would go to the next record???

EXCEPTION
       -- Does not handle raised exception
 WHEN bypass_curr_rec THEN
               l_temp_CR(i).next; --Is this necessary???
                LOOP --WILL THIS GET ME TO GO TO THE NEXT RECORD?????
       WHEN OTHERS THEN
                 
             EXIT;        
    END;
slightwv (䄆 Netminder) Commented:
>>Well I wasn't sure if I was doing it right but I used next thinking it would go to the next record???

Set up a simple test and try things like I posted above.

Comment out the .NEXT and see if it is necessary or not...  Note:  Be prepared to hit ctrl-C  ;)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
l_temp_CR(i).next;

by itself is insufficient.  look at line 65 in the code block I posted above.
you need something like this

  i :=   l_temp_CR(i).next;
jknj72Author Commented:
will check both thanks
johnsoneSenior Oracle DBACommented:
So, what you are saying is that every record will make it into the exception handler?  That seems like poorly designed code.
jknj72Author Commented:
Thanks guys!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.