Home » SQL & PL/SQL » SQL & PL/SQL » Package Errors (Oracle)
Package Errors [message #281509] Sat, 17 November 2007 16:56 Go to next message
pbrown
Messages: 4
Registered: November 2007
Location: FL
Junior Member
I have a function and a procedure that I am trying to combine into a package. The function, procedure and package spec all compile. However, the package body is giving me the following errors and I can't figure out why since I am fairly new to this.

Any help would be greatly appreciated in understanding what I am doing incorrectly.

ERRORS:

Errors for PACKAGE BODY SALARY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/1 PLS-00103: Encountered the symbol "AS" when expecting one of the
following:
begin end function package pragma procedure form

27/1 PLS-00103: Encountered the symbol "END"


PACKAGE SPEC:
CREATE OR REPLACE PACKAGE salary
AS
 PROCEDURE new_emp (namein VARCHAR2, statein CHAR,
                    deptin NUMBER);
END salary;
/


PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY salary
AS
FUNCTION avg_emp1
RETURN NUMBER
AS
 avg_sal   NUMBER DEFAULT 0;
 min_sal   NUMBER DEFAULT 0;
 sum_sal   NUMBER DEFAULT 0;
 num_sal   NUMBER DEFAULT 0;
BEGIN
 SELECT SUM(e.salary) INTO sum_sal FROM Emp2 e;
 SELECT MIN(e.salary) INTO min_sal FROM Emp2 e;
 SELECT COUNT(e.salary) INTO num_sal FROM Emp2 e;
 avg_sal := (sum_sal - min_sal) / (num_sal - 1);
 RETURN (avg_sal);
END avg_emp1;

PROCEDURE new_emp
 (namein VARCHAR2, statein CHAR, deptin NUMBER);
AS
 new_salary   NUMBER DEFAULT 0;
BEGIN
 new_salary := avg_emp1 - (avg_emp1 * 0.15);
 INSERT INTO Emp2 VALUES(namein, statein, new_salary, deptin);
END new_emp;

END salary;
/

[Updated on: Sat, 17 November 2007 17:28]

Report message to a moderator

Re: Package Errors [message #281510 is a reply to message #281509] Sat, 17 November 2007 17:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above; such as using <code tags> along with line number from SQL*Plus.

Many fine coding examples can be found at http://asktom.oracle.com

If/when you get desperate enough to RTFM the PL/SQL Guide can be found at http://tahiti.oracle.com
Re: Package Errors [message #281512 is a reply to message #281510] Sat, 17 November 2007 17:26 Go to previous messageGo to next message
pbrown
Messages: 4
Registered: November 2007
Location: FL
Junior Member
Thanks for the help. I am new to the forum. I will make the changes immediately.
Re: Package Errors [message #281513 is a reply to message #281510] Sat, 17 November 2007 17:30 Go to previous messageGo to next message
pbrown
Messages: 4
Registered: November 2007
Location: FL
Junior Member
Are we suppose to add line numbers manually or something? I didn't find anything about the line numbers so far in the Posting Guidelines.
Re: Package Errors [message #281515 is a reply to message #281509] Sat, 17 November 2007 17:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The semicolon, ";" at the end of the line before the "as" should not be there.
Re: Package Errors [message #281516 is a reply to message #281509] Sat, 17 November 2007 18:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
 sqlplus

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 17 16:02:40 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter user-name: dbadmin
Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set term on echo on
SQL> CREATE OR REPLACE PACKAGE BODY salary
  2  AS
  3  FUNCTION avg_emp1
  4  RETURN NUMBER
  5  AS
  6   avg_sal   NUMBER DEFAULT 0;
  7   min_sal   NUMBER DEFAULT 0;
  8   sum_sal   NUMBER DEFAULT 0;
  9   num_sal   NUMBER DEFAULT 0;
 10  BEGIN
 11   SELECT SUM(e.salary) INTO sum_sal FROM Emp2 e;
 12   SELECT MIN(e.salary) INTO min_sal FROM Emp2 e;
 13   SELECT COUNT(e.salary) INTO num_sal FROM Emp2 e;
 14   avg_sal := (sum_sal - min_sal) / (num_sal - 1);
 15   RETURN (avg_sal);
 16  END avg_emp1;
 17  
 18  PROCEDURE new_emp
 19   (namein VARCHAR2, statein CHAR, deptin NUMBER);
 20  AS
 21   new_salary   NUMBER DEFAULT 0;
 22  BEGIN
 23   new_salary := avg_emp1 - (avg_emp1 * 0.15);
 24   INSERT INTO Emp2 VALUES(namein, statein, new_salary, deptin);
 25  END new_emp;
 26  
 27  END salary;
 28  /

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY SALARY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/1     PLS-00103: Encountered the symbol "AS" when expecting one of the
         following:
         begin end function package pragma procedure form

27/1     PLS-00103: Encountered the symbol "END"
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Re: Package Errors [message #281521 is a reply to message #281515] Sat, 17 November 2007 21:14 Go to previous messageGo to next message
pbrown
Messages: 4
Registered: November 2007
Location: FL
Junior Member
Thanks! That was it. Just goes to show it helps to have a fresh pair of eyes. I guess I added the semicolon when I transposed the procedure to the package. The procedure worked but not the package version. Guess I should have copied and pasted instead.

Thanks again I have struggled with that for 3 days. Would be nice if the error messages would tell you that a extra semicolon was added. Shocked
Re: Package Errors [message #281545 is a reply to message #281521] Sun, 18 November 2007 01:55 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Would be nice if the error messages would tell you that a extra semicolon was added

It can't as a semicolon at this place can be a valid syntax depending on other things.
So the first error for it is "AS" as what is before is correct.

Regards
Michel
Previous Topic: drop all tables
Next Topic: Query hanging
Goto Forum:
  


Current Time: Fri Dec 09 17:28:02 CST 2016

Total time taken to generate the page: 0.29198 seconds