Skip to Main Content

APEX

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Anyone integrating Google Apps Provisioning API via Oracle or APEX ?

Jaydipsinh RauljiJul 15 2011 — edited Mar 25 2019
Hi,

I am working on the google apps - education version.

I am able to get auth token using ClientLogin Interface system.

But when trying to make request for other actions like retrieve, create , update users getting errors:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure

The request url is like :

utl_http.begin_request('https://apps-apis.google.com/a/feeds/mydomain.com/user/2.0/myusername','GET', 'HTTP/1.1').

So can any one please help me on this?

I'll appreciate if some one provide some piece of code on the OAuth for the education google app. and for different actions.

Thanks in advance.

Thanks & Regards,
Jaydipsinh Raulji
[www.oracleapexconsultant.com|http://www.oracleapexconsultant.com]
This post has been answered by John Edward Scott on Jul 15 2011
Jump to Answer

Comments

John Edward Scott Jul 15 2011
Hello

Yes we have done that (we've implemented most of the Google API's like Calendar, Contacts, Provisioning, GMail etc in PL/SQL and might be releasing them soon).

From your error -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
It tells me that you haven't imported the Google SSL Certificate in your Oracle Wallet (or you do not have an intermediate Certificate in your Wallet)

Hope this helps,

John
--------------------------------------------
Blog: http://jes.blogs.shellprompt.net
Work: http://www.apex-evangelists.com
Author of Pro Application Express: http://tinyurl.com/3gu7cd
REWARDS: Please remember to mark helpful or correct posts on the forum, not just for my answers but for everyone!
trent Jul 15 2011 — edited on Jul 15 2011
Jes wrote:
(we've implemented most of the Google API's like Calendar, Contacts, Provisioning, GMail etc in PL/SQL and might be releasing them soon).
As another commercial offering? Either way, /subscribes with interest.

Trent
John Edward Scott Jul 15 2011
Trent,

Drop me a mail (john@apex-evangelists.com).

Thanks

John.
--------------------------------------------
Blog: http://jes.blogs.shellprompt.net
Work: http://www.apex-evangelists.com
Author of Pro Application Express: http://tinyurl.com/3gu7cd
REWARDS: Please remember to mark helpful or correct posts on the forum, not just for my answers but for everyone!
Jaydipsinh Raulji Jul 15 2011
John,

Thanks for reply.

I have configured oracle wallet.

and able to get make a https call for authentication :

-- access the oracle wallet to allow us to make an https request

utl_http.set_wallet( path => 'file:/walletpath', password => 'walletpassword');

-- set up the request body with our credentials
l_params := 'Email=' || p_email || '&Passwd=' || p_password || '&accountType=HOSTED&service=apps';

l_request := utl_http.begin_request('https://www.google.com/accounts/ClientLogin', 'POST' , 'HTTP/1.1');


When making a requests for other urls - reporting api & calendar , its giving proper response.

SELECT utl_http.request('https://www.google.com/hosted/services/v1.0/reports/ReportingData' ,null, 'file:/walletpath' , 'walletpassword' ) from dual;

SELECT utl_http.request('https://www.google.com/calendar/hosted/gmailtest.sdsu.edu' ,null, 'file:/walletpath' , 'walletpassword' ) from dual;

but when i executed request for :

SELECT utl_http.request('https://apps-apis.google.com/a/feeds/mydomain/user/2.0/myusername',null,'file:/walletpath','walletpassword') from dual;

getting errors:

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1577 ORA-29024: Certificate validation failure

Can you please revert back on my email [ jpraulji@gmail.com] .

I'll appreciate your help.

Thanks in advance.

Jaydipsinh Raulji
John Edward Scott Jul 15 2011
Answer
Jaydipsinh

You emailed me directly (and I replied to that mail), but you also posted the exact same question here, so I'm repasting my email reply for completeness.

The error -
ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1577 ORA-29024: Certificate validation failure
tells me that the Oracle Wallet cannot negotiate with the remote certificate (hence the Wallet either does not contain the correct certificate, or requires an intermediate certificate depending on your configuration)

As you mentioned -
SELECT utl_http.request('https://www.google.com/hosted/services/v1.0/reports/ReportingData' ,null, 'file:/walletpath' , 'walletpassword' ) from dual;
SELECT utl_http.request('https://www.google.com/calendar/hosted/gmailtest.sdsu.edu' ,null, 'file:/walletpath' , 'walletpassword' ) from dual;
those work, so you have imported the correct certificate for https://www.google.com, however -
SELECT utl_http.request('https://apps-apis.google.com/a/feeds/mydomain/user/2.0/myusername',null,'file:/walletpath','walletpassword') from dual;
notice that is a different URL (hostname), namely apps-apis.google.com rather than www.google.com, so it sounds to me like you don't have the certificate for apps-apis.google.com imported in your Oracle wallet.

John.
--------------------------------------------
Blog: http://jes.blogs.shellprompt.net
Work: http://www.apex-evangelists.com
Author of Pro Application Express: http://tinyurl.com/3gu7cd
REWARDS: Please remember to mark helpful or correct posts on the forum, not just for my answers but for everyone!
Marked as Answer by Jaydipsinh Raulji · Sep 27 2020
Arka Aug 15 2011
notice that is a different URL (hostname), namely apps-apis.google.com rather than www.google.com, so it sounds to me like you don't have the certificate for apps-apis.google.com imported in your Oracle wallet.
How can you get SSL certificate for apps-apis.google.com?
I have the same problem with smtp.gmail.com - could you please explain how to obtain SSL certificate for smtp.gmail.com?
Tomasz Ceran Sep 22 2011 — edited on Sep 22 2011
It may be helpful:
http://arkatec.wordpress.com/2011/08/15/sending-email-using-oracle-database-and-google-mail-service/

important:
add SCHEMES parameter with 'PLAIN' value in utl_smtp.auth procedure
 v_smtp_conn :=  utl_smtp.open_connection(
                        host => 'smtp.gmail.com'
                        port => 587, 
                        wallet_path => 'file:/walletpath',
                        wallet_password => 'walletpass',
                        secure_connection_before_smtp => false);
   utl_smtp.helo(v_smtp_conn, v_mail_host_name);
   utl_smtp.starttls(v_smtp_conn);
   utl_smtp.auth(v_smtp_conn, v_gmail_user ,v_gmail_pass,'PLAIN');
.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 20 2011
Added on Jul 15 2011
7 comments
3,233 views