If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Hello, Can anyone figure this out?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 12-01-11, 00:03
bolbi123456 bolbi123456 is offline
Registered User
 
Join Date: Dec 2011
Posts: 1
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
Reply With Quote
  #2 (permalink)  
Old 12-01-11, 00:14
anacedent anacedent is offline
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,415
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.
Reply With Quote
  #3 (permalink)  
Old 12-01-11, 01:51
Littlefoot Littlefoot is offline
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
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.
Reply With Quote
  #4 (permalink)  
Old 12-01-11, 09:56
beilstwh beilstwh is offline
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,222
ELSE IF must be ELSIF
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
  #5 (permalink)  
Old 12-01-11, 15:10
Littlefoot Littlefoot is offline
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
Not necessarily. ELSE IF is perfectly valid syntactically. (Logically? Ask the OP).
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On