Trent's Tech Tips

First I want to point out, the code posted here is in my no means complete, but I believe it provides a good foundation to extend on. To point out some of what is lacking - I am not fetching the refresh token to easily get a new access token without user intervention. I am not handling when the access token is no longer valid. Etc. Also, since a lot of the responses result in JSON, I used the PL/JSON package throughout. I also unfortunately cannot set up a demo on apex.oracle.com due to the obvious limitation of the wallet, and using utl_http. You may know, google has a series of API's that allow you to access your data programatically, to create third party apps. Originally when I looked at the docs, it was using OAuth 1, and I never ended up mastering it. Oleg made a post about the differences here:  http://dbswh.webhop.net/htmldb/f?p=BLOG:READ:0::::ARTICLE:889800346602035 The google docs are here, and they are quite detailed with what to do:  https://developers.google.c...

In addition to setting up the ACL, if you wish to make HTTPS requests you will need to set up a wallet - and more than likely, import trusted certificates. On the oracle server, open the wallet manager. In linux this is with the command: owm. Assuming no current wallet exists, create a new one by going to File --> New. At this point you will need to give the wallet a password. Save the wallet and take note of the directory it installed to (this is what you need to pass to utl_http, and no single file). The default directory this is saved to is generally: $ORACLE_HOME/owm/wallets/oracle. The next step is to import trusted certificates into the wallet. The process varies between OS/Browser. First, go to the website you are intending on making requests to and view the certificate information which is normally done by clicking a button in the address bar. Linux Chrome: Click the button the left of the URL Click certificate information Click Details Click Export ...

In order to use utl_http requests from Oracle 11g onward (or any network services for that matter), you need to set up an ACL. Two generic permissions to grant are connect and resolve, where * can be used as a wild card. A good template I commonly use, just replacing the file name and schema name. This should be executed as a user with dba privileges. DECLARE l_filename varchar2(200) := 'file_test_http.xml'; l_schema varchar2(200) := 'FILE_TEST'; BEGIN BEGIN DBMS_NETWORK_ACL_ADMIN.DROP_ACL( acl => l_filename ); EXCEPTION WHEN OTHERS THEN NULL; -- ACL does not exist yet END; DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => l_filename , description => 'All requests to test utl_http' , principal => l_schema -- schema name , is_grant => TRUE , privilege => 'connect' ); ...