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

Question

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"

Your error occurs when compiling the package, not while running it. — William Robertson, Jan 03 '22 at 09:46
Client tools typically need something to separate two PL/SQL blocks, such as a line containing a slash character before `CREATE OR REPLACE PACKAGE BODY`. — William Robertson, Jan 03 '22 at 09:50
In addition to your immediate problem, you have a rather serious and glaring design issue. Your EMPLOYEE table has a column EMPLOYEE_AGE. Now, think about that. People get older every day. Are you going to have a daily job to interrogate every employee's 'age', and update it when necessary? And by what criteria do you determine their new aged? No, instead of EMPLOYEE_AGE, you should simply have DATE_OF_BIRTH, as a DATE type. Then, when you need to know an employee's age, you simply subtract DATE_OF_BIRTH from SYSDATE. — EdStevens, Jan 03 '22 at 15:25

score 2 · Answer 1 · answered Jan 03 '22 at 08:15

2

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>

answered Jan 03 '22 at 08:15

Littlefoot

131,892
15
35
57

after removing semi-colon getting this error: Errors: PACKAGE BODY HR Line/Col: 2/3 PLS-00103: Encountered the symbol "2" when expecting one of the following: is as compress compiled wrapped – Ravindra Jan 03 '22 at 09:10
As I already demonstrated, there's no error **IF** you use it properly. I suggest you compare code you wrote/executed to mine. – Littlefoot Jan 03 '22 at 09:15
1

@Ravindra - did you copy and paste the code from the answer - *with* the SQL\*Plus line number prompts - rather than just removing the semicolon from your own code? – Alex Poole Jan 03 '22 at 09:40
i did that and i'm running my code in live oracle sql – Ravindra Jan 03 '22 at 09:45
@Ravindra - well, don't do that; the line numbers are shown by that client so they have to be removed if you're copying the answer. Or, more simply, edit your original code to match. And if this solves your problem, see [what to do when someone answers](https://stackoverflow.com/help/someone-answers) (and don't just dump your corrected code as a separate answer). – Alex Poole Jan 03 '22 at 10:31

William Robertson · Answer 2 · 2022-01-03T11:37:30.780

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.

score 0 · Answer 3 · edited Jan 03 '22 at 10:32

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;

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jan 03 '22 at 10:33

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

3 Answers3