POST PL/SQL Example (Creation)

Description : Basic POST creation example
Method : POST
Source Type : PL/SQL
Returns : 201 for normal creation, 400 for caught exceptions, ORDS generated messages if other error

-- POST : Create New Emp
DECLARE
  MY_NEW_ID NUMBER;
  c sys_refcursor;
BEGIN

  SELECT MAX(EMPNO)+1 INTO MY_NEW_ID FROM EMP;

  INSERT INTO EMP  (
    EMPNO,
    ENAME,
    JOB,
    MGR,
    HIREDATE,
    SAL,
    COMM,
    DEPTNO
  ) VALUES (
    MY_NEW_ID, 
    :ENAME,
    :JOB,
    :MGR,
    :HIREDATE,
    :SAL,
    :COMM,
    :DEPTNO
  );

  COMMIT;
  :STATUS_CODE:=201;

  -- Return Matching ID
  OPEN c FOR SELECT * 
        FROM EMP 
       WHERE EMPNO = MY_NEW_ID;
    apex_json.write(c);
  EXCEPTION
    WHEN OTHERS THEN 
      :STATUS_CODE := 400;
      :ERRMSG := SQLERRM;
END;

Testing

POST https://test.com/ords/apxdev/inventory/test/demo?ENAME=David&JOB=Test Job&MGR=7698&HIREDATE=01-JAN-2024&SAL=1000&COMM=100&DEPTNO=10

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