CODEWITHSUNDEEP
oracle-databaseplsql
kiric8494
kiric8494

Reputation: 205

stored procedure compiled with errors

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;

Upvotes: 0

Views: 58

Answers (1)

MT0
MT0

Reputation: 168588

  • 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

Upvotes: 2

Related Questions