oracle-databaseplsqlxml-parsingoracle-apexwebservice-client

Consume XML data from external URL(basic auth) using APEX_WEB_SERVICE to move into Oracle table


There are 65 URLs generating XML data and ask for Windows credentials when hit over browser.

URL format: http://deda1w6114:18080/discoverant-ws/agdata/agmanifest/ URL format without credentials: http://USERID:PASSWORD@deda1w6114:18080/discoverant-ws/agdata/agmanifest/

I tried with sample URL: http://quotes.rest/qod.json&#8217, and XML is getting fetched, but getting error when tried with original one.

Error report - ORA-29273: HTTP request failed ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1035 ORA-12545: Connect failed because target host or object does not exist ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1148 ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 934 ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1580 ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 408 ORA-06512: at line 4 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.

I need to apply this fix for one URL and then to other 64. As this is http so not sure if SSL is needed. I need guidance on steps that need to be performed to achieve this.

I also don't want to hard code my windows credentials for these URLs. Sorry as I don't have expertise on this part but happy to learn this. Please help.


Solution

  • Ping the hostname from Oracle server and mark the response. FQDN urls should be used if its restricted on server level.

    In above problem, using FQDN password resolved the issue.