PL/SQL Rest APIs - Best Practices

Resource Handler PL/SQL

• Create a simple handler with 2 parameters, :id and :value.
• Here is standard code with error handling that returns HTTP return codes to API caller.

DECLARE
    retval integer := 1;
BEGIN

    retval := UPDATEPROC(:id, :value);

    IF retval = 0 THEN
      -- No errors
      COMMIT;
      :status_code := 200;
    ELSE
      -- Error of some type that we detected in UPDATEPROC
      :string_out := 'An error occurred on the server side: ' || SQLERRM;
      :status_code := 500;
    END IF;


EXCEPTION
    WHEN OTHERS
    THEN
        -- Error detected via PL/SQL Exception
        :string_out := 'An error occurred during processing: ' || SQLERRM;
        :status_code := 500;
END;