ORDS PL/SQL AutoREST - incomplete OpenAPI def

kdario

Hi,

It seems that openapi definition for autorest plsql objects (tried both standalone proc and plsql package) doesn't contain definition for input parameters.

Tested on ORDS 22.1.0.105.1723

For example:

create or replace PROCEDURE TEST_PROC (P1 IN VARCHAR2, P2 IN NUMBER)

ORDS OpenAPI definition:

{
   "swagger": "2.0",
   "info": {
       "title": "ORDS generated API for TEST_PROC",
       "version": "1.0.0"
   },
   "host": "192.168.213.237:35000",
   "basePath": "/ords/kdario/test_proc",
   "schemes": [
       "http"
   ],
   "produces": [
       "application/json"
   ],
   "paths": {
       "/": {
           "post": {
               "description": "Create a new record on TEST_PROC",
               "produces": [
                   "application/json"
               ],
               "responses": {
                   "201": {
                       "description": "The successfully created record.",
                       "schema": {
                           "type": "object",
                           "properties": {}
                       }
                   }
               }
           }
       }
   }
}

Also, generated api description is misleading: "Create a new record on TEST_PROC".

Is this some bug/regression or OpenAPI is not fully supported for autorest plsql api?

Regards,

Dario

thatJeffSmith-Oracle

Looks like you've got two bugs there. Let me confirm and file those for you.

kdario

Note that Open API definition for manually created ORDS module also has some issues.

For example:

CREATE OR REPLACE PROCEDURE TEST_PROC2 (P1 IN VARCHAR2, P2 IN INTEGER, P3 IN NUMBER, P4 IN DATE, P5 IN CLOB)

POST handler:

begin
 test_proc2(:p_varchar, :p_int, :p_number, :p_date, :p_clob);
end;

ORDS Open API definition:

{
   "swagger": "2.0",
   "info": {
       "title": "ORDS generated API for testmodule",
       "description": "My first module",
       "version": "1.0.0"
   },
   "host": "192.168.213.237:35000",
   "basePath": "/ords/kdario/testmodule",
   "schemes": [
       "http"
   ],
   "produces": [
       "application/json"
   ],
   "paths": {
       "/testproc": {
           "post": {
               "description": "My first handler",
               "responses": {
                   "201": {
                       "description": "The successfully created record.",
                       "schema": {
                           "type": "object",
                           "properties": {}
                       }
                   }
               },
               "parameters": [
                   {
                       "name": "payload",
                       "in": "body",
                       "required": true,
                       "schema": {
                           "$ref": "#/definitions/testmodule_ITEM"
                       }
                   }
               ]
           }
       }
   },
   "definitions": {
       "STRING": {
           "type": "string"
       },
       "testmodule_ITEM": {
           "properties": {
               "p_clob": {
                   "type": "string"
               },
               "p_date": {
                   "type": "string"
               },
               "p_int": {
                   "type": "string"
               },
               "p_number": {
                   "type": "string"
               },
               "p_varchar": {
                   "type": "string"
               }
           }
       }
   }
}

• definition for p_int and p_number has incorrect type
• open api parameter order doesn't follow parameter order in stored procedure
• description says "The successfully created record."

Also tried to test stored procedure with boolean and blob input parameters, but could not make it work, the error is: PLS-00306: wrong number or types of arguments in call to 'TEST_PROC2'.

I'm not sure if this is another bug or section about type mappings is not up to date: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.1/orddg/ords-database-type-mappings.html#GUID-4F7FA58A-1C29-4B7E-819F-21DB4B68FFE1

Thanks