Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 7th, 2004, 02:37 PM
celluloid celluloid is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 1 celluloid User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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;
/

Reply With Quote
  #2  
Old October 8th, 2004, 10:42 AM
jim mcnamara jim mcnamara is offline
......@.........
Dev Shed Beginner (1000 - 1499 posts)
 
Join Date: Jun 2004
Posts: 1,308 jim mcnamara User rank is Sergeant Major (2000 - 5000 Reputation Level)jim mcnamara User rank is Sergeant Major (2000 - 5000 Reputation Level)jim mcnamara User rank is Sergeant Major (2000 - 5000 Reputation Level)jim mcnamara User rank is Sergeant Major (2000 - 5000 Reputation Level)jim mcnamara User rank is Sergeant Major (2000 - 5000 Reputation Level)jim mcnamara User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 3 Days 5 h 21 sec
Reputation Power: 48
Remove the semi-colon at the end of the line above line 43

Reply With Quote
  #3  
Old October 8th, 2004, 11:04 AM
utecistu utecistu is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Aug 2003
Posts: 16 utecistu User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
nasty semi-colon...

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > Errors When Creating Package Body


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway