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

cormaco
Answer

Here is a corrected version of your query,
I removed the leading slash in the starttime path and added the namespace declaration:

SELECT x.*
FROM owxml t,
   XMLTABLE (
        xmlnamespaces(default 'http://winscp.net/schema/session/1.0'),
        '/session'
        PASSING t.content
        COLUMNS
            starttime VARCHAR2(255) PATH 'group[@name="pwd"]/@start'
        ) x

STARTTIME                     
------------------------------
2020-12-01T18:22:06.383Z
Marked as Answer by CarstenDD · Dec 3 2020
1 person found this helpful
CarstenDD

Hello cormaco,
thanks a lot for the quick response and the solution. Can you tell me, why the xmlnamespaces() is solving the problem?
Regards
Carsten

cormaco

You always have to declare all the namespaces that are referenced in the path expressions in your xmltable statement.
Xquery only matches tags if the name and the namespace are the same.
In you code there was no namespace declaration, so starttime had the empty default namespace xmlns="" while in your xml it was xmlns="http://winscp.net/schema/session/1.0" so the tags did not match.

1 person found this helpful
1 - 3
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,053 views