Skip to Main Content
  • Questions
  • ACL created but accessing gives ORA-29273 ORA-12541

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jayeeta.

Asked: February 16, 2016 - 11:42 pm UTC

Last updated: July 07, 2021 - 3:13 am UTC

Version: 11.2.0.4

Viewed 50K+ times! This question is

You Asked

I have created a ACL and assigned it to a host. When accessing I get the above erros.

I did the following steps

SQL> exec dbms_network_acl_admin.create_acl(acl=>'testlitle.xml', description=> 'all hctra.net connections',principal=>'TAG_OWNER't=>true,privilege=>'connect');

PL/SQL procedure successfully completed.

SQL> exec dbms_network_acl_admin.add_privilege(acl=>'testlitle.xml', principal=>'TAG_OWNER', is_grant=>true,privilege=>'resolve');

PL/SQL procedure successfully completed.

SQL> exec dbms_network_acl_admin.assign_acl(acl=>'testlitle.xml',host=>'*.testlitle.com');

PL/SQL procedure successfully completed.

Not sure if it was needed, I also created a wallet for accessing the secure websiute

HTD1#>orapki wallet create -wallet /app/oracle/product/11204/dbv1_JANPSU2016/wallet -pwd Sl33pl3ss -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

HTD1#>orapki wallet add -wallet /app/oracle/product/11204/dbv1_JANPSU2016/wallet -trusted_cert -cert "base64_encode_4_litle.cer" -pwd Sl33pl3ss
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.


But then, I get the below error

SQL> select substr(utl_http.request(' https://www.litle.com' ),1,30) from dual;
select substr(utl_http.request(' https://www.litle.com' ),1,30) from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-12541: TNS:no listener
ORA-06512: at line 1


Please help



and Connor said...

I've just done the following end to end test and it works - so edit accordingly for your site and try it there, see how you go:

mkdir c:\oracle\wallet
orapki wallet create -wallet c:\oracle\wallet -pwd MyWallePassword999 -auto_login
orapki wallet add -wallet c:\oracle\wallet -trusted_cert -cert c:\temp\cert1.cer -pwd MyWallePassword999
orapki wallet add -wallet c:\oracle\wallet -trusted_cert -cert c:\temp\cert2.cer -pwd MyWallePassword999
orapki wallet add -wallet c:\oracle\wallet -trusted_cert -cert c:\temp\cert3.cer -pwd MyWallePassword999

where the three certificate files were the three files I got for www.litle.com

Then I did the following ACL

SQL> begin
  2    dbms_network_acl_admin.create_acl (
  3      acl          => 'utl_http.xml',
  4      description  => 'my acl',
  5      principal    => 'MCDONAC',
  6      is_grant     => TRUE,
  7      privilege    => 'connect',
  8      start_date   => null,  
  9      end_date     => null);
 10    commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    dbms_network_acl_admin.add_privilege (
  3      acl         => 'utl_http.xml',
  4      principal   => 'MCDONAC',
  5      is_grant    => false,
  6      privilege   => 'connect',
  7      position    => null,
  8      start_date  => null,
  9      end_date    => null);
 10
 11    commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.




And then ran the following

SQL> set serverout on
SQL> declare
  2    l_url            varchar2(100) := 'https://www.litle.com/';
  3    l_req            utl_http.req;
  4    l_result         utl_http.resp;
  5    l_data           varchar2(32767);
  6  begin
  7    utl_http.set_wallet('file:C:\oracle\wallet', 'MyWallePassword999');
  8    l_req  := utl_http.begin_request(l_url);
  9    l_result := utl_http.get_response(l_req);
 10
 11    begin
 12      loop
 13        utl_http.read_text(l_result, l_data, 1000);
 14        dbms_output.put_line (l_data);
 15      end loop;
 16    exception
 17      when utl_http.end_of_body then
 18        utl_http.end_response(l_result);
 19    end;
 20  end;
 21  /


and out came the web site content ....


Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jayeeta Sarkar, February 24, 2016 - 4:52 pm UTC

We have a proxy server and I have assigned the proxy to the ACL but we still get the ORA-12541: TNS:no listener error
Chris Saxon
February 25, 2016 - 4:09 am UTC

I have a search around the Support. All the info regarding ORA-12541 with UTL_HTTP typically seems to be around network configuration (firewalls etc).

Some of the examples listed were:

- incorrect proxy port
- IP4 vs IP6 clash
- incorrect hosts file setup
etc.

You might need to chat with your network administrator.

Sorry.

Jayeeta Sarkar, February 25, 2016 - 3:22 pm UTC

Thanks for the update.

Looks like I was pointing to the wrong port. Now that I am pointing to the correct port, I do not get the 'no listener' error anymore but I get the following.

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-29259: end-of-input reached
ORA-06512: at line 1

when I execute

select utl_http.request(' https://www.prelive.litle.com', 'sdev-prelive-litle.hctra.pri:443', '/app/oracle/product/11204/dbv1_JANPSU2016/wallet','*****') from dual;

I have also applied the patch#20551790, as found in the doc 1996347.1; but it did not help

Any help to resolve this will be much appreciated.

Thanks
Chris Saxon
February 26, 2016 - 1:20 am UTC

Now that you've got the right port, try my original script which has a handler for 'utl_http.end_of_body' and see how you go.

Jayeeta Sarkar, February 26, 2016 - 6:37 pm UTC

still get the same error.

I tried to create a procedure as below, (this is almost same as the one you had)

CREATE OR REPLACE PROCEDURE tag_owner.show_html_from_url (p_url IN VARCHAR2) AS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_text VARCHAR2(32767);
BEGIN
-- Make a HTTP request and get the response.
l_http_request := UTL_HTTP.begin_request(p_url);
l_http_response := UTL_HTTP.get_response(l_http_request);

-- Loop through the response.
BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32766);
DBMS_OUTPUT.put_line (l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
RAISE;
END show_html_from_url;
/

and then from SQL prompt, did the following

SQL> exec utl_http.set_proxy('sdev-prelive-litle.hctra.pri:443');

PL/SQL procedure successfully completed.

SQL> exec utl_http.set_wallet('file: /app/oracle/product/11204/dbv1_JANPSU2016/wallet', '***');

PL/SQL procedure successfully completed.

Then, executed the procedue

SQL> exec show_html_from_url(' https://prelive.litle.com' );
BEGIN show_html_from_url(' https://prelive.litle.com' ); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1525
ORA-29261: bad argument
ORA-06512: at "TAG_OWNER.SHOW_HTML_FROM_URL", line 22
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29259: end-of-input reached
ORA-06512: at line 1


Any idea what could be the issue ?



Connor McDonald
February 28, 2016 - 1:14 am UTC

All I can think of is something in a proxy layer interfering with the data, because your proc seems fine. I know "works on my machine" doesnt help you much :-(

SQL> exec  utl_http.set_wallet('file:C:\oracle\wallet', '**********');

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE show_html_from_url (p_url IN VARCHAR2) AS
  2  l_http_request UTL_HTTP.req;
  3  l_http_response UTL_HTTP.resp;
  4  l_text VARCHAR2(32767);
  5  BEGIN
  6  -- Make a HTTP request and get the response.
  7  l_http_request := UTL_HTTP.begin_request(p_url);
  8  l_http_response := UTL_HTTP.get_response(l_http_request);
  9
 10  -- Loop through the response.
 11  BEGIN
 12  LOOP
 13  UTL_HTTP.read_text(l_http_response, l_text, 32766);
 14  DBMS_OUTPUT.put_line (l_text);
 15  END LOOP;
 16  EXCEPTION
 17  WHEN UTL_HTTP.end_of_body THEN
 18  UTL_HTTP.end_response(l_http_response);
 19  END;
 20  EXCEPTION
 21  WHEN OTHERS THEN
 22  UTL_HTTP.end_response(l_http_response);
 23  RAISE;
 24  END show_html_from_url;
 25  /

Procedure created.

SQL> exec show_html_from_url('https://www.litle.com/');

[html output as expected]



Check out MOS note 1304804.1 - it might be a factor here.

Hope this helps.

Jayeeta Sarkar, March 01, 2016 - 7:23 pm UTC

How do I check for the 'mutual authentication' between our system and target system ?
Chris Saxon
March 02, 2016 - 12:26 am UTC

https://en.wikipedia.org/wiki/Mutual_authentication

You'd need to check with your network and security admin's

But as I said: it *might* be a factor here




APEX_WEB_SERVICE.make_rest_request call using HTTPS

Dipika Poraniya, December 12, 2018 - 1:42 pm UTC

Hi experts,

I am having same problem with APEX_WEB_SERVICE.make_rest_request(),I also created oracle wallet and also create and add and assign appropriate privileges to user as you suggested in this post,but there is problem with rest API call from remote client.can you please help me.

code: select APEX_WEB_SERVICE.make_rest_request(
p_url => ' https://*.clientname.com',
p_http_method => 'GET',
p_wallet_path => 'file:///home/oracle/mywallet',
p_wallet_pwd => 'TestC3#1')from dual;
Connor McDonald
December 13, 2018 - 4:25 am UTC

Check out dbms_network_acl_admin calls in the original answer to this question. You need to allow network access from the database to the outside world.

APEX_WEB_SERVICE.make_rest_request call using HTTPS

Dipika Poraniya, December 12, 2018 - 1:44 pm UTC

As i have informed about fetching data from remote client,
Below are errors which i'm facing


ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "APEX_180200.WWV_FLOW_WEB_SERVICES", line 1017
ORA-06512: at "APEX_180200.WWV_FLOW_WEB_SERVICES", line 1523
ORA-06512: at "APEX_180200.WWV_FLOW_WEBSERVICES_API", line 369
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.

notwork access Given

Dipika Poraniya, December 13, 2018 - 7:54 am UTC

Thanks for your response Connor.

I have checked Network,It's allowed 1521 to client's ip from AWS web services.

Again also recreated ACL.

Can you please give me step by step example to connect external API from local oracle 11G DB using
apex_web_service.make_rest_request.

Please Help...
Thanking uou in advance.


Connor McDonald
December 17, 2018 - 2:51 am UTC

1521 is (typically) a sqlnet connection. You are not using sqlnet here - you are calling a web service over http/https.

Here's an example of me calling a free web service on the net

http://www.webservicex.net:500/Prices?ticker=BASMNYARA189N

SQL> CONN / AS SYSDBA
Connected.
--
-- allow apex to get out to www.webservicex.net on port 500
--
SQL> DECLARE
  2    l_principal VARCHAR2(20) := 'APEX_180200';
  3  BEGIN
  4    DBMS_NETWORK_ACL_ADMIN.append_host_ace (
  5      host       => 'www.webservicex.net',
  6      lower_port => 500,
  7      upper_port => 500,
  8      ace        => xs$ace_type(privilege_list => xs$name_list('http'),
  9                                principal_name => l_principal,
 10                                principal_type => xs_acl.ptype_db));
 11  END;
 12  /

PL/SQL procedure successfully completed.

--
-- then call the web service
--
SQL> set serverout on
SQL> declare
  2    l_clob    CLOB;
  3    l_result  VARCHAR2(32767);
  4  BEGIN
  5
  6    l_clob := APEX_WEB_SERVICE.make_rest_request(
  7      p_url         => 'http://www.webservicex.net:500/Prices',
  8      p_http_method => 'GET',
  9      p_parm_name   => APEX_UTIL.string_to_table('ticker'),
 10      p_parm_value  => APEX_UTIL.string_to_table('BASMNYARA189N')
 11    );
 12
 13    DBMS_OUTPUT.put_line('l_clob=' || l_clob);
 14
 15  END;
 16  /
l_clob=[{"internalId":"5ba651504ad8714da42465fa","ticker":"BASMNYARA189
ticker":"BASMNYARA189N","date":"
1991-01-01T00:00:00Z","value":7822885000.0},{"internalId":"5ba651504ad8
ernalId":"5ba651504ad8714da42466
0f","ticker":"BASMNYARA189N","date":"1993-01-01T00:00:00Z","value":1498
...
...
...


APEX_WEB_SERVICE.make_rest_request call using HTTPS

Dipika Poraniya, December 20, 2018 - 6:01 am UTC

Hi Connor,

Thanks For response..

There is still error with APEX_WEB_SERVICE.make_rest_request,But I did it with UTL_HTTP.begin_request using another example from yours in this site.

And it's done successfully,

Thank you.

Connor McDonald
December 20, 2018 - 7:36 am UTC

For APEX_WEB_SERVICE, you'll need a wallet (just like UTL_HTTP).

You'd add:

p_wallet_path => 'file:/data/wallet',
p_wallet_pwd => 'TheWalletPassword'

as parameters to the call

ORA-29273: HTTP request failed

Arjun Sah, December 09, 2019 - 2:04 pm UTC

CREATE OR REPLACE FUNCTION imedical.translation_aks (
p_words IN CLOB, -- words to be translated
p_to IN VARCHAR2 DEFAULT 'en', -- language to translate to
p_from IN VARCHAR2 DEFAULT 'ar'
) -- language to translate from
-- ar = Arabic
-- en = English
-- es = Spanish
-- fr = French
RETURN CLOB AS
l_res CLOB;
l_words CLOB;
BEGIN
l_res := httpuritype(' http://translate.google.com/?hl='
|| p_from
|| '&layout=1&eotf=1&sl='
|| p_from
|| '&tl='
|| p_to
|| '&text='
|| utl_url.escape(p_words)
|| '#').getclob();

l_res := substr(l_res, instr(l_res, '<span title="'
|| p_words
|| '"'));

l_res := substr(l_res, 1, instr(l_res, '</span>') + 6);

l_words := xmltype(l_res).extract('/span/text()').getstringval();
RETURN l_words;
END;
===============================
select imedical.translation_aks ('cats and dogs', 'ar') from dual;

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1817
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.HTTPURITYPE", line 34
ORA-06512: at "IMEDICAL.TRANSLATION_AKS", line 14
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
Connor McDonald
December 13, 2019 - 3:17 am UTC

See MOS 2462431.1.

Most likely you are missing a proxy, or have another network issue blocking your request.

Doesn't work in a procedure

Sammy, April 07, 2020 - 7:11 pm UTC

Back to the original question and your answer - if I make this anonymous block a procedure I get:
ORA-29273: HTTP request failed
ORA-28860: Fatal SSL error
ORA-06512: at "SYS.UTL_HTTP", line 368
...
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.

If I make the procedure use authid current_user it works.

We're version using 12.1.0.2. The ACL and wallet appear to be setup correctly, because it works fine as an anonymous block.

Thanks for any help you can give.
Connor McDonald
April 09, 2020 - 1:16 am UTC

And all of this is being done as the same account? ie, same owner for procedure and same for anon block etc ?

Sammy, April 21, 2020 - 2:41 pm UTC

Yes - same owner for procedure and anonymous block. Also of note: If I make the procedure "authid current_user" it works fine.
Connor McDonald
April 22, 2020 - 4:06 am UTC

Can you give us a top to bottom test case using

http://www.webservicex.net:500/Prices?ticker=BASMNYARA189N

as the target.

We'll try replicate here.

Sammy, April 23, 2020 - 4:25 pm UTC

The issue I'm having requires https, a certificate to connect to the site, and a wallet.
Connor McDonald
April 24, 2020 - 2:48 am UTC

OK, find a different accessible ssl target and we'll try that.

ORA-29259: end-of-input reached

Mohamed Abuhaya, January 23, 2021 - 10:53 pm UTC

Hello Dear,
I need you help, which I done all the steps, but stuck with this error
ORA-29259: end-of-input reached.

Success Testing:
http for other request. done

my problem exactly in https request, you will asking me about wallet "I did it also".

Pls guide me to resolve this problem.


Connor McDonald
January 28, 2021 - 9:13 am UTC

That might be related to your version.

Check MOS notes 2303905.1 and 1602116.1

ORA-29259: end-of-input reached Error in Oracle apex

Thilak, June 13, 2021 - 11:00 am UTC

DB Version is 12.2.0.1.0, Using oracle apex ORDS
Created ACL and Wallet configured
by using below step

1.Application Server certificates are added in DB Wallet
2.DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
3.DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
4.DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL
5.DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE



select UTL_HTTP.REQUEST( ' https://localhostr:8443/ords', null ,'file:/oracle/product/appwallet', 'App1234$' ) from dual;


Still i am getting the below error

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1501
ORA-29259: end-of-input reached
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1441
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"

Connor McDonald
July 07, 2021 - 3:13 am UTC

Need a complete test case

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library