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

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;

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

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.

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

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.

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.

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

jariola

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.

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.

Jose R Gonzalez

Hi David!

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

Regards!

JOSE