Skip to Main Content
  • Questions

  • REST API in PLSQL Giving ORA-29273 & ORA-12541

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Madhan.

Asked: March 08, 2018 - 10:31 pm UTC

Last updated: March 14, 2018 - 1:48 pm UTC

Version: 12.1.0.2.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Below is my environmental information

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
PL/SQL Release 12.1.0.2.0 -


Below activity performed by DBA

grant execute on utl_http  to schema_name
grant execute on dbms_lock to schema_name

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'scim_api_dev.xml', 
    description  => 'A test of the API DEV - ACL functionality',
    principal    => 'schema_name',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
end;

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (acl       => 'scim_api_dev.xml'
                                      , principal => 'schema_name'
                                      , is_grant  => TRUE
                                      , privilege => 'resolve');
 begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'scim_api_dev.xml',
    host        => 'https://hs-identity-api.example.com/scim/v1/dev/Users', 
    lower_port  => 1,
    upper_port  => 50000);    
end; 
/
commit;


Below is the code snippet, but getting an error "ORA-29273: HTTP request failed ORA-12541: TNS:no listener ORA-06512:"

declare
  req UTL_HTTP.REQ;
  resp UTL_HTTP.RESP;
  val VARCHAR2(2000);
  str varchar2(1000);
begin
  req := UTL_HTTP.BEGIN_REQUEST('https://hs-identity-api.example.com/scim/v1/dev/Users' || '?' || 'filter=userName+Eq+%22madhan%22' || '&'||'attributes=id');
  resp := UTL_HTTP.GET_RESPONSE(req);
  LOOP
    UTL_HTTP.READ_LINE(resp, val, TRUE);
    DBMS_OUTPUT.PUT_LINE(val);
  END LOOP;
  UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY
  THEN
    UTL_HTTP.END_RESPONSE(resp);
END;

and Chris said...

Are you sure the DBA's commands worked? Because if you run:

begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'scim_api_dev.xml',
    host        => 'https://hs-identity-api.example.com/scim/v1/dev/Users', 
    lower_port  => 1,
    upper_port  => 50000);    
end; 
/


Gives:

ORA-24244: invalid host or port for access control list (ACL) assignment


You need to pass just the host. Not the full URL:

begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'scim_api_dev.xml',
    host        => 'hs-identity-api.example.com', 
    lower_port  => 1,
    upper_port  => 50000);    
end; 
/


Anyway, you need to ensure your database server has access to the URI you're trying to access. This may be blocked at the network level.

As MOS note 2198047.1 says:

1. Make sure the Database has access to the site you are attempting to run the UTL_HTTP package against.
If this is a secure Server (ie: no Internet access), then you need to review your usage as well as test with a local URL.


2. Unset any HTTP_PROXY settings (or any HTTP settings showing up in the RAW listener control services that may be in question).

Do not use any HTTP_PROXY settings unless this is required by the program and is correct with the Proxy itself.
Remove any Proxy settings when connecting to an internal only system.

A full Server restart may also be required in some situations to clear any environment settings made outside the user profile.

Further reading:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526497800346930725
https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1

Rating

  (2 ratings)

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

Comments

Madhan Subbiah, March 09, 2018 - 2:17 pm UTC

Yes. DBA tried with host=> ' https://hs-identity-api.example.com/scim/v1/dev/Users' Got an error and then executed with below host=>*.api.oracleindustry.com and also tried with host=>hs-identity-api.example.com

Have below questions - Please help.

1. Make sure the Database has access to the site you are attempting to run the UTL_HTTP package against.
- How to check if database have access to the site from my end? Please provide steps.

If this is a secure Server (ie: no Internet access), then you need to review your usage as well as test with a local URL.
- I just tried the url in google chrome in a fresh laptop where only internet is exist and i got the output as expected
URL -
https://hs-identity-api.oracleindustry.com/scim/v1/ <companyname>dev/Users?filter=userName+Eq+%22Madhan%22&attributes=id


2. Unset any HTTP_PROXY settings (or any HTTP settings showing up in the RAW listener control services that may be in question).
- What is the command to Set and Unset HTTP_PROXY settings via PLSQL from my end ? Please help.

Do not use any HTTP_PROXY settings unless this is required by the program and is correct with the Proxy itself.
- To whom should i reach out to determine if it is required by the program? Please help.

Remove any Proxy settings when connecting to an internal only system.
- How to remove Proxy Settings via PLSQL from my end ? Please help.

A full Server restart may also be required in some situations to clear any environment settings made outside the user profile.

Chris Saxon
March 09, 2018 - 2:48 pm UTC

1. It depends on your OS. Simple method is to see if you can access the site using browser while logged in to the server. Or using a command line tool like wget.

Testing on a laptop is useless. You need to be connected to the server.

2. I believe this refers to environment variables. You can't set these from PL/SQL.

If you're struggling, speak with your network admin.

Madhan Subbiah, March 09, 2018 - 6:38 pm UTC

1. It depends on your OS. Simple method is to see if you can access the site using browser while logged in to the server. Or using a command line tool like wget.
Testing on a laptop is useless. You need to be connected to the server.
-Yes. i am able to access complete url from browser.
2. I believe this refers to environment variables. You can't set these from PL/SQL.
If you're struggling, speak with your network admin.
- Had a meeting with network admin. They are able to see a call begin made to destination from PLSQL but there is a block in firewall and hence recommended to access the destination via Proxy settings from PLSQL.

When i used below command in PLSQL, receiving an error "ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure"

Command
UTL_HTTP.SET_PROXY('webproxy.mycompany.com:8080','');

Would like to know if above command is correct - Please help.

Below is the code snippet.

declare
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
val VARCHAR2(2000);
str varchar2(1000);
begin
UTL_HTTP.SET_PROXY('webproxy.mycompany.com:8080','');
req := UTL_HTTP.BEGIN_REQUEST(' https://hs-identity-api.company.com/' );
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, val, TRUE);
DBMS_OUTPUT.PUT_LINE(val);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY
THEN
UTL_HTTP.END_RESPONSE(resp);
END;
Chris Saxon
March 14, 2018 - 1:48 pm UTC

And what happens when you run the code?

Your best course of action here is to speak with your network administrator. Get them to verify your database server can communicate with the URL you're trying access.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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