Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Unanswered: Hello, Can anyone figure this out?

    I am having a slight problem with an sql procedure im trying to create, keep getting an error saying "encountered ;, while expecting the following: if" could anyone help?

    CREATE OR REPLACE PROCEDURE CustOrders(
    iCust IN NUMBER
    )
    AS
    CurrentRow OrderCursor%ROWTYPE;
    oCompany VARCHAR2(15);

    CURSOR OrderCursor IS
    SELECT Description, Amount
    FROM Products, Orders
    WHERE iCust = Cust;
    BEGIN

    SELECT Company
    INTO oCompany
    FROM Customers
    WHERE iCust = CustNum;

    IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE ("Customer " || oCompany || " was not found.");

    ELSE IF SQL%FOUND THEN

    SELECT OrderNum
    FROM Orders
    WHERE CustNum = Cust;

    IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE(oCompany || " has no orders recorded.");
    ELSE IF SQL%FOUND THEN

    DBMS_OUTPUT.PUT_LINE("Orders For " || iCust || oCompany);
    DBMS_OUTPUT.PUT_LINE("Description Amount");
    FOR CurrentRow IN OrderCursor
    LOOP
    DBMS_OUTPUT.PUT_LINE (CurrentRow.Description || " " || CurrentRow.Amount);
    END LOOP;

    END IF;
    END IF;
    END;
    /
    SHOW ERRORS

    any help would be greatly appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please realize that we don't have your tables & we don't have your data.
    Therefore we can't run, test or improve your posted SQL.
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.

    Following is unsolicited advice/suggestion - DBMS_OUTPUT has no place in Production code.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There are two END IFs missing at the end of the script (before the closing END); if you had formatted the script, you'd see it much easier.

    Furthermore, you should have used single quotes in DBMS_OUTPUT, not double ones.

    Moreover, your script will fail when "SELECT Company INTO oCompany" returns nothing - it won't even get to "IF SQL%NOTFOUND" line but raise NO-DATA-FOUND exception - you should handle it.

    There might be some other errors, but these were the ones I saw looking at this code.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    ELSE IF must be ELSIF
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Not necessarily. ELSE IF is perfectly valid syntactically. (Logically? Ask the OP).

Posting Permissions

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