Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: SQL Statement Ignored (ORACLE XE sql developer version 17.3.0.271)
PL/SQL: SQL Statement Ignored [message #666602] Thu, 16 November 2017 04:13 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #666607 is a reply to message #666604] Thu, 16 November 2017 05:31 Go to previous messageGo to next message
malflowers
Messages: 5
Registered: November 2017
Junior Member
Thank you for the quick response, i get exactly the same error but i have created the sequence
it is almost as if it is falling over on the insert into evaluations and then the sequence is just tripped up by the
initial failure, is there any way to see why the insert into statement has been ignored

Regards

Mal
Re: PL/SQL: SQL Statement Ignored [message #666608 is a reply to message #666604] Thu, 16 November 2017 05:45 Go to previous messageGo to next message
malflowers
Messages: 5
Registered: November 2017
Junior Member
Just rechecked my code and you were spot on i had setup the sequence but as evaluation_seq
fixed my mistake and all good.

very impressed with the response from you guys, hopefully will not need to bother you again

Thank you
Re: PL/SQL: SQL Statement Ignored [message #666609 is a reply to message #666607] Thu, 16 November 2017 05:45 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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

Re: PL/SQL: SQL Statement Ignored [message #666618 is a reply to message #666612] Fri, 17 November 2017 05:04 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why is referencing parameter values that way a problem?
Re: PL/SQL: SQL Statement Ignored [message #666619 is a reply to message #666612] Fri, 17 November 2017 05:34 Go to previous messageGo to next message
malflowers
Messages: 5
Registered: November 2017
Junior Member
Thank you for your response

the problem was cause by incorrectly naming the sequence

all good now Smile
Re: PL/SQL: SQL Statement Ignored [message #666620 is a reply to message #666618] Fri, 17 November 2017 05:35 Go to previous message
malflowers
Messages: 5
Registered: November 2017
Junior Member
all sorted now, was referencing sequence with wrong name
Previous Topic: oracle external table issue
Next Topic: REGEXP_SUBSTR
Goto Forum:
  


Current Time: Fri Mar 29 02:58:25 CDT 2024