i'm getting error while running plsql package working till package creation-CodePudding

create table employee(
employee_id number(10) NOT NULL,
employee_name varchar2(50),
employee_sal number(10),
employee_role varchar2(50),
employee_age number(10),
CONSTRAINT employee_pk PRIMARY KEY(employee_id)
);

insert into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(12,'ravi',5676,'dse',23);
insert into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(13,'sai',85676,'tester',21);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(15,'chandu',4676,'developer',28);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(16,'raju',7676,'tech lead',22);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(17,'teja',9676,'manager',29);

create or replace package hr
as
    procedure hire(enum in number, ename in varchar2, sal in  number, erole in varchar2, age in number);
 
    procedure fire(enum in number); 

end;

CREATE OR REPLACE PACKAGE BODY hr
    AS
      PROCEDURE hire (enum   IN NUMBER,
                      ename  IN VARCHAR2,
                      sal    IN NUMBER,
                      erole  IN VARCHAR2,
                      age    IN NUMBER)
      IS
      BEGIN
         INSERT INTO employee (employee_id,
                              employee_name,
                              employee_sal,
                              employee_role,
                              employee_age)
              VALUES (enum,
                      ename,
                     sal,
                     erole,
                      age);
 
         COMMIT;
      END hire;
 
      PROCEDURE fire (enum IN NUMBER)
      IS
      BEGIN
        DELETE FROM employee
              WHERE employee_id = enum;
 
         COMMIT;
      END fire;
  END;

--getting this error

Errors: PACKAGE HR
Line/Col: 9/1 PLS-00103: Encountered the symbol "CREATE"

CodePudding user response：

Remove semi-colon at the end of the hire procedure in package body:

CREATE OR REPLACE PACKAGE BODY hr
AS
   PROCEDURE hire (enum   IN NUMBER,
                   ename  IN VARCHAR2,
                   sal    IN NUMBER,
                   erole  IN VARCHAR2,
                   age    IN NUMBER);            --> here
   IS
   ...

Once you do that, package and its body are successfully compiled:

SQL> CREATE OR REPLACE PACKAGE BODY hr
  2  AS
  3     PROCEDURE hire (enum   IN NUMBER,
  4                     ename  IN VARCHAR2,
  5                     sal    IN NUMBER,
  6                     erole  IN VARCHAR2,
  7                     age    IN NUMBER)
  8     IS
  9     BEGIN
 10        INSERT INTO employee (employee_id,
 11                              employee_name,
 12                              employee_sal,
 13                              employee_role,
 14                              employee_age)
 15             VALUES (enum,
 16                     ename,
 17                     sal,
 18                     erole,
 19                     age);
 20
 21        COMMIT;
 22     END hire;
 23
 24     PROCEDURE fire (enum IN NUMBER)
 25     IS
 26     BEGIN
 27        DELETE FROM employee
 28              WHERE employee_id = enum;
 29
 30        COMMIT;
 31     END fire;
 32  END;
 33  /

Package body created.

SQL>

CodePudding user response：

create table employee(
employee_id number(10) NOT NULL,
employee_name varchar2(50),
employee_sal number(10),
employee_role varchar2(50),
employee_age number(10),
CONSTRAINT employee_pk PRIMARY KEY(employee_id)
);

insert into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(12,'ravi',5676,'dse',23);
insert into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(13,'sai',85676,'tester',21);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(15,'chandu',4676,'developer',28);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(16,'raju',7676,'tech lead',22);
insert  into employee(employee_id, employee_name, employee_sal, employee_role, employee_age)values(17,'teja',9676,'manager',29);
   
  select * from employee;
  
  
  
create or replace package hr
  as
    procedure hire(enum in number, ename in varchar2, sal in  number, erole in varchar2, age in number);
 
    procedure fire(enum in number);
 
end;
/
CREATE OR REPLACE PACKAGE BODY hr
    AS
      PROCEDURE hire (enum   IN NUMBER,
                      ename  IN VARCHAR2,
                      sal    IN NUMBER,
                      erole  IN VARCHAR2,
                      age    IN NUMBER)
      IS
      BEGIN
         INSERT INTO employee (employee_id,
                              employee_name,
                              employee_sal,
                              employee_role,
                              employee_age)
              VALUES (enum,
                      ename,
                     sal,
                     erole,
                      age);
 
         COMMIT;
      END hire;
 
      PROCEDURE fire (enum IN NUMBER)
      IS
      BEGIN
        DELETE FROM employee
              WHERE employee_id = enum;
 
         COMMIT;
      END fire;
  END;

CodePudding user response：

Client tools typically need something to separate two PL/SQL blocks, such as a line containing a slash character between the first END; and CREATE OR REPLACE PACKAGE BODY, so this

end;

create or replace package body hr

needs to become

end;
/

create or replace package body hr

otherwise it all gets treated as a single block of code with CREATE at line 9, which then won't compile because for one thing PL/SQL has no CREATE keyword.

For production code, it is considered good practice to maintain the package header and body in two separate version-controlled source files.