REST enabling PL/SQL

ORDS has a feature named auto-plsql enablement. While this is the fastest way to access and leverage existing PLSQL logic via HTTP it is not ‘REST’. REST implies much more and there’s many answers and explaination out there such as Stack Overflow , REST API Tutorial, Wikipedia, and lots more.

ORDS API

The easiest way to enable a PLSQL object is to use our PLSQL API. Here’s an example of enabling a procedure that has 1 IN/OUT which is TIMESTAMP WITH LOCAL TIME ZONE.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'KLRICE',
                       p_object => 'TIMESTAMPLTZ_INOUT_PROC',
                       p_object_type => 'PROCEDURE',
                       p_object_alias => 'timestampltz_inout_proc',
                       p_auto_rest_auth => FALSE);

    commit;

END;

OpenAPI

A new feature in ORDS 17.3 is the generation of OpenAPI definitions. This is format from https://swagger.io/ for documentation of a REST API. There are many tools that support this standard and can simply import the definition and offer a way to test the endpoint automatically.

Open API for this procedure

{  
   "swagger":"2.0",
   "info":{  
      "title":"ORDS generated API for TIMESTAMPLTZ_INOUT_PROC",
      "version":"1.0.0"
   },
   "host":"localhost:9090",
   "basePath":"/ords/klrice/timestampltz_inout_proc",
   "schemes":[  
      "http"
   ],
   "produces":[  
      "application/json"
   ],
   "paths":{  
      "/":{  
         "post":{  
            "produces":[  
               "application/json"
            ],
            "responses":{  
               "200":{  
                  "description":"output of the endpoint",
                  "schema":{  
                     "type":"object",
                     "properties":{  

                     }
                  }
               }
            }
         }
      }
   }
}

Postman Import

Postman has an import feature which takes in the link for the swagger json above. Once it’s imported the tool makes a Collection in the sidebar for everything imported.

Getting the JQuery

Getting the jquery code is just 2 more clicks away. Click the Code in the right hand side, choose the language which included much more than just JavaScript->JQuery and there’s a sample generated for inclusion in your code.

Summary

• 1 PLSQL API call to enable the procedure
• Get the URL for the automatically generated Swagger JSON
• Import into Postman
• Get working code.
• Now leveraging existing pl/sql logic from anywhere.