Speech to Text to APEX

One of my favorite online services is Jott. Think of Jott as an online list manager (task or todo lists) that you can call from your phone. Jott does an amazing job of recognizing what you say, then translating that to text and delivering it as an email and text message as well as storing it on jott.com. I use it all the time in the car when I have an idea or need to remember something. My wife and I have even used it occasionally for jotting a short grocery list to each other (don’t worry, she knew she was marrying a geek pretty early in the relationship). I was playing around with some of the partner applications called “Jott Links” such as 30 Boxes, Google Calendar, and Twitter. I also noticed a custom link, allowing developers to create their own Jott partner applications (more info on the Jott Developer page). Hmmm, I’m a developer…I also think voice input is a VERY cool concept…

So, with a little help from Matt at Jott, I put together a sample of integrating Jott with APEX. This allows you to input data into a database table while on the go with nothing but a phone.

Overview

• Sign up for an account on Jott. Note: don’t use your bank password as they are storing your password in clear text, not a hash of it, which they will hopefully change soon.
• Create a new Custom Jott Link called APEX. The 2 URLs you will use are to PL/SQL procedures that are exposed on the Internet.
• You’ll be redirected to an APEX application where you can enter any username. When you hit register, this username will be mapped to your userkey from jott. This just allows you to identify who the jotts belong to.
• Now, when you call Jott, you can perform the following:
  • (Jott) Who would like to Jott?
  • (You) APEX
  • (Jott) Beep (and some other stuff)
  • (You) Remember to blog about integration between Jott and APEX
• Jott will translate your voice message to text, then post it back to a PL/SQL package on your server.

Here are some screenshots from this process:

Demo

If you want to try out my sample application with out downloading it…

1. Sign up for an account on jott.com
2. Configure a custom link
   1. Link Name: APEX
   2. Setup UTL: http://apex.oracle.com/pls/otn/tyler.jott.register
   3. Link URL: http://apex.oracle.com/pls/otn/tyler.jott.message
3. When you save the link, you’ll be redirected to my APEX application
4. Enter any username, just remember it, as you’ll need it later to see your jotts. This application uses open door authentication, so you can login with any username and no password. The key here is that your jotts will show up under your username.
5. Call Jott and Jott a message to APEX
6. Access your message at the following URL:
   http://apex.oracle.com/pls/otn/f?p=32041:1

Code

The following is just the package body, not the spec or the DDL, which are included in the APEX application as “supporting objects” (download link at bottom of this post):

create or replace package body jott as
function check_ip(
p_ip in varchar2)
return boolean
is
begin
— check that the IP of the host calling this package is in the range 69.12.107.200-204
if regexp_instr(p_ip,’^69\.12\.107\.(200|201|202|203|204)$’ ) = 1 then
return true;
else
return false;
end if;
end check_ip;

function sanitize(
p_input varchar2)
return varchar2
is
begin
— Replace characters that could be used for SQL Injection and Cross Site Scripting with spaces
return regexp_replace(p_input,'[;|”|-|<|>|/]’,’ ‘);
end sanitize;

procedure message(
UserKey in varchar2 default null,
Message in varchar2 default null,
listen in varchar2 default null,
CreationDate in varchar2 default null,
creationdateutc in varchar2 default null,
confidence in varchar2 default null,
audio in varchar2 default null)
is
l_username varchar2(255);
l_message varchar2(1000);
begin
if not check_ip(owa_util.get_cgi_env(‘REMOTE_ADDR’)) then
htp.p(‘This package only accepts posts from jott.com’);
return;
end if;

for c1 in (select local_username from jott_user_mapping where jott_userkey = UserKey)
loop
l_username := c1.local_username;
end loop;

if l_username is null then
htp.p(‘Error! This Jott account is not registered yet.’);
else
l_message := sanitize(message);

insert into jotts(username,UserKey,Message,listen,CreationDate,creationdateutc,audio,confidence)
values
(l_username,UserKey,l_message,listen,CreationDate,creationdateutc,audio,confidence);
htp.p(l_message||chr(13)||
‘APEX Link: ‘||g_url||g_app_number||’:1′);
end if;

end message;

procedure register(
UserKey in varchar2 default null,
userid in varchar2 default null,
endpointid in varchar2 default null,
postbackurl in varchar2 default null)
is
l_new_id varchar2(32);
begin
if g_app_number = 12345 then
htp.p(‘Error! Application Number (g_app_number) must be set in the JOTT package.’);
return;
end if;

delete from jott_temp where created_on < sysdate - interval '1' hour; insert into jott_temp (jott_userkey,jott_userid,jott_postbackurl) values (UserKey,userid,postbackurl) returning id into l_new_id; owa_util.status_line(301, null,FALSE); owa_util.redirect_url('f?p='||g_app_number||':200:::::P200_ID:'||l_new_id, TRUE); end register; end jott; /[/sourcecode] Make sure you edit the package spec after installation and change the g_app_number and g_url globals. You download either the APEX Application (which includes all of the DDL) or just the table and package DDL here.