Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 392 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
ORA-29259 when calling REST API from PL/SQL

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
-
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;
-
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
-
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.
-
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 -
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.
-
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.
-
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 -
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,
David1) 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.
-
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.
-
Hi David!
Did you find the solution for your "ORA-29259: end-of-input reached" problem?
Regards!
JOSE