I\'m getting compilation errors on the following two SQL commands. What am I doi
ID: 3559130 • Letter: I
Question
I'm getting compilation errors on the following two SQL commands. What am I doing wrong? I have attached a screen shot of my sql below.
1. Create a procedure that accepts an employee number and a job. In the procedure, determine if the employee has the specified job or not. If the employee has the job, display the employee's ID number. If the employee does not have that job, display the employee's name and his or her actual job title.
2. Create a trigger to store a copy of any record deleted from the employee table into a table called Emp_temp. Assume that the Emp_temp table has the same structure as the employee table.
*This is one of my sql for #1
SQL>create or replace trigger emp_del
After delete on employees
For each row
Begin
INSERT into emp_temp
(employee_id,employee_name,job_id,job_description,hod,hiredate,salary,department_id)
Select
:OLD.employee_id,
:OLD.employee_name,
:OLD.job_id,
:OLD.job_description,
:OLD.hod,
:OLD.hiredate,
:OLD.salary;
:OLD.department_id
FROM dual;
End;
/
*This is my error: I get this same thing for both questions.
Warning: Trigger created with compilation errors
Explanation / Answer
CREATE OR REPLACE PROCEDURE check_job (in_emp_id IN NUMBER, in_emp_job VARCHAR2)
IS
v_cnt NUMBER;
v_id employees.employee_id%TYPE;
v_job employees.job_description%TYPE;
BEGIN
SELECT count(*) INTO v_cnt
FROM employees
WHERE employee_id = in_emp_id
AND job_description = in_emp_job;
IF v_cnt = 0 THEN
SELECT employee_id, job_description
INTO v_id, v_job
FROM employees
WHERE employee_id = in_emp_id;
dbms_output.put_line('Employee_id ' || v_id || ' job ' || v_job);
ELSE
SELECT employee_id
INTO v_id
FROM employees
WHERE employee_id = in_emp_id
AND job_description = in_emp_job;
dbms_output.put_line('Employee_id ' || v_id );
END IF;
END;
/
-- trigger
create or replace trigger emp_del
After delete on employees
For each row
Begin
INSERT into emp_temp
(employee_id,employee_name,job_id,job_description,hod,hiredate,salary,department_id)
VALUES (:OLD.employee_id, :OLD.employee_name, :OLD.job_id, :OLD.job_description, :OLD.hod, :OLD.hiredate, :OLD.salary, :OLD.department_id);
End;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.