9 Replies Latest reply on Aug 19, 2020 3:32 PM by jariola

    ORA-29259 when calling REST API from PL/SQL

    DDavid

      Hi all,

      I have created a simple REST API in ORDS, defining methods manually through APEX Interface.

      I tested the interface successfully from the Browser, Postman and also an APEX Simple Report.

       

      However, when trying to call the API from PL/SQL, I get an error "ORA-29259: end-of-input reached" when obtaining the response. The code is the following:

       

      declare

          req utl_http.req;

          res utl_http.resp;

          url varchar2(4000) := 'http://apex.oracle.com/pls/apex/dbento1/prueba/dept/';

          buffer varchar2(32000);

      begin

          req := utl_http.begin_request(url, 'GET');

          dbms_output.put_line('begin request success');

          res := utl_http.get_response(req);   -- at this point I get ORA-29259 end-of-input reached

          dbms_output.put_line('get response success');

          -- process the response from the HTTP call

          begin

              loop

                utl_http.read_line(res, buffer);

                dbms_output.put_line(buffer);

              end loop;

              utl_http.end_response(res);

          exception

              when utl_http.end_of_body then

                utl_http.end_response(res);

          end;

      end;

       

      Any help will be sincerely appreciated.

       

      Thanks in advance,
      David

        • 1. Re: ORA-29259 when calling REST API from PL/SQL
          EJ-Egyed

          You might be getting an error because you are attempting to use HTTP instead of HTTPS in your PL/SQL code.  The browser, Postman, and possibly even the APEX Simple Report are following the redirect from HTTP to HTTPS, but I do not believe PL/SQL will.  When I try to hit the HTTP URL you shared from PL/SQL, I receive a 403.  When I try the HTTPS version, I get a certificate validation error, but if you add the certificate to your DB's wallet, then the response should work properly.

           

          Also, since you do have APEX installed in the database, I would highly recommend using APEX's packages to interact with web services instead of UTL_HTTP as they greatly simplify the code necessary.  The code you shared can be greatly simplified like this:

           

          DECLARE
              l_response   CLOB;
          BEGIN
              l_response :=
                  apex_web_service.make_rest_request (
                      p_url           => 'http://apex.oracle.com/pls/apex/dbento1/prueba/dept/',
                      p_http_method   => 'GET');
          
              DBMS_OUTPUT.put_line ('Status Code: ' || apex_web_service.g_status_code);
              DBMS_OUTPUT.put_line ('Response: ' || DBMS_LOB.SUBSTR (l_response, 1, 32767));
          END;
          
          1 person found this helpful
          • 2. Re: ORA-29259 when calling REST API from PL/SQL
            DDavid

            Hi EJ-Egyed, thank you for your response.

            APEX's packages approach seems to be very helpful, but in this case APEX will not be available in my target environment, so this is not an option.

             

            However, I tested your suggestion of using https instead of http in the url, and I'm still getting the same error:

              ORA-29273: HTTP request failed 

              ORA-06512: at "SYS.UTL_HTTP", line 1130

              ORA-29259: end-of-input reached

              ORA-06512: at line 7

             

            By the way, this is the error I'm receiving for both http and https, not the 403 you mentioned.

            Any ideas?

             

            Thanks in advance,

            David

            • 3. Re: ORA-29259 when calling REST API from PL/SQL
              EJ-Egyed

              So I was playing around with it a bit more using the UTL_HTTP method, and found an oddity. I'm not sure if it was a caching issue in my database but it seems UTL_HTTP did not like having the / at the end of the URL for the request initially.  I had tested with the URL http://apex.oracle.com/pls/apex/dbento1/prueba/dept/10 and that had returned an HTML response indicating it was blocked by a firewall.  Then when I went back to http://apex.oracle.com/pls/apex/dbento1/prueba/dept/ I started receiving that same HTML response instead of the ORA-29259.

               

              So I would try possibly doing those same steps and see if that stops you from receiving the ORA-29259 and of course you will need to use HTTPS and add the certificate to your wallet to get a valid response.

              • 4. Re: ORA-29259 when calling REST API from PL/SQL
                DDavid

                Hi EJ-Egyed,

                I tried to reproduce the suggested steps, and I got the following results:

                 

                1) I still receive ORA-29259, no matter if I use http or https, or if the URL ends with "/" or not. The calling environment is an Oracle 11g Database

                 

                2) I tested the same scripts from the SQL Workshop in APEX environment (which connects to an Oracle 18c DB), and I always got a different message: ORA-24247: network access denied by access control list (ACL).

                 

                Due to such outcome, I think there might be some security configuration in the calling environment (which I don't administer). Is there a way I can figure it out?

                By the way, the REST service is defined as unsecured, so I don't understand why a Certificate should be provided.

                 

                Thank you very much for your help!


                David

                • 5. Re: ORA-29259 when calling REST API from PL/SQL
                  EJ-Egyed

                  The certificate from the website you are connecting to is needed anytime you are doing anything with HTTPS.  It is not used as authentication for the REST endpoint, it is used to validate the URL you are connecting to.

                  • 6. Re: ORA-29259 when calling REST API from PL/SQL
                    jariola

                    EJ-Egyed wrote:

                     

                    So I was playing around with it a bit more using the UTL_HTTP method, and found an oddity. I'm not sure if it was a caching issue in my database but it seems UTL_HTTP did not like having the / at the end of the URL for the request initially. I had tested with the URL http://apex.oracle.com/pls/apex/dbento1/prueba/dept/10 and that had returned an HTML response indicating it was blocked by a firewall. Then when I went back to http://apex.oracle.com/pls/apex/dbento1/prueba/dept/ I started receiving that same HTML response instead of the ORA-29259.

                     

                    So I would try possibly doing those same steps and see if that stops you from receiving the ORA-29259 and of course you will need to use HTTPS and add the certificate to your wallet to get a valid response.

                    I suspect you haven't created needed ACL. See 2303905.1

                    At least in other environment where you get ORA-24247: network access denied by access control list (ACL) from APEX SQL Workshop that is the case.

                    See Enabling Network Services in Oracle Database 11g or Later.

                     

                    But even you fix ACL, you need have configure correctly wallet for HTTPS.

                    1 person found this helpful
                    • 7. Re: ORA-29259 when calling REST API from PL/SQL
                      DDavid

                      Hi jariola

                      Yes, in 18c environment (APEX SQL Workshop) the message says clearly that an ACL needs to be configured. But I'm not able to configure an ACL since I' logged in to APEX as a workspace schema owner at apex.oracle.com, and it lacks privileges.

                       

                      But I still don't understand:

                       

                      1) Why I need a certificate when calling the REST service from PL/SQL if I was able to use the very same service from browser, Postman and APEX Simple Report without providing one (for both http and https calls).

                       

                      2) Is there a workaround to test a web service from APEX SQL Workshop if ACL cannot be configured?

                       

                      Thanks in advance,
                      David

                      • 8. Re: ORA-29259 when calling REST API from PL/SQL
                        jariola

                        DDavid wrote:

                         

                        Hi jariola

                        Yes, in 18c environment (APEX SQL Workshop) the message says clearly that an ACL needs to be configured. But I'm not able to configure an ACL since I' logged in to APEX as a workspace schema owner at apex.oracle.com, and it lacks privileges.

                         

                        But I still don't understand:

                         

                        1) Why I need a certificate when calling the REST service from PL/SQL if I was able to use the very same service from browser, Postman and APEX Simple Report without providing one (for both http and https calls).

                         

                        2) Is there a workaround to test a web service from APEX SQL Workshop if ACL cannot be configured?

                         

                        Thanks in advance,
                        David

                        1) You need wallet only for HTTPS calls. Browser and Postman have CA certificates in your PC. Database don't have those by default and don't use those from OS. You need install those to wallet for database.

                        2) I don't know any workaround, sorry.

                        1 person found this helpful
                        • 9. Re: ORA-29259 when calling REST API from PL/SQL
                          jariola

                          BTW, in apex.oracle.com there is wallet and ACL configured for APEX. That's why you can use in SQL Workshop  apex_web_service.make_rest_request. When you use that procedure/function privileges and wallet comes from APEX instance configuration.

                           

                          You can't use SYS.UTL_HTTP in apex.oracle.com, because ACL is not given to your schema and you don't know path to wallet if you like call HTTPS url.

                          1 person found this helpful