The Talking Database – on putting query results into words – soundbiting from SQL?
In my investigations into the role the Oracle RDBMS can play in application architectures and especially how Java applications can leverage the database, I have ventured into some strange environments. Where the database acquires new skills and features, including the power of speech…
With a few tricks that I will describe below, I managed to teach the database how to speak out, for example how to report on the employees in table EMP.
For a sample of what that sounds like, click this link scott.soundmachine.play_employee_7369 . The text you here is one actually produced by the database, reporting on an actual record in the database.
After an update of that record, the sample would describe the new information.
The complete source code and story behind this approach is unveiled in the Masterclass Leverage the Oracle Database in Java Applications, Friday 4th February 2011 (AMIS, Nieuwegein). See http://www.amis.nl/amis-masterclasses/oracle-database-in-java-applications for more details.
This whole investigation started last week, when at one of my projects, I was pestered all day long with short speeches, especially in German, initiated by Daan. He showed me that what he had been using to get on my nerves was Google Translate, a site that supports translation as well as text-to-speech.
At that moment, the challenge was born to make the database produce sound bites. It look a little research, I had to combine a number of features I do not frequently work with, but finally I had fit the pieces together and my own local database started to speak. It was quite an emotional moment, a bit like ‘Mary had a little lamb’ I suppose (see: http://en.wikipedia.org/wiki/File:Thomas_Edison_Mary_had_lamb.ogg).
This next picture describes in more detail what I have crafted:
A PL/SQL package (soundmachine) is exposed through dbms_epg. It can be invoked through URLs such as http://xp-vm:2100/hrm/scott.soundmachine.play_employee?p_empno=7369.
Procedure play_employee has p_empno for its input parameter. It queries table EMP to retrieve details on the designated employee, and creates the text that should be turned into sound, as a string.
The procedure then calls upon a second procedure to invoke Google Translate. This returns responses of mime type audio/mpeg, containing binary data. This data is stored locally in a BLOB variable. Several segments of sound (Google Translate handles a maximum of 100 characters at a time) are collected in this way, appended together in a single larger BLOB.
Finally, the owa_util.mime_header procedure is used to set the correct mime header for the HTTP response the dbms_epg framework will return to the browser, the content length (the size of the pieced-together-blob) is set and then the contents of the blob is downloaded with the wpg_docload.download_file(blob) procedure.
Once the response is complete, the browser will run the appropriate plugin for mime type audio/mpeg, most likely a media player.
The source code for the Soundmachine: Â soundmachine.text
- XSL Transforming XML generated from SQL results using JSTL tags in JSP pages – putting JSTL to the test
- Dynamic and Conditional grouping in SQL queries – for flexible results from single query (oh and a useful case for the CUBE operator)
- Oracle releases Raptor – free tool for Database Development (SQL, PL/SQL, database object browsing)
- Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table
- That's Rich! Putting a smile on ADF Faces (presenting at ODTUG Kaleidoscope 2009)