1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

i have got a warning : procedure created with compilation errors .

Discussion in 'SQL PL/SQL' started by sushmitha, Jul 20, 2014.

  1. sushmitha

    sushmitha Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    create or replace procedure items (orders varchar) is
    qtyhand number;
    releve1 number;
    max1eve1 number;
    begin
    select qty_lev, re_lev, max_lev into qtyhand, relevel,
    maxlevel from itemfile where itemcode = orders;
    if qtyhand < relevel then
    update itemfile set qty_lev = relevel + qtyhand where
    itemcode = orders;
    else
    dbms_output.put_line (‘itemlevel ok’);
    End if;
    end;

    may i knw wt is wrng wit the abv procedure ??
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Sushmita,

    First of all, please post the table creation scripts together with the code for us to be able to check/reproduce and eventually provide a solution as well as a brief on what you're trying to do with the procedure.

    Anyway, had a look at your code and initial findings are as follows:

    - There are some basic typing errors (I suppose) in your procedure: you've mixed "1" (one) with "l" :p
    For ex.: "releve1" => "relevel", "max1eve1" => "maxlevel"

    - For enclosing text for dbms_output.put_line use the ' character and not `
    For ex.: (‘itemlevel ok’); => ('itemlevel ok');

    Here's the corrected code:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE items(
        orders VARCHAR)
    IS
      qtyhand  NUMBER;
      relevel  NUMBER;
      maxlevel NUMBER;
    BEGIN
      SELECT qty_lev,
        re_lev,
        max_lev
      INTO qtyhand,
        relevel,
        maxlevel
      FROM itemfile
      WHERE itemcode = orders;
      IF qtyhand     < relevel THEN
        UPDATE itemfile SET qty_lev = relevel + qtyhand WHERE itemcode = orders;
      ELSE
        dbms_output.put_line ('itemlevel ok');
      END IF;
    END;
    /
     
    However, I wonder why you're using a procedure for this update (from what I gather in the code); you can simply use an SQL update:

    Code (SQL):
    UPDATE itemfile SET qty_lev = qty_lev + re_lev WHERE itemcode = orders
    AND qty_lev < re_lev;
     
  3. sushmitha

    sushmitha Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    thnx fr responding to my post .. :) i ws jst practising the concept of procedure and dis code s nt meant fr any meaningful purpose .. i jst wntd to make myself clear wit the concept ..
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    That's Ok Sushmita.
    We understand now :).
    It's always good to present the context and needs so that we can provide the members of the community the appropriate responses.

    Welcome to the world of Oracle !
     
  5. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Sushmita,

    While Creating Procedure/Function it is better to handle the exceptions by using Exception block at least with " WHEN others".
    Code (SQL):
    CREATE OR REPLACE PROCEDURE items(
        orders VARCHAR)
    IS
      qtyhand  NUMBER;
      relevel  NUMBER;
      maxlevel NUMBER;
    BEGIN
     
       BEGIN
          SELECT qty_lev,
                     re_lev,
                     max_lev
           INTO qtyhand,
                  relevel,
                  maxlevel
           FROM itemfile
          WHERE itemcode = orders;

       EXCEPTION
           WHEN others THEN
              qty_lev := 0;
               re_lev  :=0 ;
               max_lev := 0;
       END;

      IF qtyhand     < relevel THEN
        UPDATE itemfile SET qty_lev = relevel + qtyhand WHERE itemcode = orders;
      ELSE
        dbms_output.put_line ('itemlevel ok');
      END IF;

    EXCEPTION
       WHEN others THEN
          dbms_output.put_line ('Exception '||SQLERRM);
    END;
    /
    Regards
    Sambasiva Reddy.K