Forum Stats

  • 3,815,377 Users
  • 2,259,010 Discussions
  • 7,893,063 Comments

Discussions

ORA-29259 when calling REST API from PL/SQL

DDavid
DDavid Member Posts: 13 Blue Ribbon

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

Answers

  • EJ-Egyed
    EJ-Egyed Member Posts: 125 Blue Ribbon
    edited Aug 17, 2020 11:17AM

    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;
    DDavid
  • DDavid
    DDavid Member Posts: 13 Blue Ribbon
    edited Aug 18, 2020 10:27AM

    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

  • EJ-Egyed
    EJ-Egyed Member Posts: 125 Blue Ribbon
    edited Aug 18, 2020 10:46AM

    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.

  • DDavid
    DDavid Member Posts: 13 Blue Ribbon
    edited Aug 18, 2020 5:58PM

    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

  • EJ-Egyed
    EJ-Egyed Member Posts: 125 Blue Ribbon
    edited Aug 18, 2020 7:44PM

    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.

  • jariola
    jariola Member Posts: 10,735 Silver Crown
    edited Aug 19, 2020 9:18AM
    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.

    DDavid
  • DDavid
    DDavid Member Posts: 13 Blue Ribbon
    edited Aug 19, 2020 11:04AM

    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

  • jariola
    jariola Member Posts: 10,735 Silver Crown
    edited Aug 19, 2020 11:10AM
    DDavid wrote:Hi jariolaYes, 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.

    DDavid
  • jariola
    jariola Member Posts: 10,735 Silver Crown
    edited Aug 19, 2020 11:32AM

    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.

    DDavid
  • Jose R Gonzalez
    Jose R Gonzalez Member Posts: 1 Blue Ribbon

    Hi David!

    Did you find the solution for your "ORA-29259: end-of-input reached" problem?

    Regards!

    JOSE