Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Red face Unanswered: Procedure Compliation Error

    Hi,
    Pretty new to Oracle9i. I had only 1 error when running this program, a Procedure Compliation Error. Can someone tell me where I went wrong.

    Thanks,
    terriep1

  2. #2
    Join Date
    Sep 2003
    Posts
    9

    code

    CREATE OR REPLACE PROCEDURE PETS_DAILY_FOOD
    (Food_Units IN number)

    IS

    CURSOR petfood_unit IS
    SELECT name, breed, dailyfoodunits, owner_id from PETS;

    c1_name varchar2(20);
    c1_breed varchar2(20);
    c1_dailyfoodunits number;
    c1_owner_id number;

    CURSOR p_owner IS
    SELECT fname, lname from PET_OWNER;

    c2_fname varchar2(20);
    c2_lname varchar2(20);
    c2_owner_id number;

    BEGIN

    OPEN petfood_unit;
    LOOP
    FETCH petfood_unit INTO c1_name, c1_breed, c1_dailyfoodunits;
    EXIT when petfood_unit%NOTFOUND;

    IF dailyfoodunits > food_units THEN

    OPEN p_owner;
    LOOP
    FETCH p_owner into c2_fname, c2lname;
    EXIT when p_owner%NOTFOUND;
    if c1_owner_id = c2_owner_id THEN
    DBMS_OUTPUT.PUTLINE (c1_name |' '| c1_breed |''| c1_dailyfoodunits |''|
    c2_fname |''| c2_lname);

    END_LOOP;
    CLOSE p_owner;
    CLOSE petfood_unit;

    End PETS_DAILY_FOOD;

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Procedure Compliation Error

    Originally posted by terriep1
    Hi,
    Pretty new to Oracle9i. I had only 1 error when running this program, a Procedure Compliation Error. Can someone tell me where I went wrong.

    Thanks,
    terriep1

    SQL> SHOW ERROR

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You've got two LOOPs, but only one END LOOP. Also I hope you don't mind some style suggestions....

    You loop through the pets table, regardless of how many rows you actually want. Add a where clause to the select based on food units. This way Oracle will only return the correct number of rows.

    Then for each pet returned, you loop through the entire owner table (every time) to get the owner details. That's not the way to do it, consider this....

    PHP Code:
    CREATE OR REPLACE PROCEDURE PETS_DAILY_FOOD(Food_Units IN numberIS
       CURSOR curPF
    bvUnits NUMBERIS
          SELECT p
    .name
                 
    p.breed
                 
    p.dailyfoodunits
                 
    o.fname,
                 
    o.lname 
          FROM   PETS p
    ,
                 
    OWNERS o
          WHERE  p
    .DailyFoodUnits bvUnits AND
                 
    o.owner_id       p.owner_id;
       
    rowPF curPF%ROWTYPE;
    BEGIN
       OPEN curPF
    Food_Units );
       
    LOOP
          FETCH curPF INTO rowPF
    ;
          EXIT 
    WHEN curPF%NOTFOUND;
          
    DBMS_OUTPUT.PUT_LINErowPF.Name||' '||rowPF.Breed||' '||rowPF.DailyFoodUnits||' '||rowPF.fname||' '||rowPF.lName );
       
    END LOOP;
       
    CLOSE curPF;
    EXCEPTION WHEN OTHERS THEN
       
    IF curPF%ISOPEN THEN
          CLOSE curPF
    ;
       
    END IF;
    END PETS_DAILY_FOOD
    Hth
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •