PUT PL/SQL Example (Update)

Description : Basic PUT Update Example
Method : PUT
Source Type : PL/SQL
Returns : 200 for normal update, 400 for caught exceptions, ORDS generated messages if other error

-- PUT : Update Employee Data
DECLARE
  c sys_refcursor;
BEGIN

  IF (:EMPNO IS NULL) THEN
    :STATUS_CODE:=400;
    :ERRMSG := 'Required value is missing (EMPNO)';

  ELSE

--    UPDATE EMP SET ENAME = :ENAME WHERE EMPNO = :EMPNO;
    UPDATE EMP SET ENAME = :ENAME , MGR=:MGR, HIREDATE=:HIREDATE,SAL=:SAL,COMM=:COMM,DEPTNO=:DEPTNO
    WHERE EMPNO = :EMPNO;

    COMMIT;
    :STATUS_CODE:=200;
    -- Return Updated Values for Confirmation
    OPEN c FOR SELECT * 
                 FROM EMP
                WHERE EMPNO = :EMPNO;
    apex_json.write(c);
  END IF;
  EXCEPTION
    WHEN OTHERS THEN 
      :STATUS_CODE := 400;
      :ERRMSG := SQLERRM;
END;

Testing

PUT https://test.com/ords/apxdev/inventory/test/demo?ENAME=Jonx3&JOB=Test Job&MGR=7698&HIREDATE=01-JAN-2024&SAL=1000&COMM=100&DEPTNO=20&EMPNO=7840

[
    {
        "EMPNO": 7840,
        "ENAME": "Jonx3",
        "JOB": "Test Job",
        "MGR": 7698,
        "HIREDATE": "2024-01-01T00:00:00Z",
        "SAL": 1000,
        "COMM": 100,
        "DEPTNO": 20
    }
]