We help IT Professionals succeed at work.

Cursor functionality limited

graziazi
graziazi asked
on
Medium Priority
304 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I will be happy to take out the <70 clause and just keep in the >100 clause.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
No need to take it out.  Just don't raise an exception unless you 'handle' it.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.