4 Replies Latest reply on Jan 16, 2018 5:03 AM by 3618765

    We can create rest API's and publish using ORDS. But, can we invoke/consume rest api's using ORDS?

    3618765

      Hi guys,

      We can create rest API's and publish using ORDS. But, can we invoke/consume rest api's using ORDS? If yes , what is the way.

      Thank you.

        • 1. Re: We can create rest API's and publish using ORDS. But, can we invoke/consume rest api's using ORDS?
          thatJeffSmith-Oracle

          No.

           

          But, if you give an example of what you're looking for, we can give a better answer.

          • 2. Re: We can create rest API's and publish using ORDS. But, can we invoke/consume rest api's using ORDS?
            3618765

            Hi Jeff,

             

            Thank you for the response.

             

            Here's our requirement:

             

             

            --> Our client is implementing third party customer payment portal system(Versa Pay) which enables his customers to login to the portal and pay his invoices.

             

             

            --> Our task is to import all the customers , invoice information from our existing system (Oracle EBS) to VersaPay  using rest API's provided by the VersaPay

                from staging tables in custom schema.

             

             

            --> All the customer and invoice information are copied from EBS base tables to staging tables in the custom schema

             

             

            We have two different servers (linux and solaris)

             

             

            Below are the components installed on each servers

             

                                                

             

             

             

            --> ORDS on linux server is mapped to the custom schema on Database in solaris server.i am able create get rest webservice on tables in our custom schema

                using plsql developer by connecting to the ords on linux server and fetch results at browser level in our local environment

             

             

            --> Our Database server (solaris) is restricted make any http or https call to outside network.Port is only open our internal Linux server.

             

             

            --> Since our database server doesn't allow to make Http or Https call directly from our database server i am unable to use utl_https package to invoke

                rest web services by running code in sql developer.

             

             

            Declare

              T_HTTP_REQ UTL_HTTP.REQ;

              T_HTTP_RESP UTL_HTTP.RESP;

              T_REQUEST_BODY VARCHAR2(30000);

              T_RESPOND      VARCHAR2(30000);

              T_START_POS    INTEGER := 1;

              T_OUTPUT       VARCHAR2(2000);

            BEGIN

             

              T_REQUEST_BODY := '{"identifier":"customer001","name": "My Sample Customers1119"}';

             

              utl_http.set_wallet('file:' || '<wallet path on slorais server>', '<Password>');

              T_HTTP_REQ:= UTL_HTTP.BEGIN_REQUEST( 'https://####.#####.com/api/imports/customer' , 'POST' , 'HTTP/1.1');

             

             

              UTL_HTTP.SET_AUTHENTICATION(T_HTTP_REQ,'<user name>','<password>');

             

             

              UTL_HTTP.SET_HEADER(T_HTTP_REQ, 'Content-Type', 'application/json');

             

              UTL_HTTP.SET_HEADER(T_HTTP_REQ, 'Content-Length', LENGTH(T_REQUEST_BODY));

             

              UTL_HTTP.WRITE_TEXT(T_HTTP_REQ, T_REQUEST_BODY);

             

             

              T_HTTP_RESP:= UTL_HTTP.GET_RESPONSE(T_HTTP_REQ);

             

             

              UTL_HTTP.READ_TEXT(T_HTTP_RESP, T_RESPOND);

              DBMS_OUTPUT.PUT_LINE(T_RESPOND);

             

              UTL_HTTP.END_RESPONSE(T_HTTP_RESP);

              return 'S';

            END;

             

             

            i am getting below error when i run this code on SQL Developer.

             

             

            ORA- 29273: HTTP REQUEST FAILED

            ORA-29259 END - OF INPUT REACHED

            ORA-06512 AT SYS.UTL_HTTP", LINE 268

            ORA-06512 AT SYS.UTL_HTTP", LINE 118

            292273.00000 - "HTTP REQUEST FAILED"

             

             

            --> my company want me to use ords to get / post data from Versapay.

             

             

            --> So, I am trying to create a get api from our end using ords and sql developer which internally calls versapay get / post api to get or post data.

             

             

            --> Eg: I am tried to create a get api with above code in "source query" section.but when i call our get webservice in browser, i am getting 404 not found error.

             

             

            --> Is this the right approach. What is the best practice to achieve this task?

            • 3. Re: We can create rest API's and publish using ORDS. But, can we invoke/consume rest api's using ORDS?
              thatJeffSmith-Oracle

              --> Since our database server doesn't allow to make Http or Https call directly from our database server i am unable to use utl_https package to invoke

                  rest web services by running code in sql developer.

              ORDS just runs in the db what you have coded for the handlers. You could have a POST handler run a PL/SQL block that runs an HTP call to do a GET or POST somewhere else...but you'd need to setup the DB to allow that.