Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Speech to Text to APEX

Posted by Tyler Muth on May 11, 2008

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:

jott_screen1

jott_screen2

apex_screen1

apex_screen2

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.

20 Responses to “Speech to Text to APEX”

  1. Tony said

    Cool post, yet another example of the limitless possibilities offered through Apex. I could see some cool implementation of this that would really take my users by surprise. Thanks for the info.

    Tony

  2. Tyler, that’s really cool! To bad that’s not available here in Europe 😦

    Patrick

  3. […] of the speech recognition into the table of an Oracle APEX application. Read all the details on his Speech to text to Oracle APEX […]

  4. Tyler,

    I went through your instructions and it kept failing at Step 3. when I hit the “save” button it kept coming up with a 404 Error. I actually logged off Jott and back on again and there were multiple Custom Links configured so deleted all but one. When I edited the one remaining link it correctly went to your application.

    Now I can Jott away with glee 🙂

    Yet another marvelous example of using APEX in creative ways.

    Thanks,
    David

  5. Tyler Muth said

    Make sure you follow instructions carefully. A bunch of people have tried to skip the jott step and register null accounts (yeah, I’m talking to you Beowolf, Dummy, Dummy2, Foo, Bar, Blah, and Hello). I added some validation to the app to prevent this in the future.

  6. Michael Roessler said

    Excellent information. Thank you for this!
    This opens up a lot of ideas, especially if in future Jott could post into distinct table columns or APEX form fields, ie:
    JOTT: “What do you want to Jott?”
    Me: Buy stock
    JOTT: Which stock?
    Me: Oracle
    JOTT: How many?
    Me: 10,000
    JOTT: Limit price?
    Me: 21.75
    JOTT: Got it!

  7. ittichai said

    I agree that there is no limit here. I tried to configured it by following the instructions, and it works well without any issue. Thanks Tyler.

    As DBA, I think this can be used for database management and monitoring; for example, to check database export status, object count, etc. So I created a package where it just simply executing a defined SQL statement and send its output back.

    —-
    Part of Package
    :
    :
    if lower(p_cmd) like ‘select all object%’ then
    select count(*) into l_return_val from all_objects;
    end if;

    return l_return_val;

    :
    :


    In the jot package, I just added the return result into the returned message.

    l_return := sql_cmd.exec_sql_cmd(l_message);

    htp.p(l_message||chr(13)||l_return);

    It is not pretty but it seems to work fine.

    Output sample-

    Jott to APEX (APEX): Select all objects.
    Select all objects.
    44373

    Thanks again for a great post.

    Ittichai

  8. Looks really cool, but is your demo still up? When configuring the custom link, the redirect to:

    http://apex.oracle.com/pls/otn/tyler.jott.register?userKey=…etc…

    yielded a 404:

    Not Found
    The requested URL /pls/otn/tyler.jott.register was not found on this server.

    Oracle-Application-Server-10g/10.1.3.0.0 Oracle-HTTP-Server Server at htmldb.oracle.com Port 80

  9. Really, really cool! It was on my list to try this with Jott and now I don’t have to – many thanks for the time saver Tyler! (P.S. I had the same problem as #4 David Peake above – 404 – and the same solution – log out of Jott and edit the custom link. Would be interesting to know what is going on there since a typical user would be stymied by the experience and likely give up).

  10. It’s great when i stumble on a good post such as this. Getting a little bit tired of the junk posts quite a few bloggers write on their blogs. Continue the good work. You now have a frequent subscriber now!!!

  11. Woah! I’m really digging the template/theme of this blog. It’s simple,
    yet effective. A lot of times it’s very difficult to get that “perfect balance” between usability and appearance. I must say that you’ve done a very good
    job with this. Also, the blog loads very quick for me on Safari.
    Exceptional Blog!

  12. Norwegian said

    Quality content is the main to attract the visitors to go to see the site, that’s
    what this web page is providing.

  13. I’ve been surfing on-line greater than three hours as
    of late, but I by no means found any interesting article like yours.
    It is beautiful value sufficient for me. In my view, if all
    webmasters and bloggers made just right content material as you probably did,
    the internet might be much more useful than ever before.

  14. I am chris and i make money online playing games!!! http://make-money-playing-games.com

  15. It’s remarkable designed for me to have a site, which is valuable designed for my know-how.

    thanks admin

  16. Very good blog! Do you have any recommendations for aspiring writers?
    I’m hoping to start my own site soon but I’m a little lost on everything.
    Would you propose starting with a free platform like WordPress or go for a paid option?
    There are so many options out there that I’m totally confused ..
    Any ideas? Appreciate it!

  17. Vincent said

    Hurrah! After all I got a website from where I know how to in fact
    take valuable data regarding my study and knowledge.

  18. Wyatt said

    My family always say that I am killing my time here
    at net, however I know I am getting experience every day by reading thes pleasant content.

  19. Chanel said

    It is in point of fact a great and helpful piece of info.

    I’m happy that you just shared this helpful information with us.
    Please stay us informed like this. Thanks for sharing.

  20. Jana said

    you’re in point of fact a just right webmaster. The website
    loading pace is incredible. It seems that you are doing any distinctive trick.
    Moreover, The contents are masterpiece. you’ve performed a magnificent
    task on this topic!

Leave a reply to Tony Cancel reply