-- update_emp.sql

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE update_emp (eid NUMBER, 
           column VARCHAR2, new_val VARCHAR2)
AS
   sqltext VARCHAR2(500);
   updated_val VARCHAR2(50);
BEGIN
   sqltext := 'UPDATE employee SET ' || column || ' = ''' || new_val ||
         ''' WHERE id = :id RETURNING ' || column || ' INTO :x';
   -- DBMS_OUTPUT.PUT_LINE(sqltext); --uncomment line to debug statement
   EXECUTE IMMEDIATE sqltext USING eid RETURNING INTO updated_val;
   -- DBMS_OUTPUT.PUT_LINE(updated_val); --uncomment to see the updated info
END;
/

-- test with a call
SELECT * FROM employee WHERE id = 7881;

BEGIN
  update_emp(7881, 'title', 'CEO and All Powerful One');
END;
/

SELECT * FROM employee WHERE id = 7881;
