PL/SQL: SQL Statement Ignored [message #666602] |
Thu, 16 November 2017 04:13 |
|
malflowers
Messages: 5 Registered: November 2017
|
Junior Member |
|
|
I am trying to follow the oracle tutorial 2 days developers guide, 11g release 2 (11.2) E18147-13
everything was going fine until i entered the following procedure now i get Error(91:4): PL/SQL: SQL Statement ignored
on the line underlined, i have trawled all the faq's but cannot find a solution, i have reinput the code but it still
fails on the highlighted line - any assistance would be very gratefully received
PROCEDURE add_eval ( employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
, today IN DATE )
AS
job_id EMPLOYEES.JOB_ID%TYPE;
manager_id EMPLOYEES.MANAGER_ID%TYPE;
department_id EMPLOYEES.DEPARTMENT_ID%TYPE;
BEGIN
INSERT INTO EVALUATIONS ( ***** this is the line causing the error message
evaluation_id,
employee_id,
evaluation_date,
job_id,
manager_id,
department_id,
total_score
)
SELECT
evaluations_sequence.NEXTVAL, -- evaluation_id
add_eval.employee_id, -- employee_id
add_eval.today, -- evaluation_date
e.job_id, -- job_id
e.manager_id, -- manager_id
e.department_id, -- department_id
0 -- total_score
FROM employees e;
IF SQL%ROWCOUNT = 0 THEN
RAISE NO_DATA_FOUND;
END IF;
END add_eval;
|
|
|
Re: PL/SQL: SQL Statement Ignored [message #666603 is a reply to message #666602] |
Thu, 16 November 2017 04:22 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Generally PL/SQL: SQL Statement ignored comes with other errors explaining why it's ignored.
If you're not seeing them try creating the procedure in sqlplus.
Or query the user_errors view.
|
|
|
Re: PL/SQL: SQL Statement Ignored [message #666604 is a reply to message #666602] |
Thu, 16 November 2017 04:23 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
You need to provide more information, such as a copy/paste of what happens when you try to create the procedure. For example, I get this:orclz>
orclz> create or replace PROCEDURE add_eval ( employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE, today IN DATE )
2 AS
3 job_id EMPLOYEES.JOB_ID%TYPE;
4 manager_id EMPLOYEES.MANAGER_ID%TYPE;
5 department_id EMPLOYEES.DEPARTMENT_ID%TYPE;
6 BEGIN
7 INSERT INTO EVALUATIONS (
8 evaluation_id,
9 employee_id,
10 evaluation_date,
11 job_id,
12 manager_id,
13 department_id,
14 total_score
15 )
16 SELECT
17 evaluations_sequence.NEXTVAL, -- evaluation_id
18 add_eval.employee_id, -- employee_id
19 add_eval.today, -- evaluation_date
20 e.job_id, -- job_id
21 e.manager_id, -- manager_id
22 e.department_id, -- department_id
23 0 -- total_score
24 FROM employees e;
25 IF SQL%ROWCOUNT = 0 THEN
26 RAISE NO_DATA_FOUND;
27 END IF;
28 END add_eval;
29 /
Warning: Procedure created with compilation errors.
orclz> sho err
Errors for PROCEDURE ADD_EVAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1 PL/SQL: SQL Statement ignored
16/8 PL/SQL: ORA-02289: sequence does not exist
orclz>
because I haven't created EVALUATIONS_SEQUENCE.
|
|
|
|
|
Re: PL/SQL: SQL Statement Ignored [message #666609 is a reply to message #666607] |
Thu, 16 November 2017 05:45 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If you get the same error (which you have not bothered to show) then you have the same problem. You need to prove that the sequence does indeed exist. Then do a few simple tests: SELECTing from it would be a start. Then run the statement that SELECTs from EMPLOYEES. Then try the INSERT. You will never find the problem if you start with creating the procedure. Work up to that, step by step.
|
|
|
Re: PL/SQL: SQL Statement Ignored [message #666612 is a reply to message #666609] |
Thu, 16 November 2017 08:47 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You should NOT reference parameter values the way you are doing it. Also you have no need to define the job_id, manager_id, or Department_id in your declare section. I perfer to put a "p_" on my parameters so I can see them in my code. The correct code is
PROCEDURE Add_eval (P_employee_id IN Employees.Employee_id%TYPE,
P_today IN DATE)
AS
BEGIN
INSERT INTO Evaluations (Evaluation_id,
Employee_id,
Evaluation_date,
Job_id,
Manager_id,
Department_id,
Total_score)
SELECT Evaluations_sequence.NEXTVAL, -- evaluation_id
P_employee_id, -- employee_id from parameter
P_today, -- evaluation_date from parameter
E.Job_id, -- job_id
E.Manager_id, -- manager_id
E.Department_id, -- department_id
0 -- total_score
FROM Employees E;
IF SQL%ROWCOUNT = 0
THEN
RAISE NO_DATA_FOUND;
END IF;
END Add_eval;
[Updated on: Thu, 16 November 2017 08:49] Report message to a moderator
|
|
|
|
|
|