CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
Mark
Mark

Reputation: 55

Compile Error for Oracle Procedure

I have the following PL/SQL code in a script and am trying to run it in SQL*Plus: 

create or replace procedure add_employee 
(fname IN varchar2(20), lname IN varchar2(25), email IN varchar2(25), job IN varchar2(25))
AS
eid INTEGER := 300;
BEGIN
    insert into Employees (employee_id, first_name, last_name, email, hire_date, job_id) 
    values (eid, fname, lname, email, job);
END add_employee;
/

I get the following error: 

Errors for PROCEDURE ADD_EMPLOYEE:

LINE/COL
---------------------------------------------------------------------------
ERROR
---------------------------------------------------------------------------

1/42
PLS-00103: Encountered the symbol "(" when expecting one of the following:

   := . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.

I don't understand what the issue is. I have written other procedures where the opening parenthesis follows the procedure name without any problem.

Upvotes: 0

Views: 83

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You can't specify the size of strings in the procedure declaration, so it should be:

create or replace procedure add_employee 
(fname IN varchar2, lname IN varchar2, email IN varchar2, job IN varchar2)

It's not a good idea to have the argument names match any table columns (email and job in this case); prefixing the parameters is common, e.g. using p_email and p_job; but should be done consistently. It often also makes it easier to follow the code even when the names don't clash - you know where the variable comes from and what its scope is.

Upvotes: 4

Related Questions