CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
KPavezC
KPavezC

Reputation: 305

Errors creating Oracle package body

I have the following body from my package, used to do some CRUD operations in HR schema:

CREATE SEQUENCE emp_sequence;

CREATE OR REPLACE PACKAGE BODY employee_crud AS

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, p_first_name IN    employees.first_name%type, 
                             p_email IN employees.email%type, p_hire_date IN  employees.hire_date%type, 
                             p_job_id IN employees.job_id%type) AS


   BEGIN
     SELECT emp_sequence.NEXTVAL INTO id FROM dual;
     INSERT INTO employees(last_name, first_name, email, hire_date, job_id)
     VALUES (emp_seq.nextval, p_last_name, p_first_name, p_email, p_hire_date, p_job_id);
   EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
     dbms_output.put_line('error');
   END create_emp;


   PROCEDURE erase_emp(p_employee_id IN employees.employee_id%type) AS
   BEGIN
     DELETE FROM employees
     WHERE employee_id = p_employee_id;
   EXCEPTION
   WHEN  NO_DATA_FOUND THEN
     dbms_output.put_line('Error');
   END erase_emp;


   PROCEDURE upd_emp(p_employee_id IN employees.employee_id%type, p_salary IN  employees.salary%type, 
                                  p_email IN employees.email%type, p_department_id IN  employees.department_id%type) AS
   BEGIN
     UPDATE employees
     SET  employee_id = p_employee_id,
          salary = p_salary,
          email = p_email,
          department_id = p_department_id
     WHERE employee_id = p_employee_id;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('error');
   END upd_emp;


   PROCEDURE read_emp(p_employee_id IN employees.employee_id%type, p_last_name OUT  employees.last_name%type, 
                                 p_first_name OUT employees.first_name%type, p_email OUT  employees.email%type, 
                                 p_hire_date OUT  employees.hire_date%type, p_job_id OUT  employees.job_id%type, 
                                 p_salary OUT employees.salary%type) AS
   BEGIN
     SELECT employee_id, last_name, first_name, email, hire_date, job_id, salary
     INTO  p_last_name, p_first_name, p_email, p_hire_date, p_job_id, p_salary
     FROM EMPLOYEES
     WHERE employee_id = p_employee_id;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('Error');
   END read_emp;

END employee_crud;
/

After creating the specification (which is correct), I create a sequence to help adding employees. But compiling the body of the package, it shows the following errors:

LINE/COL ERROR
-------- ----------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
10/38    PLS-00201: 'ID' must be declared
10/41    PL/SQL: ORA-00904: : identifier is not valid
11/5     PL/SQL: SQL Statement ignored
11/17    PL/SQL: ORA-00913: too many values
49/5     PL/SQL: SQL Statement ignored
51/5     PL/SQL: ORA-00947: insufficient values

What should I do to correct these errors? I changed some names from my code because it's an assignment and I having troubles trying to do it without errors.

Upvotes: 0

Views: 927

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

10/38    PLS-00201: 'ID' must be declared

You're selecting data into a local variable ID that hasn't been declared. If you want to declare a local variable, you'd do so in the declaration section between the AS and the BEGIN

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, 
                        p_first_name IN    employees.first_name%type, 
                        p_email IN employees.email%type, 
                        p_hire_date IN  employees.hire_date%type, 
                        p_job_id IN employees.job_id%type) 
   AS
     id integer;
   BEGIN
     SELECT emp_sequence.NEXTVAL INTO id FROM dual;

If you're going to do that, you'd want to use the local variable id in your INSERT statement rather than calling emp_sequence.nextval directly. Personally, though, I'd get rid of the local variable, get rid of the initial SELECT, and just make the emp_sequence.nextval call in your INSERT statement. 

11/17    PL/SQL: ORA-00913: too many values

Regardless of how you do it, though, you'd need the number of columns in your INSERT to match the number of VALUES you specify.

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, 
                        p_first_name IN    employees.first_name%type, 
                        p_email IN employees.email%type, 
                        p_hire_date IN  employees.hire_date%type, 
                        p_job_id IN employees.job_id%type) 
   AS
     id integer;
   BEGIN
     SELECT emp_sequence.NEXTVAL INTO id FROM dual;
     INSERT INTO employees(employee_id, last_name, first_name, email, hire_date, job_id)
       VALUES (id, p_last_name, p_first_name, p_email, p_hire_date, p_job_id);
  END create_emp;

Or if you want to call the sequence directly

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, 
                        p_first_name IN    employees.first_name%type, 
                        p_email IN employees.email%type, 
                        p_hire_date IN  employees.hire_date%type, 
                        p_job_id IN employees.job_id%type) 
   AS
   BEGIN
     INSERT INTO employees(employee_id, last_name, first_name, email, hire_date, job_id)
       VALUES ( emp_sequence.NEXTVAL, p_last_name, p_first_name, p_email, p_hire_date, p_job_id);
  END create_emp;

Similarly, for your next error, the number of variables that you fetch into should match the number of columns that you're selecting

51/5     PL/SQL: ORA-00947: insufficient values

In your read_emp, you're selecting 7 things and trying to put them into 6 variables. Assuming that you don't want to return the employee_id, don't bother selecting it.

   PROCEDURE read_emp(p_employee_id IN employees.employee_id%type, 
                      p_last_name OUT  employees.last_name%type, 
                      p_first_name OUT employees.first_name%type, 
                      p_email OUT  employees.email%type, 
                      p_hire_date OUT  employees.hire_date%type, 
                      p_job_id OUT  employees.job_id%type, 
                      p_salary OUT employees.salary%type) 
   AS
   BEGIN
     SELECT last_name, first_name, email, hire_date, job_id, salary
     INTO  p_last_name, p_first_name, p_email, p_hire_date, p_job_id, p_salary
     FROM EMPLOYEES
     WHERE employee_id = p_employee_id;
   END read_emp;

While you can write a read_emp procedure like this, it would generally make more sense to create a function that returns an employees%rowtype record instead.

Your exception clauses should be removed. At best, they are discarding the error stack that would tell a person what failed and where. At worst, they're hiding the errors (you should never assume that anyone will ever see anything that you write to dbms_output) and causing the calling code to believe that some operation succeeded when it didn't.

Upvotes: 3

Related Questions