Home > Databases/Embedded Sql > Using Oracle to Place a Google Voice Call

Using Oracle to Place a Google Voice Call

Google Voice PL/SQL Package:

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;

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: