CODEWITHSUNDEEP
sqloracle-databasestored-procedures
adohertyd
adohertyd

Reputation: 2689

Procedure to insert employees: PL/SQL statement ignored

I am trying to insert values into an Employees table using the following procedure. I'm getting errors. Can anyone please explain the problem to me? Error code: Line 29: SQL statement ignored.

CREATE OR REPLACE PROCEDURE insert_employee(
    p_emp_id IN NUMBER, 
    p_emp_fname IN VARCHAR2,
    p_emp_sname IN VARCHAR2,
    p_emp_add1 IN VARCHAR2, 
    p_emp_add2 IN VARCHAR2,
    p_emp_town IN VARCHAR2, 
    p_emp_county IN VARCHAR2,
    p_emp_telno IN NUMBER,
    p_emp_position IN VARCHAR2, 
    p_emp_manager IN VARCHAR2

) IS 
    v_county_id County.county_id%type;
    v_position_id Positions.position_id%type;
    v_manager_id Facilities.facilty_id%type;
BEGIN    

    SELECT county_id
    INTO v_county_id
    FROM County WHERE county_name = p_emp_county;

    SELECT position_id
    INTO v_position_id
    FROM Positions WHERE position_name = p_emp_position;

    SELECT facility_manager
    INTO v_manager_id
    FROM Facilities WHERE facility_name = p_emp_manager; 

    INSERT INTO Employees (emp_id, emp_fname, emp_sname, emp_add1, emp_add2, emp_town, emp_county, emp_telno, emp_position, emp_salary, emp_manager) 
    VALUES(p_emp_id, p_emp_fname, p_emp_sname, p_emp_add1, p_emp_add2, p_emp_town, v_county_id, p_emp_telno, v_position_id, p_emp_salary, v_manager_id);
END;
/

Upvotes: 0

Views: 33391

Answers (6)

anonamus
anonamus

Reputation: 1

create or replace procedure procOne(sid  OUT  number,sname OUT varchar,fee OUT number)
as
begin
insert into student values(sid,sname,fee);
end;

Upvotes: 0

S M Sohail Kazmi
S M Sohail Kazmi

Reputation: 1

check column name

v_manager_id Facilities.facilty_id%type;

FROM Facilities WHERE facility_name = p_emp_manager;

Upvotes: 0

usergray
usergray

Reputation: 11

you should type ; after variable definition instead comma.

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48121

Your local variable declarations should have semicolons at the end of each line:

) IS 
    v_county_id County.county_id%type;
    v_position_id Positions.position_id%type;
    v_manager_id Facilities.facilty_id%type;

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132590

Remove the SELECTs - they are in invalid syntax, and don't do anything necessary here anyway. ALso remove the pointless error "handler" which does nothing more than hide the error:

CREATE OR REPLACE PROCEDURE insert_employee(
    p_emp_id IN NUMBER, 
    p_emp_fname IN VARCHAR2,
    p_emp_sname IN VARCHAR2,
    p_emp_add1 IN VARCHAR2, 
    p_emp_add2 IN VARCHAR2,
    p_emp_town IN VARCHAR2, 
    p_emp_county IN VARCHAR2,
    p_emp_telno IN NUMBER,
    p_emp_position IN VARCHAR2, 
    p_emp_manager IN VARCHAR2

) IS
BEGIN    

    INSERT INTO Employees (emp_id, emp_fname, emp_sname, emp_add1, emp_add2, emp_town, emp_county, emp_telno, emp_salary, emp_position, emp_manager) 
    VALUES(p_emp_id, p_emp_fname, p_emp_sname, p_emp_add1, p_emp_add2, p_emp_town, p_emp_county, p_emp_telno, p_emp_salary, p_emp_position, p_emp_manager);

 END;

If your example has been oversimplified and you actually need the values you were selecting before (e.g. you insert those into Employees also) then you need to use "select into" syntax e.g. 

...
) IS
    v_county_id county.county_id%type;
    v_xxx county.xxx%type;
BEGIN    

    SELECT county_id
    INTO   v_county_id
    FROM County WHERE county_name = p_emp_county;
    ...

Upvotes: 2

HJW
HJW

Reputation: 23443

Do you need to select into a cursor or a variable?

E.g.

SELECT county_id into v_country
    FROM County WHERE county_name = p_emp_county;

Upvotes: 0

Related Questions