Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

utl_http https certificate using pl/sql in a script works fine, but get 403 error when using procedu

J. AndréJun 4 2014 — edited Jun 4 2014

I have this script to use UTL_HTTP.

DECLARE

req   UTL_HTTP.REQ;

resp  UTL_HTTP.RESP;

value VARCHAR2(1024);

BEGIN

  req := UTL_HTTP.BEGIN_REQUEST('http://dba-oracle.com');

  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');

  resp := UTL_HTTP.GET_RESPONSE(req);

  LOOP

    UTL_HTTP.READ_LINE(resp, value, TRUE);

    dbms_output.put_line(value);

  END LOOP;

  UTL_HTTP.END_RESPONSE(resp);

EXCEPTION

  WHEN UTL_HTTP.END_OF_BODY THEN

    UTL_HTTP.END_RESPONSE(resp);

END;

/

I adjusted the script to use the Oracle Wallet using https with certificate:

declare

  req    utl_http.req;

  resp   utl_http.resp;

  value  varchar2(1024);

  l_url  varchar2(2000) := 'https://******.*****.nl:443';

  l_data clob;

  cursor c_data is

    select clobdata

    from   post_file;

begin

  open c_data;

  fetch c_data into l_data;

  close c_data;

  utl_http.set_proxy('168.0.0.1:8080', '');

  utl_http.set_wallet('file:C:\wallet', '*******');

  req := utl_http.begin_request(l_url);

  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

  utl_http.set_header(req, 'content-length', length(l_data));

  utl_http.write_text(req, l_data);

  resp := utl_http.get_response(req);

  loop

    utl_http.read_line(resp, value, true);

    dbms_output.put_line(value);

  end loop;

  utl_http.end_response(resp);

exception

  when utl_http.end_of_body then

    utl_http.end_response(resp);

end;

/

This works fine and I get a good response.

But when I put it in a procedure I get:

HTTP Error 403.7 - Forbidden: SSL client certificate is required.

Internet Information Services (IIS)

How is this possible and how can I solve this?

Message was edited by: J. André

I now created the procedure in the sys scheme and it works there. So the question is: what rights are missing for the user scheme, which makes it impossible to use the procedure, but possible doing it by script?

Comments

Paul Horth

Don't you think it would be helpful to post the procedure?

J. André

Thank you for your reply. I didn't think so because it is the same as the script. But here it is:

create or replace procedure sp_post_https

as

  req    utl_http.req;

  resp   utl_http.resp;

  value  varchar2(1024);

  l_url  varchar2(2000) := 'https://******.*****.nl:443';

  l_data clob;

  cursor c_data is

    select clobdata

    from   post_file;

begin

  open c_data;

  fetch c_data into l_data;

  close c_data;

  utl_http.set_proxy('168.0.0.1:8080', '');

  utl_http.set_wallet('file:C:\wallet', '*******');

  req := utl_http.begin_request(l_url);

  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

  utl_http.set_header(req, 'content-length', length(l_data));

  utl_http.write_text(req, l_data);

  resp := utl_http.get_response(req);

  loop

    utl_http.read_line(resp, value, true);

    dbms_output.put_line(value);

  end loop;

  utl_http.end_response(resp);

exception

  when utl_http.end_of_body then

    utl_http.end_response(resp);

end;

This is the exact response:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<HTML><HEAD><TITLE>The page requires a client certificate</TITLE>

<META HTTP-EQUIV="Content-Type" Content="text/html; charset=Windows-1252">

<STYLE type="text/css">

  BODY { font: 8pt/12pt verdana }

  H1 { font: 13pt/15pt verdana }

  H2 { font: 8pt/12pt verdana }

  A:link { color: red }

  A:visited { color: maroon }

</STYLE>

</HEAD><BODY><TABLE width=500 border=0 cellspacing=10><TR><TD>

<h1>The page requires a client certificate</h1>

The page you are attempting to access requires your browser to have a Secure Sockets Layer (SSL) client certificate that the Web server will recognize. The client certificate is used for identifying you as a valid user of the resource.

<hr>

<p>Please try the following:</p>

<ul>

<li>Contact the Web site administrator if you believe you should be able to view this directory or page without a client certificate, or to obtain a client certificate.</li>

<li>If you already have a client certificate, use your Web browser's security features to ensure that your client certificate is installed properly. (Some Web browsers refer

to client certificates as browser or personal certificates.)</li>

</ul>

<h2>HTTP Error 403.7 - Forbidden: SSL client certificate is required.<br>Internet Information Services (IIS)</h2>

<hr>

<p>Technical Information (for support personnel)</p>

<ul>

<li>Go to <a href="http://go.microsoft.com/fwlink/?linkid=8180">Microsoft Product Support Services</a> and perform a title search for the words <b>HTTP</b> and <b>403</b>.</li>

<li>Open <b>IIS Help</b>, which is accessible in IIS Manager (inetmgr),

and search for topics titled <b>About Certificates</b>, <b>Using Certificate Trust Lists</b>, <b>Enabling Client Certificates</b>, and <b>About Custom Error Messages</b>.</li>

</ul>

</TD></TR></TABLE></BODY></HTML>

Paul Horth

Where is your database server? On the windows machine where c:\wallet is?

J. André

Yes, both on the same machine (Microsoft Windows Server 2008 R2 (64-bit)).

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Paul Horth

Are you running these as the same user - with the correct ACL applied?

J. André

Yes, same user and correct ACL applied. Also ran by sys and sysman, same results.

Paul Horth 
J. André wrote:




Yes, same user and correct ACL applied. Also ran by sys and sysman, same results.

Sorry, I've run out of ideas at the moment - but I'll keep thinking about it.

1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 2 2014
Added on Jun 4 2014
#procedure, #script
7 comments
1,013 views