• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Cursor functionality limited

Hi. I am trying to implement a cursor that accesses all Medications. If a the amount of a med is less than 70 raise an application error. If any med is greater than 100 output a message then re-raise the exception.

Here's my code.......

DECLARE
med_too_much EXCEPTION;
      CURSOR medAmount IS
        SELECT * FROM Medication;
          
      v_med medAmount%ROWTYPE;
    BEGIN
      OPEN medAmount;
 
     FETCH medAmount INTO v_med;
 
     WHILE medAmount%FOUND LOOP
       DBMS_OUTPUT.PUT_LINE(v_med.medication || '' || v_med.amount_in_mg || '' || v_med.prescription_id);
 FETCH medAmount INTO v_med;
 
 if v_med.amount_in_mg < 70 then
                   raise_application_error(-20001, v_med.medication || ' with ' || v_med.prescription_id || ' has a medication less than 70 ');

         else

if v_med.amount_in_mg > 100 then  
                 RAISE med_too_much;
         end if;
 end if;

END LOOP;

CLOSE medAmount;

EXCEPTION
WHEN med_too_much THEN
dbms_output.put_line('Is too much');


CLOSE medAmount;

END;
/

Open in new window



When I run my code however, only 1 row is returned 'saying xxxx has a medication less than 70.  even though there are other rows with med < 70 and rows > 100
0
graziazi
Asked:
graziazi
  • 2
2 Solutions
 
devindCommented:
When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application.

Above code will finish execution after first occurrence of v_med.amount_in_mg < 70

This is the reason you are only able to see 1 row.

What exactly you are trying to achieve ?
 
0
 
slightwv (䄆 Netminder) Commented:
raise_application_error stops the execution of the code.

You can use nested pl/sql blocks the handle the exception and continue but I don't think you can 'raise' an exception then continue.


For example
begin
	for i in 1..10 loop
		begin
		    if i=5 then
		    	raise_application_error(-20001,'Hello');
		   	end if;

		  exception when others then dbms_output.put_line('Do something special here');
		end;
		dbms_output.put_line(i);
	end loop;
end;
/

Open in new window

0
 
graziaziAuthor Commented:
I will be happy to take out the <70 clause and just keep in the >100 clause.
0
 
slightwv (䄆 Netminder) Commented:
No need to take it out.  Just don't raise an exception unless you 'handle' it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now