When I compile the package-procedure in oracle, i get error message ' success with compilation error' How to fix the problem?

Question

I am using Apex Oracle online workspace for my final database assignment. I already created a table 'volunteer'. Now I need to insert data into the table using pl-sql procedure statements.

When I try to run this code -

CREATE OR REPLACE PACKAGE pkgVolunteer
AS PROCEDURE uspaddVolunteer(varvl_ID NUMBER, varvl_type NUMBER, 
    varName VARCHAR2, varAge NUMBER, varGenger VARCHAR2, 
    varBirthDateVARCHAR2, varPhone NUMBER, varEmail VARCHAR2,
    varFaculty VARCHAR2, varCourse VARCHAR2, varYear NUMBER,
    varcountry VARCHAR2, varCity VARCHAR2 varstreet VARCHAR2,
    varPostCode VARCHAR2);
END pkgVolunteer;
--
CREATE OR REPLACE PACKAGE BODY pkgVolunteer
AS PROCEDURE uspaddVolunteer(varvl_ID NUMBER, varvl_type NUMBER, varName 
    VARCHAR2, varAge NUMBER, varGenger VARCHAR2, varBirthDate VARCHAR2, 
    varPhone NUMBER, varEmail VARCHAR2, varFaculty VARCHAR2,
    varCourse VARCHAR2, varYear NUMBER, varcountry VARCHAR2,
    varCity VARCHAR2, varstreet VARCHAR2, varPostCode VARCHAR2)
IS 
BEGIN INSERT INTO  Volunteer(vl_id, vl_type, name, age, gender, birth_date, 
    phone, email, faculty, course, year,addr_country, addr_city, 
    addr_street, addr_postcode)
    VALUE(varvl_ID, varvl_type, varName, varAge, varGenger, vBirthDate, 
    varPhone, varEmail, varFaculty, varCourse, varYear, varcountry, 
    varCity, varstreet, varPostCode);
END uspaddVolunteer;
--
BEGIN pkgVolunteer.uspaddVolunteer('vl34322343', 1, 'Jack smith', 23 
    'Male','02/20/1994', 07400323321, '[email protected]', '',
    'Bsc IT', 2, 'United Kingdom', 'London', '42 Hill street', 'EC329RU');
END;

I get error 'ORA-24344: success with compilation error'.

Also here is the sql statement for creating the table -

CREATE TABLE volunteer
    (vl_id VARCHAR2(10) NOT NULL, vl_type NUMBER,
     name VARCHAR2(50), age NUMBER, gender VARCHAR2(6),
     birth_date VARCHAR2(10), phone NUMBER, email VARCHAR2(30),
     faculty VARCHAR2(50), course VARCHAR2(50), year NUMBER,
     addr_country VARCHAR2(30), addr_city VARCHAR2(50),
     addr_street VARCHAR2(50), addr_postcode VARCHAR2(7),

CONSTRAINT pk_volunteer PRIMARY KEY (vl_id));

I tried checking for syntax errors, but could not find any errors. Could someone help me fix the problem?

Answer 1

Try the below , check my comments :

CREATE OR REPLACE PACKAGE pkgVolunteer AS
  PROCEDURE uspaddVolunteer(varvl_ID NUMBER,
                            varvl_type NUMBER,
                            varName VARCHAR2,
                            varAge NUMBER,
                            varGenger VARCHAR2,
                            varBirthDate VARCHAR2,-- here needs space
                            varPhone NUMBER,
                            varEmail VARCHAR2,
                            varFaculty VARCHAR2,
                            varCourse VARCHAR2,
                            varYear NUMBER,
                            varcountry VARCHAR2,
                            varCity VARCHAR2 , --> needs comma
                                                        varstreet VARCHAR2,
                            varPostCode VARCHAR2);
END pkgVolunteer;
/
CREATE OR REPLACE PACKAGE BODY pkgVolunteer AS
PROCEDURE uspaddVolunteer(varvl_ID NUMBER, varvl_type NUMBER, varName VARCHAR2, varAge NUMBER, varGenger VARCHAR2, varBirthDate VARCHAR2, varPhone NUMBER, varEmail VARCHAR2, varFaculty VARCHAR2, varCourse VARCHAR2, varYear NUMBER, varcountry VARCHAR2, varCity VARCHAR2, varstreet VARCHAR2, varPostCode VARCHAR2) IS
BEGIN
INSERT INTO Volunteer
  (vl_id,
   vl_type,
   name,
   age,
   gender,
   birth_date,
   phone,
   email,
   faculty,
   course,
   year,
   addr_country,
   addr_city,
   addr_street,
   addr_postcode) VALUEs --> needs s 
  (varvl_ID,
   varvl_type,
   varName,
   varAge,
   varGenger,
   vBirthDate,
   varPhone,
   varEmail,
   varFaculty,
   varCourse,
   varYear,
   varcountry,
   varCity,
   varstreet,
   varPostCode);
END;
end; -- add another END;
/