
October 7th, 2004, 02:37 PM
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 1
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Errors When Creating Package Body
Hey all Im quite new to PL/SQL programming and Im getting an error when trying to create a package body.
What I am trying to do is create an overloaded function that will return a string indicating the average cost of a given stock for a given portfolio. If no stock is found a message is returned stating that no stock for that portfolio could be found. If there was stock found then it will return a message stating the weighted average cost of the stock.
When I try to create the Package Body I get the following errors. I have added the line numbers so they can easily be found. Im not quite sure how to approach these errors. Any help you could give would be much appreciated.
LINE/COL ERROR
-------- -----------------------------------------------------------------
43/5 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
begin end function package pragma procedure form
80/1 PLS-00103: Encountered the symbol "END"
Code:
CREATE OR REPLACE PACKAGE BODY PKG_Q4
AS
FUNCTION FN_Q4 (p_Portfolio_Number IN Portfolios.Portfolio_Number%TYPE, p_Stock_Code IN Stocks.Stock_Code%TYPE)
RETURN v_Return_String
Is
v_WA_Return_value varchar2 (50);
v_F_Name varchar2 (100);
v_L_Name varchar2 (100);
v_All_Return_String varchar2 (200);
CURSOR Weighted_Avg_Cursor IS
Select ((SUM(PRICE_PER_SHARE * QUANTITY)) / (SUM(QUANTITY)))
FROM Transactions
Where Transactions.Portfolio_Number = p_Portfolio_Number AND
Transactions.Stock_Code = p_Stock_Code;
CURSOR Member_Name_Cursor IS
Select First_Name, Last_Name, Stock_Name, Portfolio_Descrition
FROM Members, Stocks, Portfolios
Where Portfolios.Portfolio_Number = p_Portfolio_Number AND
Portfolios.Member_Number = Members.Member_Number AND
Stocks.Stock_Code = p_Stock_Code;
Begin
Open Member_Name_Cursor;
Fetch Member_Name_Cursor Into v_F_Name, v_L_Name, v_Stock_Name, v_Portfolio_Description;
v_All_Return_String := v_F_Name || ' ' || v_L_Name || ' does not have any of the units of the requested stock in the requested portfolio.';
Open Weighted_Avg_Cursor;
Fetch Weighted_Avg_Cursor Into v_WA_Return_Value;
If Weighted_Avg_Cursor%FOUND
Then
v_All_Return_String := v_F_Name || ' ' || v_L_Name || 'paid, on average, ' || v_WA_Return_Value || ' for the stock ' || v_Stock_Name ||
' in the portfolio ' || v_Portfolio_Description;
End If;
Close Weighted_Avg_Cursor;
Close Member_Name_Cursor;
Return v_All_Return_String;
End FN_Q4;
FUNCTION FN_Q4 (p_Portfolio_Description IN Portfolios.Portfolio_Description%TYPE, p_Member_Number IN Portfolios.Member_Number%TYPE, p_Stock_Name IN Stocks.Stock_Name%TYPE)
RETURN v_Return_String;
(LINE 43) IS
v_WA_Return_value varchar2 (50);
v_F_Name varchar2 (100);
v_L_Name varchar2 (100);
v_All_Return_String varchar2 (200);
CURSOR Weighted_Avg_Cursor IS
Select ((SUM(PRICE_PER_SHARE * QUANTITY)) / (SUM(QUANTITY)))
FROM Transactions
Where Transactions.Portfolio_Description = p_Portfolio_Description AND
Portfolios.Member_Number = p_Member_Number AND
Stocks.Stock_Name = p_Stock_Name;
CURSOR Member_Name_Cursor IS
Select First_Name, Last_Name
FROM Members
Where Portfolios.Portfolio_Number = p_Portfolio_Number AND
Members.Member_Number = p_Member_Number;
Begin
Open Member_Name_Cursor;
Fetch Member_Name_Cursor Into v_F_Name, v_L_Name;
v_All_Return_String := v_F_Name || ' ' || v_L_Name || ' does not have any of the units of the requested stock in the requested portfolio.';
Open Weighted_Avg_Cursor;
Fetch Weighted_Avg_Cursor Into v_WA_Return_Value;
If Weighted_Avg_Cursor%FOUND
Then
v_All_Return_String := v_F_Name || ' ' || v_L_Name || 'paid, on average, ' || v_WA_Return_Value || ' for the stock ' || p_Stock_Name ||
' in the portfolio ' || Portfolios.Portfolio_Description;
End If;
Close Weighted_Avg_Cursor;
Close Member_Name_Cursor;
Return v_All_Return_String;
End FN_Q4;
(LINE 80) END PKG_Q4;
/
|