Google Reports API (Parse Data)

3) Parse the Data.
In order to keep the process fluid, we eliminated the need to load an actual 'table' by using pipe lined funtions.  Essentialy, my function will take in a .csv style formatted clob, and 'pipe' rows back.  Note: You do have to create user defined types in order make this work, as the table is returning a 'row' of that type.  You can read more about pipe lined functionality here .


FUNCTION EMAIL_PARSE RETURN GOOGLE_APPS_EMAIL_TABLE
              PIPELINED IS
              /***
              ** Function: GOOGLE_EMAIL_PARSE
              ** Params:  none
              ** Returns: Table of GOOGLE_APPS_EMAIL_ROW type
              ** Description: Pipelined function fetchs the .csv type clob from Google Reporting API and parses
              **                  it out into a table using regular expressions.
              ***/
              ROW_STRING VARCHAR2(4000);
              V_MESSAGE  VARCHAR2(4000);
              --Our current place in the clob
              CLOB_LOCATION NUMBER := 1;
              --The clob we want to parse
              PARSE_CLOB CLOB := /* your clob here*/;
BEGIN

              --Loop to parse out fields for every single row.
              FOR I IN 1 .. REGEXP_COUNT(PARSE_CLOB, '^.*?', MODIFIER => 'm') - 1 --because we eliminate header row, need to decrement count
              LOOP
                            --Get next 'row' of data
                            ROW_STRING := REGEXP_SUBSTR(PARSE_CLOB, '.*$?', CLOB_LOCATION);
                            --Increment our location appropriately
                            CLOB_LOCATION := CLOB_LOCATION + LENGTH(ROW_STRING) + 1;

                            --Replacing any , inside fields with - so our regexp can recognize it as a field.
                            ROW_STRING := REGEXP_REPLACE(ROW_STRING, '(")(.*?)(,)(.*?)(")', '\2-\4');
                            ROW_STRING := REPLACE(ROW_STRING, ',', ' ,');
                            --Returning our rows.
                            PIPE ROW(D/*your row type */(TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 1))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 2))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 3))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 4))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 5))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 6)) /*usage in bytes*/
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 7))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 8))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 9))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 10))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 11)) /*last_web_mail_date */
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 12))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 13))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 14))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 15))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 16))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 17))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 18))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 19))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 20))
                                                                ,TRIM(REGEXP_SUBSTR(ROW_STRING, '[^,]+', OCCURRENCE => 21))));
                            --  END IF;
              END LOOP;
              RETURN;

EXCEPTION
              --when we don't need more data, do nothing.
              WHEN NO_DATA_NEEDED THEN
                            NULL;
              WHEN OTHERS THEN
                            V_MESSAGE := 'There was an error in parsing the clob EMAIL_PARSE: ' || SQLERRM;
                            --Email admins here
END;

Google Reports API (Fetch Data)

2)Get data.
In this case, we are querying against the accounts service, and it returns data in a .csv type format.  We will then re-format this according to our own needs (pipelined function.)

FUNCTION FETCH_EMAILS(AUTH_TOKEN_IN IN VARCHAR2) RETURN CLOB IS
              /***
              ** Function: FETCH_EMAILS
              ** Params:  AUTH_TOKEN_IN = Authorization token used for API call.
              ** Returns: A CLOB formatted in the fashion of a .csv.
              ** Description: Uses an XML POST request to Google reports API to get back all emails (and
              **                  assorted settings) in .csv format.
              ***/
              --declare variables
              L_REQUEST  UTL_HTTP.REQ;
              L_RESPONSE UTL_HTTP.RESP;

              V_REQUEST_BODY VARCHAR2(4000);
              V_RESP_CLOB    CLOB;
              STRING_CHECK   VARCHAR2(32767);
              V_MESSAGE      VARCHAR2(4000);
              PAGE_STRING    VARCHAR2(4000);
              PAGE_NUMBER    NUMBER;

BEGIN
              IF (SUBSTR(AUTH_TOKEN_IN, 1, 5) != 'Error')
              THEN
                            --Open CLOB
                            DBMS_LOB.CREATETEMPORARY(V_RESP_CLOB, TRUE);
                            DBMS_LOB.OPEN(V_RESP_CLOB, DBMS_LOB.LOB_READWRITE);
                            --Get the number of pages needed
                            PAGE_NUMBER := ROUND(GET_ACCOUNT_NUM(AUTH_TOKEN_IN) / 10000) + 1;
           
                            FOR I IN 1 .. PAGE_NUMBER
                            LOOP
                                          --Make a string for the current page of the request.
                                          PAGE_STRING := '<page>' || I || '</page>';
                         
                                          --Format the request body into proper rest protocol.
                                          V_REQUEST_BODY := q'|<?xml version="1.0" encoding="UTF-8"?>
                                                        <rest xmlns="google:accounts:rest:protocol"
                                                        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                                                        <type>Report</type>
                                                        <token>|' || AUTH_TOKEN_IN ||
                                                            '</token>
                                                        <domain>' || V_DOMAIN ||
                                                            '</domain>
                                                        <date>' || V_DATE || '</date>' || PAGE_STRING ||
                                                            '<reportType>daily</reportType>
                                                        <reportName>accounts</reportName>
                                                        </rest>';
                                          L_REQUEST := UTL_HTTP.BEGIN_REQUEST('https://www.google.com/hosted/services/v1.0/reports/ReportingData'
                                                                             ,'POST'
                                                                             ,'HTTP/1.1');
                         
                                          -- request headers setup
                                          UTL_HTTP.SET_HEADER(L_REQUEST, 'Content-Type', 'application/x-www-form-urlencoded');
                         
                                          UTL_HTTP.SET_HEADER(L_REQUEST, 'Content-Length', LENGTH(V_REQUEST_BODY));
                                          -- end of request headers setup
                         
                                          -- write out the request body
                                          UTL_HTTP.WRITE_TEXT(L_REQUEST, V_REQUEST_BODY);
                         
                                          -- request/get the response
                                          L_RESPONSE := UTL_HTTP.GET_RESPONSE(R => L_REQUEST);
                         
                                          --Loop to read response until we reach 'end of body', in which case we are done.
                                          BEGIN
                                                        LOOP
                                                                      UTL_HTTP.READ_TEXT(L_RESPONSE, STRING_CHECK);
                                                                      DBMS_LOB.WRITEAPPEND(V_RESP_CLOB, LENGTH(STRING_CHECK), STRING_CHECK);
                                                        END LOOP;
                                       
                                                        UTL_HTTP.END_RESPONSE(R => L_RESPONSE);
                                       
                                          EXCEPTION
                                                        WHEN UTL_HTTP.END_OF_BODY THEN
                                                                      NULL;
                                          END;
                                          UTL_HTTP.END_RESPONSE(R => L_RESPONSE);
                            END LOOP;
              ELSE
                            V_MESSAGE := 'There was an error in fetching authentication token: ' || AUTH_TOKEN_IN;
                            RDSPRD.RDS_MAIL.MAIL(ADMIN_EMAIL, ADMIN_EMAIL, 'ERROR: Google Report Tools@' || ADMIN_SERVER, V_MESSAGE);
              END IF;

              --Need to replace all the header rows (1 per page) with a blank so that pipelined function doesn't output them.
              RETURN REGEXP_REPLACE(V_RESP_CLOB, '^date.*' || CHR(10), '', MODIFIER => 'm');

END;

Google Reports API (Authentication)

At CSU, Chico the student email service is hosted through Google, so we can use all kinds of cool Google APIs to pull down and analyze data. Since we use Oracle databases, I found a way to query and analyze Google Reporting Services in a PL/SQL package.

1) Get an authentication token
Google will provide 3 different kinds of auth tokens, but with a little regexp adjustment, I've provided a method to pull out either the SID or AUTH types.  Simply pass either of those strings into the TOKEN_TYPE parameter.

FUNCTION FETCH_POST_TOKEN
              (
                            V_AUTH1    VARCHAR2
                           ,V_AUTH2    VARCHAR2
                           ,V_SERVICE  VARCHAR2
                           ,TOKEN_TYPE VARCHAR2
              ) RETURN VARCHAR2 IS
                            PRAGMA AUTONOMOUS_TRANSACTION;
                            /***
                            ** Function: FETCH_POST_TOKEN
                            ** Params:  V_AUTH1, V_AUTH2, V_SERVICE = credentials used to obtain auth token, TOKEN_TYPE =
                            **              determines which regexp to use in order to get right token.
                            ** Returns: auth token of choice as a varchar2
                            ** Description: Sends an http request to the Google reporting API in order to obtain an auth token.
                            **                  reads the response back and returns the appropriate auth token.
                            ***/
                            --declare variables
                            L_REQUEST  UTL_HTTP.REQ;
                            L_RESPONSE UTL_HTTP.RESP;
                            L_PARAMS   VARCHAR2(255);
                            AUTH_TOKEN VARCHAR2(2000);
                            V_RESP_BOD VARCHAR2(1000);
           
              BEGIN
                            -- set up the request body with our credentials
                            L_PARAMS := 'accountType=HOSTED&Email=' || V_AUTH1 || '&' || 'Passwd=' || V_AUTH2 || '&' || 'service=' || V_SERVICE || '&' ||
                                        'source=/*your source here*/';
                            L_REQUEST := UTL_HTTP.BEGIN_REQUEST('https://www.google.com/accounts/ClientLogin', 'POST', 'HTTP/1.1');
           
                            -- request headers setup
                            UTL_HTTP.SET_HEADER(L_REQUEST, 'Content-Type', 'application/x-www-form-urlencoded');
           
                            UTL_HTTP.SET_HEADER(L_REQUEST, 'Content-Length', LENGTH(L_PARAMS));
                            -- end of request headers setup
           
                            -- write out the request body
                            UTL_HTTP.WRITE_TEXT(L_REQUEST, L_PARAMS);
           
                            -- request/get the response
                            L_RESPONSE := UTL_HTTP.GET_RESPONSE(R => L_REQUEST);
                            UTL_HTTP.READ_TEXT(R => L_RESPONSE, DATA => V_RESP_BOD);
                            IF L_RESPONSE.STATUS_CODE = 200
                            THEN
                                          --Response 200: 'OK'
                                          --gather entire response (a bunch of crap plus the much needed "auth token" (aka an encrypted string)
                                          --UTL_HTTP.READ_TEXT(R => L_RESPONSE, DATA => V_RESP_BOD);
                         
                                          IF (TOKEN_TYPE = 'SID')
                                          THEN
                                                        --Assumes that sid= will be first 4 characters of the string.
                                                        AUTH_TOKEN := REGEXP_SUBSTR(V_RESP_BOD, '.*$?', 5);
                                          END IF;
                                          IF (TOKEN_TYPE = 'AUTH')
                                          THEN
                                                        --Use some fancy REGEX to parse the response, gathering the auth token into our variable
                                                        AUTH_TOKEN := SUBSTR(REGEXP_SUBSTR(V_RESP_BOD, 'Auth=([[:print:]]+)/?'), 6);
                                          END IF;
                                          --Insert into our auth table to store token. Prevents uneccesary auth calls.
                                          --Here we insert into a table, as each auth_token will last for 24 hours, preventing
                                          --redundant auth calls.
                            ELSE
                                          -- otherwise return 'error', something happened!
                                          AUTH_TOKEN := SUBSTR(V_RESP_BOD, 1, 1500);
                                          --DBMS_OUTPUT.PUT_LINE(AUTH_TOKEN);
                            END IF;
                            -- end the response
                            UTL_HTTP.END_RESPONSE(R => L_RESPONSE);
           
                            COMMIT;
                            -- return the auth token, otherwise 'error' (as determined above)
                            RETURN(AUTH_TOKEN);
           
              END;

Oracle Minus Functionality

At work we often run into user requests which require us to update a long-standing system, while still preserving the legacy data in its original state. Like any good developer, we go through a significant testing phase to ensure that the new tables still match the old tables. Inevitably this involves using row counts and minuses to determine validity.

The provided 'minus' function works like so:
select x from tab1
minus
select y from tab2
This would show all the rows in tab1 that aren't in tab2, in other words, a one-way minus. Rather than copy-pasting yet again, I wrote a PL/SQL procedure that can easily be re-used.

CREATE OR REPLACE PROCEDURE MINUS_BOTH

(

              QUERY1  VARCHAR2

             ,QUERY2  VARCHAR2

             ,RESULT1 IN OUT SYS_REFCURSOR

             ,RESULT2 IN OUT SYS_REFCURSOR

) IS

     /***

     ** Procedure: MINUS_BOTH

     ** Params:  QUERY1 - The first query to be used, QUERY2 - Second query to be used, RESULT1 - Cursor containing rows of first

     **              minus, RESULT2 - Cursor containing rows of second minus. 

     ** Returns: Two sys_refcursors which contain the results of their respective minuses

     ** Description: Takes in two sql queries, and minuses them against each other. The first result is QUERY1 minus QUERY2, the second

     **                  is the opposite. 

     ***/

              V_SQL CLOB;

BEGIN

              V_SQL := QUERY1 || ' minus ' || QUERY2;

              OPEN RESULT1 FOR V_SQL;

              V_SQL := QUERY2 || ' minus ' || QUERY1;

              OPEN RESULT2 FOR V_SQL;

EXCEPTION

              WHEN OTHERS THEN

                            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END MINUS_BOTH;

------------------------

It's pretty straightforward to use, just pass in your sql queries, and watch Oracle magic happen. I plan to add some exception handling in order to make the procedure more robust, and will update the post accordingly. 

Enjoy!