My oracle version:
Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production
PL/SQL Release 18.104.22.168.0 - Production
CORE 22.214.171.124.0 Production
TNS for Linux: Version 126.96.36.199.0 - Production
NLSRTL Version 188.8.131.52.0 - Production
Please help me in the below piece of code to invoke webservice using utl_http. I seems a admin set up. But, I could not get the steps to go and ask my admin.
Please give me the steps to invoke the webservice.
v_base_url VARCHAR2(500) := 'https://abcdefghijklmn.com/abc/xyz/PushService/pushToDB.json?';
v_time_out PLS_INTEGER := 60;
v_error_msg VARCHAR2(4000) DEFAULT NULL;
v_conn_req := utl_http.begin_request(v_base_url, 'GET','HTTP/1.0');
utl_http.set_header(v_conn_req, 'Content-Type', 'application/json');
utl_http.set_header(v_conn_req, 'HTTP', v_base_url);
-- Initialize Response
v_conn_resp := utl_http.get_response(v_conn_req);
v_reply_message := '';
-- Retrieve response
utl_http.read_line(v_conn_resp, v_reply_line, TRUE);
v_reply_message := v_reply_message || v_reply_line;
WHEN UTL_HTTP.END_OF_BODY THEN
WHEN UTL_HTTP.request_failed THEN
WHEN OTHERS THEN
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
I have limited knowledge on package UTL_HTTP, but from 11g you need to setup ACL first for the URL your trying access. Check the below link it would give you some information.
For HTTPS you need an Oracle Wallet (file) on the database server. It is created using ORACLE_HOME/bin/owm - and needs to contain the certificate of the web sites you want to access. That certificate will be signed by a root authority. The wallet also need the complete root authority chain of certificates in the wallet.
A wallet is protected by a password. The wallet's location on the database server, and wallet password, need to be used in the UTL_HTTP.set_wallet() call when using UTL_HTTP to access a HTTPS URL.
I got the certificate and I asked DBA to place that certificate in DB server.
then i included the below piece of code in my plsql block. Now. im getting this error.
Added piece of code: utl_http.set_wallet(v_wallet_path, v_wallet_password);
Error message: ORA-28759: failure to open file
In Oracle Wallet Manager this is a tick box in the WALLET menu that says "Auto Login". It needs to be checked and wallet saved. It creates a second file called cwallet.sso that, I believe, is needed for single sign on from Oracle software.
The path seems okay. Question - there are no multi Oracle home issues perhaps like a separate GI home and DB home, and using the wrong home for creating the wallet?
Can be a number of things... You need to isolate the problem and this means minimal moving parts ito testing.
I suggest testing using sqlplus on the server using the "/ as sysdba" logon. Write a test procedure in SYS that does the care basics for accessing the relevant URL using the wallet. (remember to trash it when done)
Use orapki (CLI utility) to view wallet certificates - and add certificates if need be.
Also create a baseline/sanity test - get something easy and simple to work, like testing against https://www.oracle.com - use Firefox to download the certificates and use Firefox to identify the root chain. Export these certificates and create and import these into a new wallet.
Also, if you have a CSI, logon to https://support.oracle.com - there are a number of useful support notes on wallets.