Project done in partnership with Steve Bealer.
Purpose: We needed a way to make a phone call from our Oracle database whenever there was a failure. We did not have access to a PBX to place the call. However, Google voice offers a service to create free phone calls through VOIP. After some investigation to figure out the process used to make a Google voice call we built a PL/SQL package to utilize this feature.
Programming Language: PL/SQL
Database: Oracle
Google Voice Implementation
Table
Package uses a table so that code is separated from the data.
| Column | Data Type | Description | Values |
| USERID | VARCHAR2(100) | google voice userid | |
| PWD | VARCHAR2(100) | google voice password | |
| SUBSCRIBER_NUMBER | VARCHAR2(10) | google voice phone number | |
| LOGIN_URL | VARCHAR2(100) | google voice login url | https://www.google.com/accounts/ServiceLogin |
| AUTH_URL | VARCHAR2(100) | google voice authentication url | https://www.google.com/accounts/ServiceLoginAuth |
| HOME_PAGE_URL | VARCHAR2(100) | google voice home page | https://www.google.com/voice |
| CONTINUE_URL | VARCHAR2(100) | needed to bypass cookie check | https://www.google.com/voice/account/signin |
| CALL_URL | VARCHAR2(100) | url to post call to | https://www.google.com/voice/call/connect |
-- Package Declaration
CREATE OR REPLACE PACKAGE GOOGLE_VOICE IS
-- Author : RLOBBAN
-- Created : 11/24/2009 11:03:05 AM
-- Purpose : To place calls through Google Voice
/*Usage Example
declare
call_success boolean;
begin
call_success := Google_Voice.Place_Call('53058984778','5308986689');
end;
*/
FUNCTION PLACE_CALL
(
VARNUMBERTOCALL IN VARCHAR2
,VARFORWARDINGNUMBER IN VARCHAR2
) RETURN BOOLEAN;
END GOOGLE_VOICE;
-- Package Body
CREATE OR REPLACE PACKAGE BODY GOOGLE_VOICE IS
------------------------------------------------------------------------------------
/*
Function: Get_Galx
Params: Login Url of google accounts
Returns: galx header value as varchar2
Description: GALX value is in the header of the page. This function parses through
the header until it comes to one with GALX in the value. It then regexp out the value
*/
FUNCTION GET_GALX(VARLOGINURL IN VARCHAR2) RETURN VARCHAR2 AS
LOGIN_URL VARCHAR2(500) := VARLOGINURL;
GALX_REQUEST UTL_HTTP.REQ;
GALX_RESPONSE UTL_HTTP.RESP;
HEADER_NAME VARCHAR2(100);
HEADER_VALUE VARCHAR2(100);
GALX VARCHAR2(30);
BEGIN
GALX_REQUEST := UTL_HTTP.BEGIN_REQUEST(LOGIN_URL);
GALX_RESPONSE := UTL_HTTP.GET_RESPONSE(R => GALX_REQUEST);
-- Loop through the headers until find galx and regex that bad boy out
FOR I IN 1 .. UTL_HTTP.GET_HEADER_COUNT(GALX_RESPONSE)
LOOP
UTL_HTTP.GET_HEADER(GALX_RESPONSE, I, HEADER_NAME, HEADER_VALUE);
IF (INSTR(HEADER_VALUE, 'GALX') > 0)
THEN
GALX := REPLACE(REGEXP_SUBSTR(HEADER_VALUE, '[^=]+;'), ';', '');
END IF;
END LOOP;
UTL_HTTP.END_RESPONSE(R => GALX_RESPONSE);
RETURN GALX;
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(R => GALX_RESPONSE);
RETURN SQLERRM;
END;
------------------------------------------------------------------------------------
/*
Function: Get_Rnr_Se
Params: accounts Url
Returns: value in _rnr_se
Description: the rnr_se value is needed when making phone calls.
The value is stored in a hidden textbox on the accounts page
*/
FUNCTION GET_RNR_SE(VARACCOUNTSURL IN VARCHAR2) RETURN VARCHAR2 AS
RNR_SE VARCHAR2(100);
ACCOUNTSURL VARCHAR2(500) := VARACCOUNTSURL;
RNR_REQUEST UTL_HTTP.REQ;
RNR_RESPONSE UTL_HTTP.RESP;
RESPONSE_BODY CLOB;
BEGIN
RNR_REQUEST := UTL_HTTP.BEGIN_REQUEST(ACCOUNTSURL);
RNR_RESPONSE := UTL_HTTP.GET_RESPONSE(R => RNR_REQUEST);
UTL_HTTP.READ_TEXT(R => RNR_RESPONSE, DATA => RESPONSE_BODY);
UTL_HTTP.END_RESPONSE(R => RNR_RESPONSE);
RNR_SE := REGEXP_REPLACE(RESPONSE_BODY, '.+(rnr_se" type="hidden"
value=")(.*?)".+$', '\2', 1, 1, 'n');
RETURN RNR_SE;
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(R => RNR_RESPONSE);
RETURN SQLERRM;
END;
------------------------------------------------------------------------------------
/*
Function: Login_To_Google_Voice
Params: UserID
Password
Authentication URL
Continue Url
GALX
Description: Procedure logs in to google voice service and navigates through the appropriate cookie checks
and maintains cookies for use in making calls
*/
PROCEDURE LOGIN_TO_GOOGLE_VOICE
(
VARUSERID IN VARCHAR2
,VARPWD IN VARCHAR2
,VARAUTHURL IN VARCHAR2
,VARCONTINUEURL IN VARCHAR2
,VARGALX IN VARCHAR2
) IS
AUTH_URL VARCHAR2(400) := VARAUTHURL;
LOGIN_PARAMS VARCHAR2(400) := UTL_URL.ESCAPE('Email=' || VARUSERID ||
'&Passwd=' || VARPWD || '&continue=' || VARCONTINUEURL || '&GALX=' || VARGALX);
AUTH_REQUEST UTL_HTTP.REQ;
AUTH_RESPONSE UTL_HTTP.RESP;
RESPONSE_BODY CLOB;
BEGIN
AUTH_REQUEST := UTL_HTTP.BEGIN_REQUEST(AUTH_URL, 'POST', 'HTTP/1.1');
UTL_HTTP.SET_HEADER(AUTH_REQUEST, 'Content-Type', 'application/x-www-form-
urlencoded');
UTL_HTTP.SET_HEADER(AUTH_REQUEST, 'Content-Length', LENGTH(LOGIN_PARAMS));
UTL_HTTP.WRITE_TEXT(AUTH_REQUEST, LOGIN_PARAMS);
AUTH_RESPONSE := UTL_HTTP.GET_RESPONSE(R => AUTH_REQUEST);
UTL_HTTP.READ_TEXT(R => AUTH_RESPONSE, DATA => RESPONSE_BODY);
AUTH_REQUEST :=
UTL_HTTP.BEGIN_REQUEST('https://www.google.com/accounts/CheckCookie?continue=https%3A%2F%2Fwww.google.com%2Fvoice%2Faccount%2Fsignin&chtml=LoginDoneHtml');
AUTH_RESPONSE := UTL_HTTP.GET_RESPONSE(R => AUTH_REQUEST);
UTL_HTTP.READ_TEXT(R => AUTH_RESPONSE, DATA => RESPONSE_BODY);
UTL_HTTP.END_RESPONSE(R => AUTH_RESPONSE);
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(R => AUTH_RESPONSE);
END LOGIN_TO_GOOGLE_VOICE;
------------------------------------------------------------------------------------
/*
Function: Make_Call
Params: Outgoing Number
Forwarding Number
Subscriber Number
rnr
phoneType
Url
Description: Self explanatory
Does a post to the appropriate url with the parameters needed to make a call.
*/
FUNCTION MAKE_CALL
(
OUTGOINGNUMBER IN VARCHAR2
,FORWARDINGNUMBER IN VARCHAR2
,SUBSCRIBERNUMBER IN VARCHAR2
,RNR IN VARCHAR2
,PHONETYPE IN VARCHAR2
,URL IN VARCHAR2
) RETURN BOOLEAN IS
RESULT BOOLEAN := FALSE;
CALL_URL VARCHAR2(400) := URL;
CALL_PARAMS VARCHAR2(400) := 'outgoingNumber=' || OUTGOINGNUMBER ||
'&forwardingNumber=' || FORWARDINGNUMBER || '&subscriberNumber=' ||
SUBSCRIBERNUMBER || '&remember=0' || '&phoneType=' || PHONETYPE ||
'&_rnr_se=' || RNR;
CALL_REQUEST UTL_HTTP.REQ;
CALL_RESPONSE UTL_HTTP.RESP;
RESPONSE_BODY CLOB;
BEGIN
CALL_REQUEST := UTL_HTTP.BEGIN_REQUEST(CALL_URL, 'POST', 'HTTP/1.1');
UTL_HTTP.SET_HEADER(CALL_REQUEST, 'Content-Type', 'application/x-www-form-
urlencoded');
UTL_HTTP.SET_HEADER(CALL_REQUEST, 'Content-Length', LENGTH(CALL_PARAMS));
UTL_HTTP.WRITE_TEXT(CALL_REQUEST, CALL_PARAMS);
CALL_RESPONSE := UTL_HTTP.GET_RESPONSE(R => CALL_REQUEST);
UTL_HTTP.READ_TEXT(R => CALL_RESPONSE, DATA => RESPONSE_BODY);
UTL_HTTP.END_RESPONSE(R => CALL_RESPONSE);
IF (INSTR(RESPONSE_BODY, 'true') > 0)
THEN
RESULT := TRUE;
END IF;
RETURN RESULT;
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(R => CALL_RESPONSE);
RETURN FALSE;
END;
------------------------------------------------------------------------------------
/*
Function: Place_Call
Params: Number To Call
Forwarding Number
Returns: true if phone call successful, false otherwise
Description: Self explanatory
*/
FUNCTION PLACE_CALL
(
VARNUMBERTOCALL IN VARCHAR2
,VARFORWARDINGNUMBER IN VARCHAR2
) RETURN BOOLEAN IS
USERID VARCHAR2(100);
PWD VARCHAR2(100);
OUTGOING_NUMBER VARCHAR2(11) := VARNUMBERTOCALL;
FORWARDING_NUMBER VARCHAR2(11) := VARFORWARDINGNUMBER;
SUBSCRIBER_NUMBER VARCHAR2(10);
LOGIN_URL VARCHAR2(100);
AUTH_URL VARCHAR2(100);
HOME_PAGE_URL VARCHAR2(100);
CONTINUE_URL VARCHAR2(100);
CALL_URL VARCHAR2(1000);
GALX VARCHAR2(500);
RNR VARCHAR2(100);
SUCCESS BOOLEAN;
BEGIN
SELECT *
INTO USERID, PWD, SUBSCRIBER_NUMBER, LOGIN_URL, AUTH_URL, HOME_PAGE_URL,
CONTINUE_URL, CALL_URL
FROM RDSPRD.GOOGLE_VOICE_DTL
WHERE USERID = 'data@csuchico.edu';
-- get galx from login page
GALX := GET_GALX(LOGIN_URL);
-- log in to gvoices
LOGIN_TO_GOOGLE_VOICE(USERID, PWD, AUTH_URL, CONTINUE_URL, GALX);
-- get rnr from accounts page
RNR := GET_RNR_SE(HOME_PAGE_URL);
-- self explanatory
SUCCESS := MAKE_CALL(OUTGOING_NUMBER, FORWARDING_NUMBER, SUBSCRIBER_NUMBER,
RNR, '2', CALL_URL);
RETURN SUCCESS;
END PLACE_CALL;
------------------------------------------------------------------------------------
BEGIN
UTL_HTTP.SET_WALLET(PATH => 'file:/opt/oracle/wallet/https');
END GOOGLE_VOICE;
No comments:
Post a Comment