Cursor functionality limited

Posted on 2011-04-26
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.......

med_too_much EXCEPTION;
      CURSOR medAmount IS
        SELECT * FROM Medication;
      v_med medAmount%ROWTYPE;
      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 ');


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


CLOSE medAmount;

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

CLOSE medAmount;


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
Question by:graziazi
    LVL 4

    Accepted Solution

    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 ?
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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
    	for i in 1..10 loop
    		    if i=5 then
    		   	end if;
    		  exception when others then dbms_output.put_line('Do something special here');
    	end loop;

    Open in new window


    Author Comment

    I will be happy to take out the <70 clause and just keep in the >100 clause.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    No need to take it out.  Just don't raise an exception unless you 'handle' it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now