stored procedure compiled with errors

Question

I have a stored procedure where I am trying to take 2 input age and name That will update the record in employee table.

But when I am compiling getting - compiled with errors

Code :

CREATE OR REPLACE PROCEDURE update_emp_age
(
    v_age in number;
    v_ename in varchar2(255 char);
) AS
BEGIN
    UPDATE employee
    SET
        eage = v_age
    WHERE
        ename = v_ename;

    ROLLBACK;
END;

@a_horse_with_no_name : Got fix for it : need to make varchar2(255 char) to varchar2; — kiric8494
– kiric8494, Commented Jul 27, 2022 at 9:52
Are you sure you want to ROLLBACK? That procedure won't have any effect with it ... — Littlefoot
– Littlefoot, Commented Jul 27, 2022 at 9:57

MT0 · Accepted Answer · 2022-07-27 10:05:13Z

Remove the size from the data types in the signature.
Use commas and not semi-colons between arguments in the signature.
Don't ROLLBACK (else your query will perform the update and then immediately ROLLBACK the change doing lots of work for no effect and potentially rolling back prior chages).

Like this:

CREATE OR REPLACE PROCEDURE update_emp_age
(
    v_age   in NUMBER,
    v_ename in VARCHAR2
) AS
BEGIN
    UPDATE employee
    SET   eage = v_age
    WHERE ename = v_ename;
END;
/

You could also use %TYPE in the signature:

CREATE OR REPLACE PROCEDURE update_emp_age
(
    v_age   in EMPLOYEE.EAGE%TYPE,
    v_ename in EMPLOYEE.ENAME%TYPE
) AS
BEGIN
    UPDATE employee
    SET   eage = v_age
    WHERE ename = v_ename;
END;
/

db<>fiddle here

Collectives™ on Stack Overflow

stored procedure compiled with errors

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related