Home » SQL & PL/SQL » SQL & PL/SQL » Want to trigger the URL using UTL_HTTP (12.1.0.2.0)
Want to trigger the URL using UTL_HTTP [message #681222] Sat, 27 June 2020 07:19 Go to next message
kavikeerthana
Messages: 3
Registered: February 2020
Junior Member
Hi Expects,

I am using database version 12.1.0.2.0. I have a requirement to call an external URL.

I am using the following code.

create or replace
PROCEDURE show_html_from_url (
  p_url              IN  VARCHAR2,
  p_wallet_path      IN  VARCHAR2 DEFAULT NULL,
  p_wallet_password  IN  VARCHAR2 DEFAULT NULL
) AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_text           VARCHAR2(4000);
BEGIN

      DBMS_OUTPUT.put_line ('Started');

  -- If using HTTPS, open a wallet containing the trusted root certificate.
  IF p_wallet_path IS NOT NULL AND p_wallet_password IS NOT NULL THEN
    UTL_HTTP.set_wallet('file:' || p_wallet_path, p_wallet_password);
  END IF;
        DBMS_OUTPUT.put_line ('hitting');

  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_url);
  
  exception when others then
        DBMS_OUTPUT.put_line ('Exception');
      dbms_output.put_line(sys.utl_http.GET_DETAILED_SQLERRM());


  -- Use basic authentication if required.
  /*IF p_username IS NOT NULL and p_password IS NOT NULL THEN
    UTL_HTTP.set_authentication(l_http_request, p_username, p_password);
  END IF;*/

  --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, 3999);
      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;

To use this procedure I have configured a wallet using orapki.

Now I am calling the procedure using exec.

EXEC show_html_from_url('Expected URL to call ', 'wallet_path', 'Wallet Password);
When I am running the above procedure.

Started
hitting
Exception
ORA-28860: Fatal SSL error
Since I am new to this error. Kindly guide me to resolve this.
Want to trigger the URL using UTL_HTTP [message #681223 is a reply to message #681222] Sat, 27 June 2020 07:27 Go to previous messageGo to next message
kavikeerthana
Messages: 3
Registered: February 2020
Junior Member
Hi Expects,

I am using database version 12.1.0.2.0. I have a requirement to call an external URL.
I am using the following code.

create or replace
PROCEDURE show_html_from_url (
p_url IN VARCHAR2,
p_wallet_path IN VARCHAR2 DEFAULT NULL,
p_wallet_password IN VARCHAR2 DEFAULT NULL
) AS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_text VARCHAR2(4000);
BEGIN

DBMS_OUTPUT.put_line ('Started');

-- If using HTTPS, open a wallet containing the trusted root certificate.
IF p_wallet_path IS NOT NULL AND p_wallet_password IS NOT NULL THEN
UTL_HTTP.set_wallet('file:' || p_wallet_path, p_wallet_password);
END IF;
DBMS_OUTPUT.put_line ('hitting');

-- Make a HTTP request and get the response.
l_http_request := UTL_HTTP.begin_request(p_url);

exception when others then
DBMS_OUTPUT.put_line ('Exception');
dbms_output.put_line(sys.utl_http.GET_DETAILED_SQLERRM());


-- Use basic authentication if required.
/*IF p_username IS NOT NULL and p_password IS NOT NULL THEN
UTL_HTTP.set_authentication(l_http_request, p_username, p_password);
END IF;*/

--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, 3999);
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;
To use this procedure I have configured a wallet using orapki.

Now I am calling the procedure using exec.
EXEC show_html_from_url('Expected URL to call ', 'wallet_path', 'Wallet Password);
When I am running the above procedure.

Started
hitting
Exception
ORA-28860: Fatal SSL error
Since I am new to this error. Kindly guide me to resolve this.
Re: Want to trigger the URL using UTL_HTTP [message #681224 is a reply to message #681223] Sat, 27 June 2020 07:59 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
The EXCEPTION handler is causing more problems than it solves. It obscures the actual SQL statement that throws the error.

REMOVE EXCEPTION code entirely & rerun to show us the problem statement

http://www.orafaq.com/wiki/WHEN_OTHERS

Re: Want to trigger the URL using UTL_HTTP [message #681225 is a reply to message #681223] Sat, 27 June 2020 08:00 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
You need to simplify your test. For example, what happens when you run this from SQL*Plus:
select utl_http.request('https://whatever',wallet_path=>'the full path',wallet_password=>'whatever it is') from dual;
Use copy/paste to show what happens, with the actual parameters.
Re: Want to trigger the URL using UTL_HTTP [message #681227 is a reply to message #681222] Sat, 27 June 2020 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-28860: Fatal SSL error
 *Cause:  An error occurred during the SSL connection to the peer. It is likely
          that this side sent data which the peer rejected.
 *Action:  Enable tracing to determine the exact cause of this error.
           Contact Oracle customer support if needed.

[Updated on: Sun, 28 June 2020 07:35]

Report message to a moderator

Re: Want to trigger the URL using UTL_HTTP [message #681234 is a reply to message #681227] Sun, 28 June 2020 07:19 Go to previous messageGo to next message
kavikeerthana
Messages: 3
Registered: February 2020
Junior Member
Hi,

Thanks for the update.

Please help me to understand how to enable the tracing.
Re: Want to trigger the URL using UTL_HTTP [message #681235 is a reply to message #681234] Sun, 28 June 2020 07:49 Go to previous message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
https://lmgtfy.com/?q=oracle+enable+ssl+tracing
Previous Topic: Creating a record from existing random records (5 merged)
Next Topic: ORA-00904: : invalid identifier
Goto Forum:
  


Current Time: Sat Sep 26 15:39:02 CDT 2020