CODEWITHSUNDEEP
sqlfunctionstored-proceduresplsqlpackage
Luke
Luke

Reputation: 31

PLSQL Creating a Package containing 2 functions and 1 procedure

I am new to creating packages. I have 2 functions and 1 procedure. They all work individually and compile with no errors. When I throw them together in a package I get the error, "Encountered the create symbol" referencing the create used for the package body. Can anybody explain to me what I am doing wrong?

CREATE OR REPLACE PACKAGE STUDENT_PKG IS 

  FUNCTION CALCULATE_GPA
    (STUDENT_ID1 IN NUMBER)
      RETURN NUMBER;

  FUNCTION CALCULATE_HOURS 
(STUDENT_ID1 IN NUMBER) 
  RETURN NUMBER;

  PROCEDURE UPDATE_STUDENTS;
END;  

CREATE OR REPLACE PACKAGE BODY STUDENT_PKG IS

FUNCTION CALCULATE_GPA
  (STUDENT_ID1 IN NUMBER)
  RETURN NUMBER
    IS 
GPA Number(3,2);
STUDENT_ID2 NUMBER(6);
BEGIN
Select To_Char(NVL(Sum(Points)/Sum(Credit_Hours),0),'0.99') GPA INTO GPA
  from
    (Select Enrollment.Student_ID, Enrollment.Grade, Grade_Value*Credit_Hours Points, Credit_hours, Enrollment.Course_Number
      from Enrollment 
        left join Grade on Grade.Grade=Enrollment.Grade
        left join Section on (Enrollment.Term_Code=Section.Term_Code and
                    Enrollment.Subject_Code=Section.Subject_code and
                    Enrollment.Course_Number=Section.Course_Number and
                    Enrollment.Section=Section.Section)
        left join Course on  (Section.Subject_Code=Course.Subject_Code and
                    Section.Course_Number=Course.Course_Number)) First
Where Student_Id=Student_ID1                   
    group by Student_ID;
RETURN GPA;
END CALCLUATE_GPA; 

FUNCTION CALCULATE_HOURS 
(STUDENT_ID1 IN NUMBER) 
  RETURN NUMBER AS
Credit_Hours1 Number(2);
BEGIN
  Select SUM(CASE WHEN GRADE IS NULL THEN 0 ELSE CREDIT_HOURS END) into Credit_Hours1
    from Course
      join Enrollment on course.course_number=Enrollment.course_number
        where Student_id=Student_Id1
          GROUP BY STUDENT_ID;
  RETURN Credit_Hours1;
END CALCULATE_HOURS;

PROCEDURE UPDATE_STUDENTS AS 
GPA1 NUMBER (3,2);
CREDIT_HOURS1 NUMBER(2);
STUDENT_ID1 NUMBER(6):=333333;
BEGIN
  GPA1:=CALCULATE_GPA(STUDENT_ID1);
  CREDIT_HOURS1:=CALCULATE_HOURS(STUDENT_ID1);
    UPDATE STUDENT
      SET GPA=GPA1,
      CREDIT_HOURS=CREDIT_HOURS1
      WHERE STUDENT_ID=STUDENT_ID1;
END UPDATE_STUDENTS;

END STUDENT_PKG;

Upvotes: 0

Views: 706

Answers (1)

Mick Mnemonic
Mick Mnemonic

Reputation: 7956

You need to terminate the PACKAGE and PACKAGE BODY blocks with a slash (/) like this:

CREATE OR REPLACE PACKAGE STUDENT_PKG IS
    /* Header declarations */
END;  
/
CREATE OR REPLACE PACKAGE BODY STUDENT_PKG IS
    /* PKG BODY here */
END STUDENT_PKG;
/

If you're running the code from SQLDeveloper, you should choose the "Run as script" option. This thread gives an in-depth explanation about when/why the slash is required.

Upvotes: 0

Related Questions