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.
- 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:
If you want to try out my sample application with out downloading it…
- Sign up for an account on jott.com
- Configure a custom link
- Link Name: APEX
- Setup UTL: http://apex.oracle.com/pls/otn/tyler.jott.register
- Link URL: http://apex.oracle.com/pls/otn/tyler.jott.message
- When you save the link, you’ll be redirected to my APEX application
- 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.
- Call Jott and Jott a message to APEX
- Access your message at the following URL:
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
p_ip in varchar2)
— check that the IP of the host calling this package is in the range 220.127.116.11-204
if regexp_instr(p_ip,’^69\.12\.107\.(200|201|202|203|204)$’ ) = 1 then
— Replace characters that could be used for SQL Injection and Cross Site Scripting with spaces
return regexp_replace(p_input,'[;|”|-|<|>|/]’,’ ‘);
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)
if not check_ip(owa_util.get_cgi_env(‘REMOTE_ADDR’)) then
htp.p(‘This package only accepts posts from jott.com’);
for c1 in (select local_username from jott_user_mapping where jott_userkey = UserKey)
l_username := c1.local_username;
if l_username is null then
htp.p(‘Error! This Jott account is not registered yet.’);
l_message := sanitize(message);
insert into jotts(username,UserKey,Message,listen,CreationDate,creationdateutc,audio,confidence)
‘APEX Link: ‘||g_url||g_app_number||’:1′);
UserKey in varchar2 default null,
userid in varchar2 default null,
endpointid in varchar2 default null,
postbackurl in varchar2 default null)
if g_app_number = 12345 then
htp.p(‘Error! Application Number (g_app_number) must be set in the JOTT package.’);
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.