OraFAQ Forum: SQL & PL/SQL » UTL_HTTP over HTTPS Webservice(2 Merged)

Home » SQL & PL/SQL » SQL & PL/SQL » UTL_HTTP over HTTPS Webservice(2 Merged) (Oracle version 11g Release 11.2.0.1.0 - 64bit Production - Windows Server 2008 Standard - Oracle Wallet)

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

UTL_HTTP over HTTPS Webservice(2 Merged) [message #509880]

Wed, 01 June 2011 08:21

dieffe
Messages: 4
Registered: June 2011
Location: Italy

Junior Member

Hi. I am new to this forum and for the first time I'm trying to consume a external web service over https in order to collect data in my Oracle database. I have a user certificate signed by the CA autorithy of the remote https web service.
I spent a lot of time on internet studing this situation and I found that I have to use Oracle Wallet ot store certificate ( both CA certificate and user one ) Is it right?
I use Oracle 11g R2 standard edition.

Working with OWM I have extracted from Internet Explorer Browser where they work fine.
I have imported the CA certificate as Trusted certificate.
I try to import the user certificate but it is imported in the OWM as trusted one, not as user. How can I import a user certificate obtained from third part application in the Oracle wallet?

I rename the .cer file certificate exported from the browser, with the private key and the root certificate as ewallet.p12 then I copied it in the wallet path D:\app\Administrator\product\11.2.0\dbhome_1\BIN. I use the pki command line display:

orapki wallet display -wallet "D:\app\Administrator\product\11.2.0\dbhome_1\BIN\owm\wallets\Administrator" -pwd "Password"

and I read correctly the user certificate and the trusted certificate, but if I try to open the OWM I obtain an Invalid password message and I can't open the wallet. I use the same password of the pki display command.

When I try to use the SQLplus request command:

SELECT utl_http.request('https url',null,'file:D:\APP\ADMINISTRATOR\ADMIN\DFR2\WALLET','Password1') from dual;

of the UTL_HHTP I obtain the follow situation:

C:\Users\Administrator.DIEFFE>sqlplus sys/df@dfr2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mer Giu 1 14:38:08 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connesso a:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> SELECT utl_http.request('https url',null,'file:D:\APP\ADMINISTRATOR\ADMIN\DFR2\WALLET','Password1') from dual;

SELECT utl_http.request('https url',null,'file:D:\APP\ADMINISTRATOR\ADMIN\DFR2\WALLET','Password1') from dual
*
ERRORE alla riga 1:
ORA-29273: richiesta HTTP non riuscita
ORA-06512: a "SYS.UTL_HTTP", line 1722
ORA-28759: apertura del file non riuscita
ORA-06512: a line 1

What's wrong in my procedure? Any idea is welcome !

Report message to a moderator

Re: UTL_HTTP over HTTPS Webservice [message #509884 is a reply to message #509880]

Wed, 01 June 2011 08:33

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

Senior Member
Account Moderator

ORA-29273: HTTP request failed
 *Cause:  The UTL_HTTP package failed to execute the HTTP request.
 *Action: Use get_detailed_sqlerrm to check the detailed error message.
          Fix the error and retry the HTTP request.

Please read OraFAQ Forum Guide.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

UTL_HTTP over HTTPS Webservice [message #509890 is a reply to message #509880]

Wed, 01 June 2011 08:52

dieffe
Messages: 4
Registered: June 2011
Location: Italy

Junior Member

orapki wallet display -wallet "D:\app\Administrator\product\11.2.0\dbhome_1\BIN\owm\wallets\Administrator" -pwd "Password"

and I read correctly the user certificate and the trusted certificate, but if I try to open the OWM I obtain an Invalid password message and I can't open the wallet. I use the same password of the pki display command.

When I try to use the SQLplus request command:

SELECT utl_http.request('https url',null,'file:D:\APP\ADMINISTRATOR\ADMIN\DFR2\WALLET','Password1') from dual;

of the UTL_HHTP I obtain the follow situation:

C:\Users\Administrator.DIEFFE>sqlplus sys/df@dfr2 as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Mer Giu 1 14:38:08 2011
 
Copyright (c) 1982, 2010, Oracle. All rights reserved.
 

Connesso a:
 Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
 
SQL> SELECT utl_http.request('https url',null,'file:D:\APP\ADMINISTRATOR\ADMIN\DFR2\WALLET','Password1') from dual;
 
SELECT utl_http.request('https url',null,'file:D:\APP\ADMINISTRATOR\ADMIN\DFR2\WALLET','Password1') from dual
 *
 ERRORE alla riga 1:
 ORA-29273: richiesta HTTP non riuscita
 ORA-06512: a "SYS.UTL_HTTP", line 1722
 ORA-28759: apertura del file non riuscita
 ORA-06512: a line 1

What's wrong in my procedure? Any idea is welcome !

Report message to a moderator

Re: UTL_HTTP over HTTPS Webservice [message #509893 is a reply to message #509890]

Wed, 01 June 2011 08:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

28759, 00000, "failure to open file"
// *Cause:  The system could not open the specified file.
// *Action: Enable tracing to determine the exact cause of this error.
//          Contact Oracle customer support if needed.

does OS user "oracle" have needed OS permissions to access file "WALLET"?

Report message to a moderator

Re: UTL_HTTP over HTTPS Webservice [message #509895 is a reply to message #509890]

Wed, 01 June 2011 09:04

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

Senior Member
Account Moderator

Quote:

*Action: Use get_detailed_sqlerrm to check the detailed error message.

Regards
Michel

Report message to a moderator

Re: UTL_HTTP over HTTPS Webservice [message #509899 is a reply to message #509893]

Wed, 01 June 2011 09:15

dieffe
Messages: 4
Registered: June 2011
Location: Italy

Junior Member

Thanks for your response BlackSwan.

I have no oracle S.O. user.
I'm working with Administrator user which is in the ora_dba group.
In Sql I'm working with SYS connected as sysdba.

Report message to a moderator

Re: UTL_HTTP over HTTPS Webservice [message #509902 is a reply to message #509899]

Wed, 01 June 2011 09:48

dieffe
Messages: 4
Registered: June 2011
Location: Italy

Junior Member

the result "get_detailed_sqlerrm" function is:

ORA-28857: Errore SSL sconosciuto

Report message to a moderator

Re: UTL_HTTP over HTTPS Webservice [message #509911 is a reply to message #509902]

Wed, 01 June 2011 10:15

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

Senior Member
Account Moderator

ORA-28857: Unknown SSL error
 *Cause:  An unknown SSL error occurred.
 *Action: This error is not normally visible to the user. Enable Oracle Net
          tracing and attempt the connection again. Then contact
          Oracle customer support with the trace output.

Regards
Michel

Report message to a moderator

Previous Topic:	Mix records from overview and from detail table in one report
Next Topic:	Covert id and comma delimited list to rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 07 18:50:58 CDT 2025