oracle-databaseoracle-apexoracle-ords

Oracle Database REST APIs (ORDS) - url endpoints verbs does not work

I'm using Oracle APEX Release 24.2.0 and Oracle ORDS Release 25.1 on Oracle Database Express Edition 21.1.

I tried to generate a RESTFul API for a couple of tables : tcustomer, tcity and tsalesperson, the code was generated but something is strange with endpoints code.

I followed the below steps 1o) Open SQL Workshopand acess Utilities ==> Method on tables 2o) Put package name : PKG_Customer 3o) Select the three tables to crete the package : customer, city, salesperson enter image description here

4o) Created the package PKG_Customer enter image description here

5o) Go to SQL Workshop ==> RESTFul Services == Create AutoREST objects enter image description here

It generates the endpoints on Full URL : http://localhost:8080/ords/wsgcash/RESTpkg_customer/

6o) Open SQL Developer Web to check the REST Objects ==> REST ==> AutoREST enter image description here

7o) Check the PKG_Customer API code PKG_Customer ==> View OpenAPI enter image description here

8o) I don't understand why it generates the urls /GET_TCITY/1 and /GET_TCITY/2 as POST method, which requires the body :

{
  "p_cit_id": 0
}

enter image description here

9o) When I run Try it button, passing an existing tcity_id value, I got the following error : enter image description here

enter image description here

see the cURL code calling a POST ! Shouldn't it be a Get verb for a Get procedure? enter image description here

10o) I tired also call the URL in Postman as GET and as POST with the same parameters on Try it now, same error occured : 404 NOt Found enter image description here

enter image description here

Did I miss some step in generating this REST API based on a package or is there any additional configuration to be made on APEX in order to get the right urls for the endpoints and verbs?

Thanks for your support!

========= Regarding error 404 and overloaded issue ======= Infact whan I created the Package PKG_Customer in the steps 1o) to 3o) above of the SQL Workshop ==> Utilities ==> Method on Tables, it generated 2 GET_ procedures for each table, each with different parameters.
Take the table TCITY as an example, the below package Specicifation was created :

--------------------------------------------------------------
  -- get procedure for table "TCITY"
  --------------------------------------------------------------
       procedure "GET_TCITY" (
          "P_CIT_ID"   in number,
          "P_CIT_NAME" out varchar2
       );
  --------------------------------------------------------------
  -- get procedure for table "TCITY"
  --------------------------------------------------------------
       procedure "GET_TCITY" (
          "P_CIT_ID"   in number,
          "P_CIT_NAME" out varchar2,
          "P_MD5"      out varchar2
       );
  --------------------------------------------------------------

the same happened for table TCustomer and TSalesperson, there were two GET_tablename procedures for each. In the Package body the code are different.

Is that behaviour correct? What is it used for?

Solution

  • AutoPLSQL support in ORDS for procedures, functions, and packages are implemented as POST requests. We can't know what your PL/SQL program is doing, so we default to POST.

    If you know it only retrieves 1 or more records with no side effects in the database, you can write your own GET handler, to implement support for your package.procedure or package.function.

    The error you're seeing...

    It looks like your package has overloaded members, GET_TCITY and GET_TCUSTOMER.

    We don't currently support those.

    There's a bug in ORDS where we're listing those in the OpenAPI spec, when we aren't actually publishing APIs for them, hence the 404.