Andrew Andrew - 1 month ago 15
reST (reStructuredText) Question

Oracle ORDS 3.0.4 POST PL/SQL Procedure 400 Bad Request

Oracle 12c 12.1.0.2, SQL Developer 4.1.3.20

Following the online tutorials got ORDS installed (no APEX) and the Auto Enablement feature works fine including POST to the enabled (emp) table so the environment seems OK. But when I try to define a PL/SQL service in a POST I can't seem to get anywhere. Here's the service definition:

-- ORDS has been started in Standalone mode in SQL developer here

CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
INSERT INTO emp (empno, ename) VALUES (10, 'TEST');
END test_proc;
/

BEGIN
ORDS.DEFINE_SERVICE(
p_module_name => 'test' ,
p_base_path => 'test/',
p_pattern => 'simple_insert/',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source => 'BEGIN
hr.test_proc;
END;');
COMMIT;
END;
/

-- At this point the service is defined and running, testing with Postman yields "400 Bad Request"

GRANT EXECUTE ON hr.test_proc TO APEX_PUBLIC_USER; -- Despite being decoupled from APEX this username still remains in ORDS 3.0.4, appears in .\ords\conf\apex.xml

-- Same error as before, having the priv makes no difference


Error messages in postman:


400 Bad Request
mapped request using: /hr/* to: ORDS:apex_pu.HR
BadRequestException [statusCode=400, reasons=[Expected one of: <<{,[>> but got: <>]]


Answer

I'm no expert with this, but I just encountered this issue. The solution for me was to pass something in the request body (even though your routine is not using it).

Even this worked:

{}

If you tried using a GET method, you would not need to pass anything in, but the POST needs it. (and I assume you need to use POST - same as I do).

Good luck

Comments