OraFAQ Forum: SQL & PL/SQL » Want to trigger the URL using UTL

Home » SQL & PL/SQL » SQL & PL/SQL » Want to trigger the URL using UTL_HTTP (12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Want to trigger the URL using UTL_HTTP [message #681222]

Sat, 27 June 2020 07:19

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.

Report message to a moderator

Want to trigger the URL using UTL_HTTP [message #681223 is a reply to message #681222]

Sat, 27 June 2020 07:27

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.

Report message to a moderator

Re: Want to trigger the URL using UTL_HTTP [message #681224 is a reply to message #681223]

Sat, 27 June 2020 07:59

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Want to trigger the URL using UTL_HTTP [message #681225 is a reply to message #681223]

Sat, 27 June 2020 08:00

John Watson
Messages: 8974
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.

Report message to a moderator

Re: Want to trigger the URL using UTL_HTTP [message #681227 is a reply to message #681222]

Sat, 27 June 2020 09:25

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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