Monday, 5 November 2012

Accessing Google Data

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.com/accounts/docs/OAuth2. There are a number of techniques you can follow, and previous presentation slides I have seen/posts have suggested following the type as a desktop application. This relies on some intervention where you need to copy the token and push it back to the application in some way, so I didn't go this route. Rather, I went down the web server application path.

The first thing you need is set up an API project, and enable the API you want access to. This is done through the Google API Console.




After clicking the button, you select what API's you would like to use with google. A common request I see, and the one I am most interested in is the Calendar API, so I'll turn that on, the go to the API Access tab to create an OAuth 2 client ID. Then specify the type as Web Application and your site's hostname. Now, since I am working locally, I am going to leave it as example.com - it doesn't seem to work with an IP address. So it is necessary to also update your hosts file to point example.com at APEX - or just use your actual apex environment URL.



This will give you two bits of crucial information that you need to authorize - client ID and client secret. The next step is to set the redirct URI. When you want to authorize, the user is redirected to google - then google will either redirect you straight away back to the callback, or you will need to grant access to the app. 

The redirect URI is the first challenge, since you cannot re-direct back to an APEX page. The reason for this is that every apex page is the result of the f procedure. When google re-directs, it passes one of a few query string parameters back - all of which the f procedure doesn't recognise. 

So I made a package called google_utl, with a procedure called authorization_call_back. Then, in the API console, I updated the redirect URI to point to this procedure (during testing, I just pointed it to http://localhost, so that it was easy to see what is being passed back etc):

http://example.com:8888/apex/google.google_utl.authorization_code_callback.

Before I post the actual code, I should also point out that I am storing the user token in the users table (I called mine app_user):

CREATE TABLE "GOOGLE"."APP_USER" 
   ( "USERNAME" VARCHAR2(200 BYTE), 
 "ACCESS_TOKEN" VARCHAR2(400 BYTE), 
 "ID_TOKEN" VARCHAR2(20 BYTE), 
 "ORIGINATING_URL" VARCHAR2(400 BYTE), 
 "CALENDAR_ID" VARCHAR2(50 BYTE)
   )

ACCESS_TOKEN - Where I store the authorization token to submit with requests
ID_TOKEN - A unique token I use - I pass this in the state parameter, so when it returns I can redirect to their last active page (stored in ORIGINATING_URL)
CALENDAR_ID - Each authorized request needs the calendar ID so you know which calendar data to use

As an additional step, you also need to set up an ACL, and a wallet since the requests are over http, to the googleapis domain. To set up the wallet, you need to export the certificate chain and import it into your wallet. 


So, on the page with the button to authorize, I have a button with the action defined by a dynamic action, with 2 true actions:

1. Generate a random token, and return the value into a hidden page item:

declare
    l_id_token app_user.id_token%type;
begin
    l_id_token := dbms_random.string('A', 20);
    update app_user
    set id_token = l_id_token
   where upper(username) = :APP_USER;
:P3_ID_TOKEN := l_id_token;
end;

2. Execute Javascript Code:

$.ajax({
    url: 'GOOGLE.GOOGLE_UTL.GET_AUTHORIZATION_URL',
    data: {
        p_state: escape($v('P3_ID_TOKEN')),
        p_url: escape(window.location.href)
    },
    success: function(data){
        window.location.href=data;
    }
});

And the code of get_authorization_url is:

  --Refer to docs: https://developers.google.com/accounts/docs/OAuth2WebServer
  procedure get_authorization_url(
    p_state in varchar2
  , p_url in varchar2)
  as
    l_url_params varchar2(400);
    l_state app_user.id_token%type;
    l_url app_user.originating_url%type;
  BEGIN
    l_state := utl_url.unescape(p_state);
    l_url := utl_url.unescape(p_url);
    
    update app_user
    set originating_url = l_url
    where id_token = l_State;
    
    l_url_params := 
      'response_type=#RESPONSE_TYPE#&client_id=#CLIENT_ID#&redirect_uri=#REDIRECT_URI#&scope=#SCOPE#&state=#STATE#';
      
    l_url_params := replace(l_url_params, '#RESPONSE_TYPE#', 'code');  
    l_url_params := replace(l_url_params, '#CLIENT_ID#', g_client_id);
    l_url_params := replace(l_url_params, '#REDIRECT_URI#', g_redirect_uri);
    l_url_params := replace(l_url_params, '#SCOPE#', 'https://www.googleapis.com/auth/calendar');
    l_url_params := replace(l_url_params, '#STATE#', p_state);
  
    htp.p(g_auth_url || '?' || l_url_params);
  END get_authorization_url;

Basically, this is a procedure where you could store any user related data before redirecting to google for authorization. It returns the URL that the user needs to go to be authorized, then the javascript re-directs the user to that web address.

Once they grant access (or cancel), it redirects to the callback procedure, which has the code as follows:

 procedure authorization_code_callback(
    code in varchar2 default NULL, 
    error in varchar2 default NULL,
    state in varchar2)
  as
  
    l_token_req utl_http.req;
    l_token_res utl_http.resp;
    
    l_token_req_payload varchar2(4000) := 
      'code=#CODE#&client_id=#CLIENT_ID#&client_secret=#CLIENT_SECRET#&redirect_uri=#REDIRECT_URI#&grant_type=#GRANT_TYPE#';
    
    l_response CLOB;
    l_response_tmp varchar2(1024);
    l_response_json JSON;
    
    l_unescaped_state APP_USER.ID_TOKEN%type;
    l_endpoint_url APP_USER.ORIGINATING_URL%type;
    
  begin
    
    l_unescaped_state := utl_url.unescape(state);
    
    select originating_url into l_endpoint_url
    from app_user
    where id_token = l_unescaped_state;
    
    --code was returned, get the token
    if code is not null then
      l_token_req_payload := replace(l_token_req_payload, '#CODE#', code);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_ID#', g_client_id);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_SECRET#', g_client_secret);
      l_token_req_payload := replace(l_token_req_payload, '#REDIRECT_URI#', g_redirect_uri);
      l_token_req_payload := replace(l_token_req_payload, '#GRANT_TYPE#', g_token_grant_type);

      utl_http.set_wallet(
        path => 'file:/home/oracle/app/oracle/product/11.2.0/dbhome_2/owm/wallets/oracle'
      , password => 'helloworld9');
      
      l_token_req := utl_http.begin_request(
        url => g_token_url
      , method => 'POST');
  
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-length'
      , value => length(l_token_req_payload));
  
      
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-Type'
      , value => 'application/x-www-form-urlencoded');
  
      utl_http.write_text(
        r => l_token_req
      , data => utl_url.escape(l_token_req_payload));
  
      
      l_token_res := utl_http.get_response(
        r => l_token_req);
  
      BEGIN
  
        LOOP
          
          utl_http.read_line(
            r => l_token_res
          , data => l_response_tmp
          , remove_crlf => FALSE);
          l_response := l_response || l_response_tmp;
          
        END LOOP;
      
      EXCEPTION
        WHEN
          UTL_HTTP.END_OF_BODY
            THEN
            
              utl_http.end_response(
                r => l_token_res);
      END;
     
     l_response_json := JSON(l_response);
     update app_user
     set access_token = json_ext.get_string(
                        l_response_json
                      , 'access_token')
                                    
      where id_token = l_unescaped_state;                  
      
    end if;
    
    update app_user
    set id_token = NULL,
    originating_url = NULL
    where id_token = l_unescaped_state;
    
    
     owa_util.redirect_url(
      curl => l_endpoint_url);
    exception
      when others
        then
          dbms_output.put_line(utl_http.get_detailed_sqlerrm);
          raise;
          
  
  end authorization_code_callback;

Obviously, I should be handling if an error comes back. The code that returns here is not the access code, it is a preliminary code that you need to send in another request to get the actual access code. Once the access code is returned, it updates the app_user table to clear the unused info and redirects the user to where they were to begin from (I haven't implemented any way to notify the user that the request was successful or not).

I mentioned earlier on about not implementing getting a refresh token. This would be advised, as it would avoid un-necessary user intervention to get a new code. In the initial redirect, you just need to pass another parameter access_type with a value of offline (default is online), and you will get a refresh token. Then you can easily get a new aaccess token by passing the refresh token accross. For more information, see this: https://developers.google.com/accounts/docs/OAuth2WebServer#offline 

For all API calls, I wrote the following wrapper function:

function authorized_request(
      p_access_token in app_user.access_token%type
    , p_url in varchar2
    , p_method in varchar2 default 'GET'
    , p_content_type in varchar2 default 'application/json'
    , p_payload in varchar2 default NULL
    , p_wallet_path in varchar2 default NULL
    , p_wallet_password in varchar2 default ''
    )
  return CLOB
  AS
    l_req utl_http.req;
    l_res utl_http.resp;
    l_return CLOB;
    l_response_tmp varchar2(1024);
  BEGIN
  
    if p_wallet_path IS NULL THEN
      utl_http.set_Wallet(
        path => g_wallet_path
      , password => g_wallet_password);
    
    else
      utl_http.set_Wallet(
        path => p_wallet_path
      , password => p_wallet_password);
      
    END IF;
    
    
    l_req := utl_http.begin_request(
      url => p_url
    , method => p_method);
    
    utl_http.set_header(
      r => l_req
    , name => 'Content-Type'
    , value => p_content_type);
    
    utl_http.set_header(
      r => l_req
    , name => 'Authorization'
    , value => 'Bearer ' || p_access_token);
    
    if p_payload is not null then
    
      utl_http.set_header(
        r => l_req
      , name => 'Content-Length'
      , value => length(p_payload));  
      
      utl_http.write_text(
        r => l_req
      , data => p_payload);
    
    end if;
    
    l_res := utl_http.get_response(
      r => l_req);
    
    BEGIN
      LOOP
        utl_http.read_line(
          r => l_res
        , data => l_response_tmp
        , remove_crlf => FALSE);
        l_return := l_return || l_response_tmp;
      END LOOP;
    EXCEPTION
      WHEN
        UTL_HTTP.END_OF_BODY
          THEN
            utl_http.end_response(
              r => l_res);
    END;

    return l_return;

  END authorized_request;

Before performing any requests on your google data, you need to know what calendar ID to pass with each request. As mentioned, I set up a column in the app_user table for this purpose. The actual API call this relates to is documented here: https://developers.google.com/google-apps/calendar/v3/reference/calendarList/list

I have implemented with the following function(s):

 --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/calendarList/list
  function get_calendar_list(
    p_username in app_user.username%type)
  return varchar2
  as
    lc_request_url CONSTANT varchar2(200) := 'https://www.googleapis.com/calendar/v3/users/me/calendarList';
    l_calender_list_req utl_http.req;
    l_calender_list_res utl_http.resp;
    l_response CLOB;
    l_response_tmp varchar2(1024);
    
    l_access_token app_user.access_token%type;
  BEGIN

    l_response := authorized_request(
                      p_access_token => get_access_token(p_username)
                    , p_url => lc_request_url
                    );
    
    return l_response;
    
  END get_calendar_list;

So that I can easily query the data with: select column_value from table(pipe_calendar_ids(:APP_USER)), create a pipelined function:  

 function pipe_calendar_ids(p_username in app_user.username%type) return t_varchar2 pipelined
  as
    l_json JSON;
    l_json_temp JSON;
    l_calendars JSON_LIST;
  begin
    
    l_json := JSON(get_calendar_list(p_username));
    
    if l_json.exist('items') then
     
      l_calendars := JSON_LIST(l_json.get('items'));
      for i in 1..l_calendars.COUNT LOOP
    
        l_json_temp := JSON(l_calendars.get(i));
        pipe row(json_ext.get_string(l_json_temp, 'id'));
    
      END LOOP;

    else
    
      pipe row('No calendars found. Try re-authorizing');
      
    end if;

  end pipe_calendar_ids;

Then, I wanted to set up to main functions for a start:

  • Listing Events
  • Adding Events
First the functions:

  --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/events/list
  function list_calendar_events(
    p_username in app_user.username%type)
  return CLOB
  as
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    
  BEGIN
      
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
                  p_access_token => get_access_token(p_username)
                , p_url => l_request_url
                );
      
    return l_response;
    
  END list_calendar_events;

Then, to actually render the returned data, I figured jQuery fullCalendar would be the best bet, so I made a procedure to output the events in its expected format:

procedure fullcalendar_events(
    p_username in app_user.username%type
    --fullCalendar fields. Could use these to improve performance, only fetching
    --events betwween start and end
  , "start" in varchar2 default null
  , "end" in varchar2 default null)
  AS
    l_response CLOB;
    l_return varchar2(32767);
    l_response_json JSON;
    l_events JSON_LIST;
    l_event JSON;
    l_event_title varchar2(255);
    l_start JSON;
    l_start_value varchar2(30);
    l_end JSON;
    l_end_value varchar2(30);
    lc_date_time_name varchar2(20) := 'dateTime';
    lc_date_name varchar2(20) := 'date';
    l_newline varchar2(2) := chr(10);
  BEGIN
  
    l_response := list_calendar_events(p_username);
    
    l_response_json := JSON(l_response);
    
    if l_response_json.exist('items') then
    
      l_events := json_list(l_response_json.get('items'));
      l_return := '[';
      for i in 1..l_Events.COUNT LOOP
        l_event := JSON(l_events.get(i));
        l_event_title := json_ext.get_string(l_event, 'summary');
        -- Some of my events have weird encoding - remove new line and extra space
        -- not sure if pl/json has a good way to handle json encoding - need to investigate
        l_event_title := regexp_replace(l_event_title, '[[:space:]]  *', ' ');
        l_start := json_ext.get_json(l_event, 'start');
        --assume not all day
        l_start_value := json_ext.get_string(l_start, lc_date_time_name);
        if l_start_value is null then
          l_start_value := json_ext.get_string(l_start, lc_date_name);
        end if;
        l_end := json_ext.get_json(l_event, 'end');
        
        l_return := l_return || '{ ';
        l_return := l_return || '"title" : "' || l_event_title || '", ' || l_newline;
        l_return := l_return || '"start" : "' ||l_start_value || '" ' || l_newline;
        l_return := l_return || ' }, ';
        
      END LOOP;
      
      l_return := rtrim(l_return, ', ');
      l_return := l_return || ']';
    end if;
    
    htp.p( l_return );
  
  END fullcalendar_Events;


I then uploaded the necessary files to the Application, and created a HTML region with:

<div id="cal"></div>
<link rel='stylesheet' type='text/css' href='#APP_IMAGES#fullcalendar.css' />
<script type='text/javascript' src='#APP_IMAGES#fullcalendar.min.js'></script>
<script type='text/javascript' src='#APP_IMAGES#gcal.js'></script>
<script type="text/javascript">
$(document).ready(function(){

    region = $('#cal');
        region.fullCalendar({
        editable: false,
        events: "#OWNER#.google_utl.fullcalendar_events?p_username=&APP_USER."
    });

});
</script>

It does take a little while to fetch all the data, so it would be worthwhile filtering the data for the datarange rendered on the current page, since it does a new call with each view change.

I verified it worked by created a couple of bogus events:


The final bit to add was adding an event. I haven't gone all out and implemented time based events, only date ranges.

 procedure add_event(
    p_username in app_user.username%type
  , p_start_date in DATE
  , p_end_date in DATE
  , p_summary in varchar2)
  AS
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events?sendNotifications=false';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    l_payload JSON;
    l_json_temp JSON;
    
  BEGIN
    l_json_temp := JSON;
    l_payload := JSON;
    l_payload.put('summary', p_summary);
    l_json_temp.put('date', to_char(p_start_date, 'yyyy-mm-dd'));
    l_payload.put('start', l_json_temp);
    l_json_temp := JSON;
    l_json_temp.put('date', to_char(p_end_date, 'yyyy-mm-dd'));
    l_payload.put('end', l_json_Temp);
    dbms_output.put_line(l_payload.to_char);
    
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
      p_access_token => get_access_token(p_username)
    , p_url => l_request_url
    , p_method => 'POST'
    , p_payload => l_payload.to_char
    );
    
  END add_event;

Then in the page, I just have the following process:

google_utl.add_event(:APP_USER, :P4_START_DATE, :P4_END_DATE, :P4_SUMMARY);


Well, that's basically my implementation. There is the obvious security issue of only passing in the username, but there are surely ways to get around that. 

Anyway, here is the full source in its glory, of the package. You would obviously need to update the values:

  • g_client_id 
  • g_client_secret 
  • g_redirect_uri 

create or replace PACKAGE "GOOGLE_UTL" 
as

  type t_varchar2 is table of varchar2(50);

  procedure get_authorization_url(
    p_state in varchar2
  , p_url in varchar2);


  procedure authorization_code_callback(
    code in varchar2 default NULL, 
    error in varchar2 default NULL,
    state in varchar2 default NULL); 
    
  function pipe_calendar_ids(p_username in app_user.username%type)
  return t_varchar2 pipelined;
    
  function get_calendar_list(
    p_username in app_user.username%type)
  return varchar2;  
  
  function list_calendar_events(
    p_username in app_user.username%type)
  return CLOB;  
  
  procedure fullcalendar_events(
    p_username in app_user.username%type
  , "start" in varchar2 default null
  , "end" in varchar2 default null);
  
  procedure add_event(
    p_username in app_user.username%type
  , p_start_date in DATE
  , p_end_date in DATE
  , p_summary in varchar2);

end google_utl;
/

create or replace PACKAGE BODY "GOOGLE_UTL" 
as

  g_auth_url varchar2(400) := 'https://accounts.google.com/o/oauth2/auth';
  g_token_url varchar2(400) := 'https://accounts.google.com/o/oauth2/token';
  
  g_wallet_path varchar2(400) := 'file:/home/oracle/app/oracle/product/11.2.0/dbhome_2/owm/wallets/oracle';
  g_wallet_password varchar2(400) := '';
  
  g_redirect_uri varchar2(400) := 'http://example.com:8888/apex/google.google_utl.authorization_code_callback';
  
  g_client_id varchar2(400) := '';
  g_client_secret varchar2(400) := '';
  
  g_token_grant_type varchar2(20) := 'authorization_code';
  
  function authorized_request(
      p_access_token in app_user.access_token%type
    , p_url in varchar2
    , p_method in varchar2 default 'GET'
    , p_content_type in varchar2 default 'application/json'
    , p_payload in varchar2 default NULL
    , p_wallet_path in varchar2 default NULL
    , p_wallet_password in varchar2 default ''
    )
  return CLOB
  AS
    l_req utl_http.req;
    l_res utl_http.resp;
    l_return CLOB;
    l_response_tmp varchar2(1024);
  BEGIN
  
    if p_wallet_path IS NULL THEN
      utl_http.set_Wallet(
        path => g_wallet_path
      , password => g_wallet_password);
    
    else
      utl_http.set_Wallet(
        path => p_wallet_path
      , password => p_wallet_password);
      
    END IF;
    
    
    l_req := utl_http.begin_request(
      url => p_url
    , method => p_method);
    
    utl_http.set_header(
      r => l_req
    , name => 'Content-Type'
    , value => p_content_type);
    
    utl_http.set_header(
      r => l_req
    , name => 'Authorization'
    , value => 'Bearer ' || p_access_token);
    
    if p_payload is not null then
    
      utl_http.set_header(
        r => l_req
      , name => 'Content-Length'
      , value => length(p_payload));  
      
      utl_http.write_text(
        r => l_req
      , data => p_payload);
    
    end if;
    
    l_res := utl_http.get_response(
      r => l_req);
    
    BEGIN
      LOOP
        utl_http.read_line(
          r => l_res
        , data => l_response_tmp
        , remove_crlf => FALSE);
        l_return := l_return || l_response_tmp;
      END LOOP;
    EXCEPTION
      WHEN
        UTL_HTTP.END_OF_BODY
          THEN
            utl_http.end_response(
              r => l_res);
    END;

    return l_return;
  END authorized_request;
  
  FUNCTION get_Access_token(
    p_username in app_user.username%type)
  return app_user.access_token%type
  as
    l_access_token app_user.access_token%type;
  BEGIN
  
    select access_token into l_Access_token
    from app_user
    where username = p_username;
    
    return l_access_token;
  
  end get_Access_token;
  
  function get_calendar_id(
    p_username in app_user.username%type)
  return app_user.calendar_id%type
  AS
    l_calendar_id app_user.calendar_id%type;
  BEGIN
  
    select calendar_id into l_calendar_id
    from app_user
    where username = p_username;
    
    return l_calendar_id;
  
  END get_calendar_id;

  --Refer to docs: https://developers.google.com/accounts/docs/OAuth2WebServer
  procedure get_authorization_url(
    p_state in varchar2
  , p_url in varchar2)
  as
    l_url_params varchar2(400);
    l_state app_user.id_token%type;
    l_url app_user.originating_url%type;
  BEGIN
    l_state := utl_url.unescape(p_state);
    l_url := utl_url.unescape(p_url);
    
    update app_user
    set originating_url = l_url
    where id_token = l_State;
    
    l_url_params := 
      'response_type=#RESPONSE_TYPE#&client_id=#CLIENT_ID#&redirect_uri=#REDIRECT_URI#&scope=#SCOPE#&state=#STATE#';
      
    l_url_params := replace(l_url_params, '#RESPONSE_TYPE#', 'code');  
    l_url_params := replace(l_url_params, '#CLIENT_ID#', g_client_id);
    l_url_params := replace(l_url_params, '#REDIRECT_URI#', g_redirect_uri);
    l_url_params := replace(l_url_params, '#SCOPE#', 'https://www.googleapis.com/auth/calendar');
    l_url_params := replace(l_url_params, '#STATE#', p_state);
  
    htp.p(g_auth_url || '?' || l_url_params);
  END get_authorization_url;
  
  procedure authorization_code_callback(
    code in varchar2 default NULL, 
    error in varchar2 default NULL,
    state in varchar2)
  as
  
    l_token_req utl_http.req;
    l_token_res utl_http.resp;
    
    l_token_req_payload varchar2(4000) := 
      'code=#CODE#&client_id=#CLIENT_ID#&client_secret=#CLIENT_SECRET#&redirect_uri=#REDIRECT_URI#&grant_type=#GRANT_TYPE#';
    
    l_response CLOB;
    l_response_tmp varchar2(1024);
    l_response_json JSON;
    
    l_unescaped_state APP_USER.ID_TOKEN%type;
    l_endpoint_url APP_USER.ORIGINATING_URL%type;
    
  begin
    
    l_unescaped_state := utl_url.unescape(state);
    
    select originating_url into l_endpoint_url
    from app_user
    where id_token = l_unescaped_state;
    
    --code was returned, get the token
    if code is not null then
      l_token_req_payload := replace(l_token_req_payload, '#CODE#', code);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_ID#', g_client_id);
      l_token_req_payload := replace(l_token_req_payload, '#CLIENT_SECRET#', g_client_secret);
      l_token_req_payload := replace(l_token_req_payload, '#REDIRECT_URI#', g_redirect_uri);
      l_token_req_payload := replace(l_token_req_payload, '#GRANT_TYPE#', g_token_grant_type);

      utl_http.set_wallet(
        path => 'file:/home/oracle/app/oracle/product/11.2.0/dbhome_2/owm/wallets/oracle'
      , password => 'helloworld9');
      
      l_token_req := utl_http.begin_request(
        url => g_token_url
      , method => 'POST');
  
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-length'
      , value => length(l_token_req_payload));
  
      
      utl_http.set_header(
        r => l_token_req
      , name => 'Content-Type'
      , value => 'application/x-www-form-urlencoded');
  
      utl_http.write_text(
        r => l_token_req
      , data => utl_url.escape(l_token_req_payload));
  
      
      l_token_res := utl_http.get_response(
        r => l_token_req);
  
      BEGIN
  
        LOOP
          
          utl_http.read_line(
            r => l_token_res
          , data => l_response_tmp
          , remove_crlf => FALSE);
          l_response := l_response || l_response_tmp;
          
        END LOOP;
      
      EXCEPTION
        WHEN
          UTL_HTTP.END_OF_BODY
            THEN
            
              utl_http.end_response(
                r => l_token_res);
      END;
     
     l_response_json := JSON(l_response);
     update app_user
     set access_token = json_ext.get_string(
                        l_response_json
                      , 'access_token')
                                    
      where id_token = l_unescaped_state;                  
      
    end if;
    
    update app_user
    set id_token = NULL,
    originating_url = NULL
    where id_token = l_unescaped_state;
    
    
     owa_util.redirect_url(
      curl => l_endpoint_url);
    exception
      when others
        then
          dbms_output.put_line(utl_http.get_detailed_sqlerrm);
          raise;
          
  
  end authorization_code_callback;
  
  --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/calendarList/list
  function get_calendar_list(
    p_username in app_user.username%type)
  return varchar2
  as
    lc_request_url CONSTANT varchar2(200) := 'https://www.googleapis.com/calendar/v3/users/me/calendarList';
    l_calender_list_req utl_http.req;
    l_calender_list_res utl_http.resp;
    l_response CLOB;
    l_response_tmp varchar2(1024);
    
    l_access_token app_user.access_token%type;
  BEGIN

    l_response := authorized_request(
                      p_access_token => get_access_token(p_username)
                    , p_url => lc_request_url
                    );
    
    return l_response;
    
  END get_calendar_list;
  
  function pipe_calendar_ids(p_username in app_user.username%type) return t_varchar2 pipelined
  as
    l_json JSON;
    l_json_temp JSON;
    l_calendars JSON_LIST;
  begin
    
    l_json := JSON(get_calendar_list(p_username));
    
    if l_json.exist('items') then
     
      l_calendars := JSON_LIST(l_json.get('items'));
      for i in 1..l_calendars.COUNT LOOP
    
        l_json_temp := JSON(l_calendars.get(i));
        pipe row(json_ext.get_string(l_json_temp, 'id'));
    
      END LOOP;

    else
    
      pipe row('No calendars found. Try re-authorizing');
      
    end if;

  end pipe_calendar_ids;
  
  --Refer to: https://developers.google.com/google-apps/calendar/v3/reference/events/list
  function list_calendar_events(
    p_username in app_user.username%type)
  return CLOB
  as
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    
  BEGIN
      
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
                  p_access_token => get_access_token(p_username)
                , p_url => l_request_url
                );
      
    return l_response;
    
  END list_calendar_events;
  
  procedure fullcalendar_events(
    p_username in app_user.username%type
    --fullCalendar fields. Could use these to improve performance, only fetching
    --events betwween start and end
  , "start" in varchar2 default null
  , "end" in varchar2 default null)
  AS
    l_response CLOB;
    l_return varchar2(32767);
    l_response_json JSON;
    l_events JSON_LIST;
    l_event JSON;
    l_event_title varchar2(255);
    l_start JSON;
    l_start_value varchar2(30);
    l_end JSON;
    l_end_value varchar2(30);
    lc_date_time_name varchar2(20) := 'dateTime';
    lc_date_name varchar2(20) := 'date';
    l_newline varchar2(2) := chr(10);
  BEGIN
  
    l_response := list_calendar_events(p_username);
    
    l_response_json := JSON(l_response);
    
    if l_response_json.exist('items') then
    
      l_events := json_list(l_response_json.get('items'));
      l_return := '[';
      for i in 1..l_Events.COUNT LOOP
        l_event := JSON(l_events.get(i));
        l_event_title := json_ext.get_string(l_event, 'summary');
        -- Some of my events have weird encoding - remove new line and extra space
        -- not sure if pl/json has a good way to handle json encoding - need to investigate
        l_event_title := regexp_replace(l_event_title, '[[:space:]]  *', ' ');
        l_start := json_ext.get_json(l_event, 'start');
        --assume not all day
        l_start_value := json_ext.get_string(l_start, lc_date_time_name);
        if l_start_value is null then
          l_start_value := json_ext.get_string(l_start, lc_date_name);
        end if;
        l_end := json_ext.get_json(l_event, 'end');
        
        l_return := l_return || '{ ';
        l_return := l_return || '"title" : "' || l_event_title || '", ' || l_newline;
        l_return := l_return || '"start" : "' ||l_start_value || '" ' || l_newline;
        l_return := l_return || ' }, ';
        
      END LOOP;
      
      l_return := rtrim(l_return, ', ');
      l_return := l_return || ']';
    end if;
    
    htp.p( l_return );
  
  END fullcalendar_Events;
  
  procedure add_event(
    p_username in app_user.username%type
  , p_start_date in DATE
  , p_end_date in DATE
  , p_summary in varchar2)
  AS
    l_request_url varchar2(200) := 'https://www.googleapis.com/calendar/v3/calendars/#ID#/events?sendNotifications=false';
    l_calendar_id app_user.calendar_id%type;
    l_response CLOB;
    l_payload JSON;
    l_json_temp JSON;
    
  BEGIN
    l_json_temp := JSON;
    l_payload := JSON;
    l_payload.put('summary', p_summary);
    l_json_temp.put('date', to_char(p_start_date, 'yyyy-mm-dd'));
    l_payload.put('start', l_json_temp);
    l_json_temp := JSON;
    l_json_temp.put('date', to_char(p_end_date, 'yyyy-mm-dd'));
    l_payload.put('end', l_json_Temp);
    dbms_output.put_line(l_payload.to_char);
    
    l_calendar_id := get_calendar_id(p_username);  
    l_request_url := replace(l_request_url, '#ID#', l_calendar_id);
    
    l_response := authorized_request(
      p_access_token => get_access_token(p_username)
    , p_url => l_request_url
    , p_method => 'POST'
    , p_payload => l_payload.to_char
    );
    
  END add_event;

end google_utl;
/


3 comments:

  1. I need to know if it is possible that you will develop an article in your blog explaining how to enter an application in apex with a google account, I would be extremely useful and I think for a lot too, because in internet no information how. thank you very much.

    ReplyDelete
    Replies
    1. Hi Daniel, Do you mean how to authenticate? I haven't done anything with that, however there is a (non-free) plugin: http://apex-plugin.com/oracle-apex-plugins/authentication-plugin/google-authentication-plugin_340.html

      Delete
    2. I had seen this plugin, but the price is very high, thanks anyway.

      Delete