Sunday, December 27, 2009

Using Google Translate from PL/SQL

UPDATE, JANUARY 2013: Google no longer offers the Translate API for free: "Google Translate API v1 is no longer available as of December 1, 2011 and has been replaced by Google Translate API v2. Google Translate API v1 was officially deprecated on May 26, 2011. The decision to deprecate the API and replace it with the paid service was made due to the substantial economic burden caused by extensive abuse.More information.


In today's globalized world, being able to communicate in different languages is important. Personally, I'm struggling to get my level of Spanish above the "una cerveza, por favor" level, and Google Translate is a great tool that I use often.


Wouldn't it be great to have the power of Google Translate directly from SQL? Google exposes the translation service via a RESTful (JSON) API, so I decided to write a small PL/SQL wrapper for it.

Here is the package specification:

create or replace package google_translate_pkg
as

/*

Purpose:    PL/SQL wrapper package for Google Translate API

Remarks:   see http://code.google.com/apis/ajaxlanguage/documentation/ 

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

-- http://code.google.com/apis/ajaxlanguage/documentation/reference.html#LangNameArray
g_lang_AFRIKAANS               constant varchar2(5) := 'af';
g_lang_ALBANIAN                constant varchar2(5) := 'sq';
g_lang_AMHARIC                 constant varchar2(5) := 'am';
g_lang_ARABIC                  constant varchar2(5) := 'ar';
g_lang_ARMENIAN                constant varchar2(5) := 'hy';
g_lang_AZERBAIJANI             constant varchar2(5) := 'az';
g_lang_BASQUE                  constant varchar2(5) := 'eu';
g_lang_BELARUSIAN              constant varchar2(5) := 'be';
g_lang_BENGALI                 constant varchar2(5) := 'bn';
g_lang_BIHARI                  constant varchar2(5) := 'bh';
g_lang_BULGARIAN               constant varchar2(5) := 'bg';
g_lang_BURMESE                 constant varchar2(5) := 'my';
g_lang_CATALAN                 constant varchar2(5) := 'ca';
g_lang_CHEROKEE                constant varchar2(5) := 'chr';
g_lang_CHINESE                 constant varchar2(5) := 'zh';
g_lang_CHINESE_SIMPLIFIED      constant varchar2(5) := 'zh-CN';
g_lang_CHINESE_TRADITIONAL     constant varchar2(5) := 'zh-TW';
g_lang_CROATIAN                constant varchar2(5) := 'hr';
g_lang_CZECH                   constant varchar2(5) := 'cs';
g_lang_DANISH                  constant varchar2(5) := 'da';
g_lang_DHIVEHI                 constant varchar2(5) := 'dv';
g_lang_DUTCH                   constant varchar2(5) := 'nl';  
g_lang_ENGLISH                 constant varchar2(5) := 'en';
g_lang_ESPERANTO               constant varchar2(5) := 'eo';
g_lang_ESTONIAN                constant varchar2(5) := 'et';
g_lang_FILIPINO                constant varchar2(5) := 'tl';
g_lang_FINNISH                 constant varchar2(5) := 'fi';
g_lang_FRENCH                  constant varchar2(5) := 'fr';
g_lang_GALICIAN                constant varchar2(5) := 'gl';
g_lang_GEORGIAN                constant varchar2(5) := 'ka';
g_lang_GERMAN                  constant varchar2(5) := 'de';
g_lang_GREEK                   constant varchar2(5) := 'el';
g_lang_GUARANI                 constant varchar2(5) := 'gn';
g_lang_GUJARATI                constant varchar2(5) := 'gu';
g_lang_HEBREW                  constant varchar2(5) := 'iw';
g_lang_HINDI                   constant varchar2(5) := 'hi';
g_lang_HUNGARIAN               constant varchar2(5) := 'hu';
g_lang_ICELANDIC               constant varchar2(5) := 'is';
g_lang_INDONESIAN              constant varchar2(5) := 'id';
g_lang_INUKTITUT               constant varchar2(5) := 'iu';
g_lang_IRISH                   constant varchar2(5) := 'ga';
g_lang_ITALIAN                 constant varchar2(5) := 'it';
g_lang_JAPANESE                constant varchar2(5) := 'ja';
g_lang_KANNADA                 constant varchar2(5) := 'kn';
g_lang_KAZAKH                  constant varchar2(5) := 'kk';
g_lang_KHMER                   constant varchar2(5) := 'km';
g_lang_KOREAN                  constant varchar2(5) := 'ko';
g_lang_KURDISH                 constant varchar2(5) := 'ku';
g_lang_KYRGYZ                  constant varchar2(5) := 'ky';
g_lang_LAOTHIAN                constant varchar2(5) := 'lo';
g_lang_LATVIAN                 constant varchar2(5) := 'lv';
g_lang_LITHUANIAN              constant varchar2(5) := 'lt';
g_lang_MACEDONIAN              constant varchar2(5) := 'mk';
g_lang_MALAY                   constant varchar2(5) := 'ms';
g_lang_MALAYALAM               constant varchar2(5) := 'ml';
g_lang_MALTESE                 constant varchar2(5) := 'mt';
g_lang_MARATHI                 constant varchar2(5) := 'mr';
g_lang_MONGOLIAN               constant varchar2(5) := 'mn';
g_lang_NEPALI                  constant varchar2(5) := 'ne';
g_lang_NORWEGIAN               constant varchar2(5) := 'no';
g_lang_ORIYA                   constant varchar2(5) := 'or';
g_lang_PASHTO                  constant varchar2(5) := 'ps';
g_lang_PERSIAN                 constant varchar2(5) := 'fa';
g_lang_POLISH                  constant varchar2(5) := 'pl';
g_lang_PORTUGUESE              constant varchar2(5) := 'pt-PT';
g_lang_PUNJABI                 constant varchar2(5) := 'pa';
g_lang_ROMANIAN                constant varchar2(5) := 'ro';
g_lang_RUSSIAN                 constant varchar2(5) := 'ru';
g_lang_SANSKRIT                constant varchar2(5) := 'sa';
g_lang_SERBIAN                 constant varchar2(5) := 'sr';
g_lang_SINDHI                  constant varchar2(5) := 'sd';
g_lang_SINHALESE               constant varchar2(5) := 'si';
g_lang_SLOVAK                  constant varchar2(5) := 'sk';
g_lang_SLOVENIAN               constant varchar2(5) := 'sl';
g_lang_SPANISH                 constant varchar2(5) := 'es';
g_lang_SWAHILI                 constant varchar2(5) := 'sw';
g_lang_SWEDISH                 constant varchar2(5) := 'sv';
g_lang_TAJIK                   constant varchar2(5) := 'tg';
g_lang_TAMIL                   constant varchar2(5) := 'ta';
g_lang_TAGALOG                 constant varchar2(5) := 'tl';
g_lang_TELUGU                  constant varchar2(5) := 'te';
g_lang_THAI                    constant varchar2(5) := 'th';
g_lang_TIBETAN                 constant varchar2(5) := 'bo';
g_lang_TURKISH                 constant varchar2(5) := 'tr';
g_lang_UKRAINIAN               constant varchar2(5) := 'uk';
g_lang_URDU                    constant varchar2(5) := 'ur';
g_lang_UZBEK                   constant varchar2(5) := 'uz';
g_lang_UIGHUR                  constant varchar2(5) := 'ug';
g_lang_VIETNAMESE              constant varchar2(5) := 'vi';
g_lang_WELSH                   constant varchar2(5) := 'cy';
g_lang_YIDDISH                 constant varchar2(5) := 'yi';
g_lang_UNKNOWN                 constant varchar2(5) := '';


-- translate a piece of text
function translate_text (p_text in varchar2,
p_to_lang in varchar2,
p_from_lang in varchar2 := null,
p_use_cache in varchar2 := 'YES') return varchar2;

-- detect language code for text
function detect_lang (p_text in varchar2) return varchar2;

-- get number of texts in cache
function get_translation_cache_count return number;

-- clear translation cache
procedure clear_translation_cache;

end google_translate_pkg;
/




And here is the package body:

create or replace package body google_translate_pkg
as

/*

Purpose:    PL/SQL wrapper package for Google Translate API

Remarks:   see http://code.google.com/apis/ajaxlanguage/documentation/ 

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

m_http_referrer                constant varchar2(255) := 'your-domain-name-or-website-here'; -- insert your domain/website here (required by Google's terms of use)
m_api_key                      constant varchar2(255) := null; -- insert your Google API Key here (optional but recommended)

m_service_url                  constant varchar2(255) := 'http://ajax.googleapis.com/ajax/services/language/';
m_service_version              constant varchar2(10)  := '1.0';

m_max_text_size                constant pls_integer   := 500; -- can be increased up towards 32k, the cache name size (below) must be increased accordingly 

type t_translation_cache is table of varchar2(32000) index by varchar2(550);

m_translation_cache            t_translation_cache;
m_cache_id_separator           constant varchar2(1) := '|';


procedure add_to_cache (p_from_text in varchar2,
p_from_lang in varchar2,
p_to_text in varchar2,
p_to_lang in varchar2)
as
begin

/*

Purpose:    add translation to cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

m_translation_cache (p_from_lang || m_cache_id_separator || p_to_lang || m_cache_id_separator || replace(substr(p_from_text,1,m_max_text_size), m_cache_id_separator, '')) := p_to_text;

end add_to_cache;


function get_from_cache (p_text in varchar2,
p_from_lang in varchar2,
p_to_lang in varchar2) return varchar2
as
l_returnvalue varchar2(32000);
begin

/*

Purpose:    get translation from cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

begin
l_returnvalue := m_translation_cache (p_from_lang || m_cache_id_separator || p_to_lang || m_cache_id_separator || replace(substr(p_text,1,m_max_text_size), m_cache_id_separator, ''));
exception
when no_data_found then
l_returnvalue := null;
end;

return l_returnvalue;

end get_from_cache;


function get_clob_from_http_post (p_url in varchar2,
p_values in varchar2) return clob
as
l_request     utl_http.req;
l_response    utl_http.resp;
l_buffer      varchar2(32767);
l_returnvalue clob := ' ';
begin

/*

Purpose:    do a HTTP POST and get results back in a CLOB

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

l_request := utl_http.begin_request (p_url, 'POST', utl_http.http_version_1_1);

utl_http.set_header (l_request, 'Referer', m_http_referrer); -- note that the actual header name is misspelled in the HTTP protocol
utl_http.set_header (l_request, 'Content-Type', 'application/x-www-form-urlencoded');
utl_http.set_header (l_request, 'Content-Length', to_char(length(p_values)));
utl_http.write_text (l_request, p_values);

l_response := utl_http.get_response (l_request);

if l_response.status_code = utl_http.http_ok then

begin
loop
utl_http.read_text (l_response, l_buffer);
dbms_lob.writeappend (l_returnvalue, length(l_buffer), l_buffer);
end loop;
exception
when utl_http.end_of_body then
null;
end;

end if;

utl_http.end_response (l_response);

return l_returnvalue;

end get_clob_from_http_post;


function translate_text (p_text in varchar2,
p_to_lang in varchar2,
p_from_lang in varchar2 := null,
p_use_cache in varchar2 := 'YES') return varchar2
as
l_values      varchar2(2000);
l_response    clob;
l_start_pos   pls_integer;
l_end_pos     pls_integer;
l_returnvalue varchar2(32000) := null;
begin

/*

Purpose:    translate a piece of text

Remarks:    if the "from" language is left blank, Google Translate will attempt to autodetect the language

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created
MBR     25.12.2009  Added cache for translations

*/

if trim(p_text) is not null then

if p_use_cache = 'YES' then
l_returnvalue := get_from_cache (p_text, p_from_lang, p_to_lang);
end if;

if l_returnvalue is null then

l_values := 'v=' || m_service_version || '&q=' || utl_url.escape (substr(p_text,1,m_max_text_size), false, 'UTF8') || '&langpair=' || p_from_lang || '|' || p_to_lang;

if m_api_key is not null then
l_values := l_values || '&key=' || m_api_key;
end if;

l_response := get_clob_from_http_post (m_service_url || 'translate', l_values);

if l_response is not null then

l_start_pos := instr(l_response, '{"translatedText":"');
l_start_pos := l_start_pos + 19;
l_end_pos := instr(l_response, '"', l_start_pos);

l_returnvalue := substr(l_response, l_start_pos, l_end_pos - l_start_pos);

if (p_use_cache = 'YES') and (l_returnvalue is not null) then
add_to_cache (p_text, p_from_lang, l_returnvalue, p_to_lang);
end if;

end if;

end if;

end if;

return l_returnvalue;

end translate_text;


function detect_lang (p_text in varchar2) return varchar2
as
l_url         varchar2(2000);
l_response    clob;
l_start_pos   pls_integer;
l_end_pos     pls_integer;
l_returnvalue varchar2(255);
begin

/*

Purpose:    detect language code for text

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

if trim(p_text) is not null then

l_url := m_service_url || 'detect?v=' || m_service_version || '&q=' || utl_url.escape (substr(p_text,1,m_max_text_size), false, 'UTF8');

if m_api_key is not null then
l_url := l_url || '&key=' || m_api_key;
end if;

l_response := httpuritype(l_url).getclob();

l_start_pos := instr(l_response, '{"language":"');
l_start_pos := l_start_pos + 13;
l_end_pos := instr(l_response, '",', l_start_pos);

l_returnvalue := substr(l_response, l_start_pos, l_end_pos - l_start_pos);

end if;

return l_returnvalue;

end detect_lang;


function get_translation_cache_count return number
as
l_returnvalue number;
begin

/*

Purpose:    get number of texts in cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

l_returnvalue := m_translation_cache.count;

return l_returnvalue;

end get_translation_cache_count;


procedure clear_translation_cache
as
begin

/*

Purpose:    clear translation cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

m_translation_cache.delete;

end clear_translation_cache;


end google_translate_pkg;
/






So, let's take the package for a test drive!

Detecting languages



Google can (try to) figure out which language a specific text is in.

select google_translate_pkg.detect_lang ('hola mundo') as detect1,
google_translate_pkg.detect_lang ('ich bin ein berliner') as detect2
from dual

detect1              detect2             
-------------------- --------------------
es                   de                  

1 row selected.



Translating text



This is the most common usage of the package. Note that if you leave out the "from" language parameter, Google will attempt to autodetect the language.

select google_translate_pkg.translate_text ('excuse me, where is the toilet?', 'es') as the_phrase
from dual

the_phrase                              
----------------------------------------
Disculpe, ¿dónde está el baño?          

1 row selected.



Mass translation



Here is an example that translates several rows -- the product descriptions from the demo products table (from the Application Express demo application) -- into several languages.

select pi.product_id, pi.product_name, pi.product_description,
google_translate_pkg.translate_text (pi.product_description, 'es', 'en') as spanish_description,
google_translate_pkg.translate_text (pi.product_description, 'de', 'en') as german_description
from demo_product_info pi
order by pi.product_name


product_id product_name         product_description            spanish_description            german_description            
---------- -------------------- ------------------------------ ------------------------------ ------------------------------
3 Bluetooth Headset    Hands-Free without the wires!  Manos libres sin cables!       Hands-Free ohne Kabel!        
8 Classic Projector    Does not include transparencie No incluye transparencias o lá Enthält keine Folien oder Fett
s or grease pencil             piz de grasa                   stift                         

2 MP3 Player           Store up to 1000 songs and tak Almacena hasta 1000 canciones  Speichern Sie bis zu 1000 Song
e them with you                y llevarlos con usted          s, und nehmen Sie sie mit     

4 PDA Cell Phone       Combine your cell phone and PD Combine su teléfono celular y  Kombinieren Sie Ihre Handy und
A into one device              PDA en un solo dispositivo      PDA in einem Gerät           

5 Portable DVD Player  Small enough to take anywhere! Lo suficientemente pequeño com Klein genug, um überall hin mi
o para tener en cualquier luga tnehmen!                      
r!                             tnehmen!                      

10 Stereo Headphones    Noise-cancelling headphones pe El ruido auriculares con perfe Noise-Cancelling-Kopfhörer ide
rfect for the traveler         cta para el viajero            al für den Reisenden          

9 Ultra Slim Laptop    The power of a desktop in a po El poder de una computadora de Die Leistung eines Desktop in 
rtable design                   escritorio en un diseño portá ein tragbares Design          
rtable design                  til                            ein tragbares Design          

1 3.2 GHz Desktop PC   All the options, this machine  Todas las opciones, se carga e Alle Optionen, ist diese Masch
is loaded!                     sta máquina!                   ine geladen!                  

6 512 MB DIMM          Expand your PCs memory and gai Amplíe su PC la memoria y obte Erweitern Sie Ihren PC Speiche
n more performance             ner más rendimiento            r und gewinnen mehr Leistung  

7 54" Plasma Flat Scre Mount on the wall or ceiling,  Montar en la pared o el techo, Montage auf der Wand oder Deck
en                   the picture is crystal clear!   el panorama es claro!         e, das Bild ist glasklar!     


10 rows selected.



Apex translation



Combine this package with the Apex dictionary views to get a kick-start when you are translating your own Apex applications into other languages (some tweaking of the results is probably necessary...).

select item_name, label,
google_translate_pkg.translate_text (label, 'es', 'en') as spanish_label,
google_translate_pkg.translate_text (label, 'nl', 'en') as dutch_label
from apex_application_page_items
where application_id = 103
and label is not null
and display_as <> 'Hidden'



item_name            label                spanish_label        dutch_label         
-------------------- -------------------- -------------------- --------------------
P101_PASSWORD        Password             Contraseña           Wachtwoord          
P101_USERNAME        User Name            Nombre de usuario    Gebruikersnaam      
P11_CUSTOMER_ID      Customer             Cliente              Klant               
P29_CUSTOMER_INFO    Customer Info        Información del clie Customer Info       
nte                                      
P29_ORDER_TIMESTAMP  Order Date           Fecha de pedido      Orderdatum          
P29_ORDER_TOTAL      Order Total          Orden total          Bestel Totaal       
P29_USER_ID          Sales Rep            Sales Rep            Vertegenwoordiger   


7 rows selected.



Caching



I have built in a simple cache mechanism, so that you avoid the network traffic if the phrase has already been translated in your current session (and the performance benefit can be huge if you repeatedly translate the same strings).
You can specify whether you want to use the cache (it's on by default), and there is also a procedure to clear (reset) the cache.


Conclusion



It is time to throw out your old-fashioned dictionaries and fire up SQL Plus instead :-)

Saturday, November 28, 2009

More PL/SQL Gateway Goodies

Hot on the heels of version 1.1, which was the topic of my previous blog post, the Thoth Gateway version 1.2 improves on the automatic SOAP Web Service feature, and adds a few new features as well!

Improved Automatic SOAP Web Services



Previously, every function had its own separate service endpoint. This was a bit of a pain, as you would have to (in Visual Studio-speak) add a separate web reference to each function. Now, all functions in a package are grouped together into a single service endpoint. Just add "?wsdl" to the package name, like this:



Upload files to file system instead of database table



Normally, files uploaded via a web page will be stored in the database table specified as "DocumentTableName" in the DAD configuration. In this version, there is a new configuration parameter, "DocumentFilePath", that will cause uploaded files to be saved to the file system instead.

XDB Integration



This version of the Thoth Gateway adds easy integration with Oracle XDB.




Upload files to XDB repository: You can specify a "DocumentXdbPath" in the DAD configuration file that causes uploaded files to be inserted as XDB resources in the specified folder. (This means there are now three different, and mutually exclusive, destinations for uploaded files: database table, file system, and XDB repository.)

Here, for example, we have just uploaded a zip file to XDB, which is then available via SQL, HTTP, FTP and WebDAV as usual:



Download files from XDB repository: You can specify an "XdbAlias" in the DAD configuration file. If this is specified, it will set up a virtual directory (similar to the "PathAlias" parameter) that forwards requests to the XDB repository. You can control which part of the repository you want to expose by specifying the "XdbPathRoot" parameter.

Here we are downloading the zip file via the gateway:



The new options are more fully explained in the installation guide.

Check out version 1.2 of the Thoth Gateway now!

Tuesday, November 17, 2009

Publish PL/SQL as SOAP Web Service

You can easily consume a SOAP Web Service from PL/SQL, for example using Application Express or the FLEX_WS_API (see also my companion utilities to FLEX_WS_API).


But if you want to publish (or "expose") your PL/SQL procedures as a SOAP Web Service, your options have so far been a bit limited.

JDeveloper/JPublisher



JDeveloper has a "Publish as Web Service" feature that uses JPublisher to create various Java artifacts which must then be deployed to the application server. There are some details here, and an issue you need to be aware of if you are using Oracle 10g Express Edition (XE).

Now, this Java-based approach probably works fine for you if you have Java developers and a Java infrastructure in your company, although the need to (re-)generate the Java code whenever the PL/SQL code changes seems like a bit of a hassle to me.

Native Web Services (11g)



Oracle 11g (Release 1) introduced "Native Web Services". This is a servlet running in the XDB listener that automagically exposes PL/SQL code as SOAP Web Services. Here is some more information about it.

If you are a database guy like me, you probably like the "Native" approach better than the JPublisher method. However, there are a couple of issues with Native Web Services; first of all, it's an 11g feature (which of course means that it is not available in 10g, nor in Express Edition 10g), and it requires the XDB listener (which means you must either allow direct connections to your database, or set up another web server as a proxy for XDB).

Automatic Web Services with the Thoth Gateway



Since I like the concept of Native Web Services, I decided to implement a similar feature in the Thoth Gateway, a mod_plsql replacement for Microsoft IIS.

Version 1.1 of the Thoth Gateway adds a new DAD configuration parameter called InvocationProtocol. If this is set to "SOAP" (instead of the default "CGI"), PL/SQL called through the DAD will take its parameters from a SOAP request body, and respond with a SOAP response body.

The Web Service Definition Language (WSDL) document is automatically generated if you append "?wsdl" to the end of the URL. This allows a tool like Visual Studio to easily add a Web Reference to your stored procedure.

Let's see an example. Let's say we have the following package specification:

create or replace package employee_service
as

function get_employee_name (p_empno in number) return varchar2;

function get_employees (p_search_filter in varchar2) return clob;

end employee_service;
/


And the following package body:

create or replace package body employee_service
as

function get_employee_name (p_empno in number) return varchar2
as
l_returnvalue emp.ename%type;
begin

begin
select ename
into l_returnvalue
from emp
where empno = p_empno;
exception
when no_data_found then
l_returnvalue := null;
end;

return l_returnvalue;

end get_employee_name;


function get_employees (p_search_filter in varchar2) return clob
as
l_context     dbms_xmlgen.ctxhandle;
l_returnvalue clob;
begin

-- there are many ways to generate XML in Oracle, this is one of them...

l_context := dbms_xmlgen.newcontext('select * from emp where lower(ename) like :p_filter_str order by empno');

-- let's make Tom Kyte happy :-)
dbms_xmlgen.setbindvalue (l_context, 'p_filter_str', lower(p_search_filter) || '%');

l_returnvalue := dbms_xmlgen.getxml (l_context);

dbms_xmlgen.closecontext (l_context);

return l_returnvalue;

end get_employees;


end employee_service;
/



Now navigate to the following URL with the browser (assuming you have downloaded and installed the Thoth Gateway, of course; see the installation guide in the Doc folder):

http://localhost/pls/soap-demo/employee_service.get_employee_name?wsdl


This brings up the automatically generated WSDL:




Now use your favorite SOAP testing tool (I'm using Web Service Studio, but another good tool is SoapUI) and enter the same URL.

After the test tool has generated a proxy class for the Web Service, you should see something similar to the following:



Fill in the value in the request and invoke the Web Service:



The response from the Thoth Gateway is a SOAP envelope that contains the return value of the function.

Invoking the second function in the example package above returns a CLOB with XML that represents a dataset with several rows:



Pretty cool, heh? It "just works", with no extra code or configuration necessary, except specifying "SOAP" as the protocol in the DAD!

You can use the usual parameters such as InclusionList, ExclusionList and RequestValidationFunction to control access to specific procedures. Also, the CGI environment is set up as usual before the call, so your PL/SQL code can use owa_util.get_cgi_env to get information about the client (browser).


Limitations and Caveats



There are a couple of limitations in this first release of the SOAP feature:

  • You can only call PL/SQL functions (not procedures) via SOAP.
  • Functions must return VARCHAR2 or CLOB (but as we have seen in the example above, functions returning CLOBs allow you to return any XML as the response, so this should not really be a big limitation). Support for arrays and complex (user-defined) types might come later.
  • Each function is exposed as a service endpoint. This means that in Visual Studio, for example, you must create a separate Web Reference for each function you would like to call. A future version of the gateway might group all functions in a package into one service. Update (Nov 28, 2009): As of Thoth Gateway version 1.2, all functions in a package are now grouped together as a single service endpoint.


If you would like to try it out, go and grab version 1.1 of the Thoth Gateway now!

Sunday, November 1, 2009

Bad news about Oracle XE 11g

We all love Oracle 10g Express Edition (XE), and I'm sure everyone's waiting for the 11g version which incorporates all the feature enhancements and security fixes from the last three years.

However, it now looks like we have to wait "another year or two" for the 11g version of Oracle Express Edition :-(

Seriously, Oracle? No XE 11g before late 2011? That means something like six years between the 10g and the 11g version?

Meanwhile, Microsoft is releasing its free SQL Server Express Edition on the same schedule as the for-pay version.

If Oracle is serious about using Express Edition to gain converts to the Oracle database, it should seriously reconsider this decision to delay XE 11g.

Leave a comment below if you would like to see Oracle Express Edition 11g before 2011!

Wednesday, October 14, 2009

Apex Interactive Report Tip #1: Aggregating numbers in string columns

I have been working on an Interactive Report based on a collection recently, to work around the fact that the SQL query for Interactive Reports must be static, and my requirement was to run different queries based on user input.

One problem is that since all columns in a collection are varchars (strings), the built-in aggregation feature of Interactive Reports does not work (it only allows you to aggregate on numbers, which is quite sensible, I guess). Normally you could always convert the string column to a number directly in the underlying query, but since my queries were dynamic (the whole point of using collections), that was not an option.

However, after some fiddling around I discovered that you can aggregate based on computed columns.

So, let's say we have this "transaction amount" column in our report, which consists of numbers but which Apex interprets as a string since it is retrieved from a varchar2 column in the apex_collections view:



Use the "Compute" menu item of the Interactive Report to create a new column, using the TRUNC function (for some reason, the TO_NUMBER function is not available from the list of functions, but TRUNC works -- just remember to include the number of decimals as the second parameter if you need decimals) and setting the appropriate numeric format mask:



We can now see that the new column has been added as a numeric column, since it is right-aligned instead of left-aligned (and also with a nice format mask):



The new column is now available under the "Aggregate" menu item of the Interactive Report, so we can create a Sum of the numbers:




After adding a "Control Break" to the report, we can see that the aggregation works:



Did I mention that I really like Interactive Reports? :-)

Monday, October 12, 2009

How to integrate PL_FPDF with Apex

In my previous post about the free PL_FPDF package that allows you to produce PDF documents from PL/SQL, there was a comment asking for a step-by-step guide on how to integrate this with Oracle Application Express (Apex).

This is really quite simple. By default, the pl_fpdf.output procedure will "print" your PDF document to the web browser along with a header that instructs the browser that this is a downloadable document. All you need to do is to call your procedure that generates the PDF document from a page process within Apex.

Here are the steps:

1. Start by compiling the demo procedure from my previous post into the parsing schema of your Apex application.

2. In Apex, go to your application and add a new, blank page. Let's assume the new page number is 4.

3. Under the "Page Rendering" section, add a new Process of type "PL/SQL". The name of the process can be anything, but let's call it "Produce PDF". Make sure that the point is "On load - before header".



4. In the process source, add the following PL/SQL code:


begin

  -- call the procedure to generate the PDF document and send it to the browser
  test_pl_fpdf;

  -- stop the Apex engine from running the rest of the page
  apex_application.g_unrecoverable_error := true;

end;


5. Add a link to the new page (page number 4 if you follow my assumption in step 2) from any other page in the application.

6. Run the application. Now, whenever you navigate to page 4 the PDF file will be downloaded to the browser.

That's all you need to integrate PDF generation into your Apex application.

Thursday, October 8, 2009

Free PDF package for PL/SQL

I'm sure you've heard about PL/PDF, the PL/SQL-based solution that allows you to create PDF documents directly from your database. PL/PDF is a good product, but it is not free (the current license price is USD 600 per database server).



However, I just came across a free alternative called PL_FPDF, which is a PL/SQL port of the PHP-based FPDF. The latest version of PL_PDF seems to have been released about a year ago, and I can't believe I haven't seen or heard about it before now... !

UPDATE, MARCH 2011: Also check out this alternative PDF generation package by Anton Scheffer. It seems simpler and more robust than the PL_FPDF package, but may lack certain features. Be sure to evaluate both!

PL_FPDF is just a single package, so installation is a snap. Note that it uses the ORDImage data type to place images in PDF documents, so if you are running Oracle XE (which doesn't include the ORDImage data type), you need to comment out the few procedures that deal with this data type (and obviously you will not be able to include images in your PDF documents...).

After a few hours of experimentation, I was able to produce a semi-complex PDF document that tests a number of features in PL_FPDF. My test script is included below for your convenience. Note that the default assumption is that output goes to the web browser via the HTP package, but it should be trivial to add a procedure that saves the resulting BLOB to a database table instead.

Note: Since PL_FPDF is based on FPDF, I found the online documentation for FPDF (see link above) very useful in order to find out how the API for PL_FPDF works.

create or replace procedure test_pl_fpdf
as
l_text varchar2(32000) := 'First, reduce actual complexity by eliminating unnecessary features and then hiding what you can''t eliminate. Secondly, reduce perceived complexity by minimizing visual noise and reusing elements. And finally, use the blank state to help orient users. Minimizing complexity in the user interface will help people learn your application more quickly, use it more effectively and be happier all the while.

As jazz musician Charles Mingus said, "Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that''s creativity."';

begin

-- create document in portrait mode, use measurements in millimeters (mm), and use the A4 page format
pl_fpdf.FPDF('P','mm','A4');
pl_fpdf.openpdf;

-- display full page, two facing pages 
pl_fpdf.setdisplaymode ('fullpage', 'two');

-- a procedure that will be called at the end of each page
-- pl_fpdf.setfooterproc('demo.test_pdf_footer');  

pl_fpdf.AddPage();

-- set up some headers
pl_fpdf.SetFillColor(255,128,128);
pl_fpdf.SetFont('Arial','B',14);
--this header will be filled with the background color 
pl_fpdf.cell(40,7,'TABLESPACE', 1, 0, 'L', pfill => 1);
pl_fpdf.cell(40,7,'CONTENTS', 1);
pl_fpdf.cell(40,7,'INITIAL EXTENT', 1, 1);

pl_fpdf.SetFont('Arial','',14);

for l_rec in (select tablespace_name, contents, initial_extent from dba_tablespaces order by 1) loop

pl_fpdf.settextcolor(0,0,0);
pl_fpdf.cell(40,7,l_rec.tablespace_name,'B');
pl_fpdf.cell(40,7,l_rec.contents,'B');
-- some conditional formatting
if l_rec.initial_extent > 66000 then
pl_fpdf.settextcolor(255,0,0);
else
pl_fpdf.settextcolor(0,0,0);
end if;
pl_fpdf.cell(40,7,l_rec.initial_extent,'B', 1, 'R');

end loop;

-- a page that shows how to position chunks of text (with automatic line breaks) on the page

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (100, 20);
pl_fpdf.Cell(10,10,'Cool Quote 1:');

pl_fpdf.setxy (100, 50);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);


pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (10, 130);
pl_fpdf.Cell(10,10,'Cool Quote 2:');

pl_fpdf.setxy (10, 150);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);

-- a page that demonstrates some simple drawing with lines and rectangles

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',14);
pl_fpdf.Cell(0,0,'And now, some beautiful line art...',0,1,'C');

pl_fpdf.line (10,10, 50, 50);
pl_fpdf.rect (50,50, 50, 50);
pl_fpdf.setdrawcolor(0,255,0);
pl_fpdf.line (150,150, 50, 50);

pl_fpdf.setdrawcolor(0,0,0);

-- a simple table of employees, without headings

pl_fpdf.AddPage();

for l_rec in (select empno, ename from emp order by ename) loop

pl_fpdf.cell(40,7,l_rec.empno, 'B');
pl_fpdf.cell(40,7, l_rec.ename, 'B', 1);

end loop;


pl_fpdf.SetFont('Arial','B',48);
pl_fpdf.settextcolor(0,0,255);

pl_fpdf.setxy (100, 250);

pl_fpdf.cell(80,10,'THE END', palign => 'C');

pl_fpdf.Output();

end test_pl_fpdf;




Conclusion: While PL/PDF is more advanced in terms of features, PL_FPDF might be for you if you just need some simple PDF reports in your application.

Monday, October 5, 2009

Native PL/SQL support in DB2

As reported by Steven Feuerstein in his PL/SQL programming newsletter this month, it is now possible to compile and run PL/SQL applications in IBM's DB2 database! Check out this demonstration video and more details at IBM's Developerworks.

Of course, the list of supported built-in packages is rather short (for example, UTL_HTTP and the XML packages are missing), but they do have support for REF CURSORS, CONNECT BY, and other advanced Oracle features.

This is cool, because it opens up a new market for all us PL/SQL developers out here.

Worth mentioning, too, is that EnterpriseDB (based on PostgreSQL) also has PL/SQL support.

Tuesday, August 18, 2009

Thoth Gateway: mod_plsql replacement for Microsoft IIS

Take a look at this screenshot:



Yes, that's right... Oracle Application Express running on Microsoft's Internet Information Server (IIS)! In-between trips to the beach this summer, I decided to write a PL/SQL gateway module for IIS. The result, called the Thoth Gateway, is now available as open source.

This means that the deployment options for PL/SQL web applications are now:

  • The Embedded PL/SQL Gateway (DBMS_EPG), which is a webserver built into the database itself
  • The Oracle HTTP Server (OHS), which is based on the Apache codebase, with mod_plsql
  • Java-based web servers (such as Tomcat, WebLogic, and others) with the open-source DBPrism plugin, or the upcoming, Oracle-supported Apex Listener
  • And now, Microsoft Internet Information Server (IIS) with the Thoth Gateway module



The Thoth Gateway is implemented in C# as an ASP.NET HttpModule and uses the Oracle Data Provider for .NET (ODP.NET) to communicate with the database.

The Thoth Gateway supports almost all of mod_plsql's features, and even adds a few more (such as CLOB parameter support for values over 32K). Check out the project page for the details.

It's also worth noting that the Thoth Gateway, being a volunteer project, is not officially supported by Oracle in any way. But at least now you have the option of using Microsoft's IIS as the web server for Oracle PL/SQL web applications. In fact, you can use any of the above listed gateways side-by-side for the same PL/SQL web application (all on the same box, or on different physical servers).

I am very interested in feedback if you decide to go ahead and try the Thoth Gateway, so feel free to add comments to this post about any bugs or problems you encounter, or use the project's issue tracker (adding issues requires a Google account).

Tuesday, July 14, 2009

Calling a SOAP web service from PL/SQL by extending the FLEX_WS_API

Jason Straub has written a Flexible Web Service API package that allows you to call SOAP web services from PL/SQL. The API handles a lot of low-level details for you. As far as I know, the API will be incorporated into the upcoming Apex 4.0.


The FLEX_WS_API package is very useful; however, there are still a few things, such as constructing the SOAP envelope and logging requests and response for debugging purposes, that you need to implement yourself.

Here are a few helpers that I have written to do just that:


Web service log table

First, let's make a table that can be used to log web service requests and responses.

create table ws_log (
request_start_date  date,
request_end_date    date default sysdate,
log_text            varchar2(4000),
ws_url              varchar2(4000),
ws_method           varchar2(4000),
ws_request          clob,
ws_response         clob,
val1                varchar2(4000),
val2                varchar2(4000),
val3                varchar2(4000)
);




Web service utility package

Here is the header of a package that handles logging, and also simplifies the extraction of values from the web service response.


create or replace package flex_ws_util
as

/*

Purpose:    The package is a companion to the flex_ws_api package

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

-- get string value
function get_value (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return varchar2;

-- get clob value
function get_value_clob (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return clob;

-- get date value
function get_value_date (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in date := null,
p_date_format in varchar2 := null) return date;

-- get number value
function get_value_number (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in number := null) return number;

-- log web service request
procedure log_request (p_url in varchar2,
p_method in varchar2,
p_request in clob,
p_response in xmltype,
p_request_start_date in date := null,
p_log_text in varchar2 := null,
p_val1 in varchar2 := null,
p_val2 in varchar2 := null,
p_val3 in varchar2 := null);

end flex_ws_util;
/




And then the package body:

create or replace package body flex_ws_util
as

/*

Purpose:    The package is a companion to the flex_ws_api package

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/


function get_value (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return varchar2
as
l_returnvalue varchar2(32767);
begin

/*

Purpose:    Get string value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

begin
l_returnvalue := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
exception
when others then
l_returnvalue := nvl(p_value_if_error, sqlerrm);
end;

return l_returnvalue;

end get_value;


function get_value_clob (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return clob
as
l_returnvalue clob;
begin

/*

Purpose:    Get clob value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

begin
l_returnvalue := flex_ws_api.parse_xml_clob (p_xml, '//' || p_name || '/text()', p_namespace);
exception
when others then
l_returnvalue := nvl(p_value_if_error, sqlerrm);
end;

return l_returnvalue;

end get_value_clob;


function get_value_date (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in date := null,
p_date_format in varchar2 := null) return date
as
l_str         varchar2(32767);
l_returnvalue date;
begin

/*

Purpose:    Get date value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/


begin
l_str := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
l_returnvalue := to_date (l_str, nvl(p_date_format, 'DD.MM.RRRR HH24:MI:SS'));
exception
when others then
l_returnvalue := p_value_if_error;
end;

return l_returnvalue;

end get_value_date;


function get_value_number (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in number := null) return number
as
l_str         varchar2(32767);
l_returnvalue number;
begin

/*

Purpose:    Get number value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

begin
l_str := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
l_returnvalue := to_number (l_str);
exception
when others then
l_returnvalue := p_value_if_error;
end;

return l_returnvalue;

end get_value_number;


procedure log_request (p_url in varchar2,
p_method in varchar2,
p_request in clob,
p_response in xmltype,
p_request_start_date in date := null,
p_log_text in varchar2 := null,
p_val1 in varchar2 := null,
p_val2 in varchar2 := null,
p_val3 in varchar2 := null)
as
pragma autonomous_transaction;
l_sysdate date := sysdate;
begin

/*

Purpose:    Log web service request

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

insert into ws_log (request_start_date, request_end_date,
log_text, ws_url, ws_method,
ws_request, ws_response,
val1, val2, val3)
values (nvl(p_request_start_date, l_sysdate), l_sysdate,
substr(p_log_text,1,4000), substr(p_url,1,4000), substr(p_method,1,4000),
p_request, p_response.getclobval(),
substr(p_val1,1,4000),substr(p_val2,1,4000), substr(p_val3,1,4000));

commit;

end log_request;



end flex_ws_util;
/





PL/SQL object type for SOAP envelopes


The following object type is used to simplify creation of SOAP envelopes to be used in web service calls:


create or replace TYPE t_soap_envelope AS OBJECT (

/*

Purpose:    Object type to handle SOAP envelopes for web service calls

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

-- public properties
service_namespace       varchar2(255),
service_method          varchar2(4000),
service_host            varchar2(4000),
service_path            varchar2(4000),
service_url             varchar2(4000),
soap_action             varchar2(4000),
soap_namespace          varchar2(255),
envelope                clob,

-- private properties
m_parameters            clob,

constructor function t_soap_envelope (p_service_host in varchar2,
p_service_path in varchar2,
p_service_method in varchar2,
p_service_namespace in varchar2 := null,
p_soap_namespace in varchar2 := null,
p_soap_action in varchar2 := null) return self as result,

member procedure add_param (p_name in varchar2,
p_value in varchar2,
p_type in varchar2 := null),

member procedure add_xml (p_xml in clob),

member procedure build_env,

member procedure debug_envelope

);
/



The type body is implemented like this:

create or replace type body t_soap_envelope
as

/*

Purpose:    Object type to handle SOAP envelopes for web service calls

Remarks:  

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

constructor function t_soap_envelope (p_service_host in varchar2,
p_service_path in varchar2,
p_service_method in varchar2,
p_service_namespace in varchar2 := null,
p_soap_namespace in varchar2 := null,
p_soap_action in varchar2 := null) return self as result
as
begin
self.service_host := p_service_host;
self.service_path := p_service_path;
self.service_method := p_service_method;
self.service_namespace := nvl(p_service_namespace, 'xmlns="' || p_service_host || '/"');
self.service_url := p_service_host || '/' || p_service_path;
self.soap_namespace := nvl(p_soap_namespace, 'soap');
self.soap_action := nvl(p_soap_action, p_service_host || '/' || p_service_method);
self.envelope := '';
build_env;
return;
end;


member procedure add_param (p_name in varchar2,
p_value in varchar2,
p_type in varchar2 := null)
as
begin

if p_type is null then
m_parameters := m_parameters || chr(13) || '  <' || p_name || '>' || p_value || '</' || p_name || '>';
else
m_parameters := m_parameters || chr(13) || '  <' || p_name || ' xsi:type="' || p_type || '">' || p_value || '</' || p_name || '>';
end if;
build_env;

end add_param;


member procedure add_xml (p_xml in clob)
as
begin

m_parameters := m_parameters || chr(13) || p_xml;
build_env;

end add_xml;


member procedure build_env (self in out t_soap_envelope)
as
begin

self.envelope := '<' || self.soap_namespace || ':Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:' || self.soap_namespace || '="http://schemas.xmlsoap.org/soap/envelope/">' ||
'<' || self.soap_namespace || ':Body>' ||
'<' || self.service_method || ' ' || self.service_namespace || '>' ||
self.m_parameters || chr(13) ||
'</' || self.service_method || '>' ||
'</' || self.soap_namespace || ':Body>' ||
'</' || self.soap_namespace || ':Envelope>';    

end build_env;


member procedure debug_envelope
as
i      pls_integer;
l_len  pls_integer;
begin

if envelope is not null then

i := 1; l_len := length(envelope);

while (i <= l_len) loop
dbms_output.put_line(substr(envelope, i, 200));
i := i + 200;
end loop;

else
dbms_output.put_line ('WARNING: The envelope is empty...');
end if;


end debug_envelope;

end;
/




Example of use

With the above objects created in your database, the code for calling a web service and extracting and logging the results now becomes simple and elegant like this:


declare
l_env          t_soap_envelope;
l_xml          xmltype;
l_val          varchar2(4000);
l_start_date   date;
begin

l_env := t_soap_envelope ('http://www.webserviceX.NET', 'length.asmx', 'ChangeLengthUnit', 'xmlns="http://www.webserviceX.NET/"');

l_env.add_param ('LengthValue', '100');
l_env.add_param ('fromLengthUnit', 'Feet');
l_env.add_param ('toLengthUnit', 'Meters');

l_start_date := sysdate;

l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);

l_val := flex_ws_util.get_value (l_xml, 'ChangeLengthUnitResult', l_env.service_namespace, 'error');

flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date, p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);

end;




If you have complex parameters that you need to add to the request, you can use the add_xml member procedure of the t_soap_envelope type to add any content to the envelope.

Sunday, July 5, 2009

Creating a REST web service with PL/SQL (and making pretty URLs for your Apex apps)





If you need to expose the data in your Oracle database to other systems in a standardized and technology-neutral way, web services are a natural choice. Broadly speaking, web services come in two flavors: SOAP and REST. Despite its name, the Simple Object Access Protocol (SOAP) can be complex and overkill for many common scenarios. Representational State Transfer (REST) is considered more lightweight and easy to use.

There is a good introduction to REST here: http://www.xfront.com/REST-Web-Services.html

A key point is that REST is not really a standard, but an architectural style. It is not limited to web services consumed by machines, either. Applications built using Ruby on Rails and the ASP.NET MVC framework typically have user-friendly URLs based on the REST principles.

For example, if you have been to StackOverflow.com, you will see URLs like the following, which are "clean" and friendly both to users and to search engines:

http://stackoverflow.com/users
http://stackoverflow.com/users/1/jeff-atwood
http://stackoverflow.com/questions
http://stackoverflow.com/questions/tagged/oracle
http://stackoverflow.com/questions/1078506/oracle-sql-developer-how-to-view-results-from-a-ref-cursor



So, REST is a good way of exposing resources on the web, and your Oracle database is full of resources (data), but how can you build a REST service using only PL/SQL?

The key to building a REST service in PL/SQL is in a documented, but little-used feature of the Embedded PL/SQL Gateway (and mod_plsql) called "Path Aliasing". I was not aware that this feature existed until I discovered it "by accident" while browsing the mod_plsql documentation:

http://download.oracle.com/docs/cd/A97335_02/apps.102/a90099/feature.htm#1007126

"If the PL/SQL Gateway encounters in an incoming URL the keyword entered in the Path Alias field, it invokes the procedure entered in the Path Alias Procedure field. (...) Applications that use path aliasing must implement the Path Alias Procedure. The procedure receives the rest of the URL (path_alias_URL) after the keyword, URL, as a single parameter, and is therefore responsible and also fully capable of dereferencing the object from the URL. Although there is no restriction on the name and location for this procedure, it can accept only a single parameter, p_path, with the datatype varchar2."

Sounds good, so let's try it out. First, we need to configure the Database Access Descriptor (DAD) to define a PL/SQL procedure which will handle our REST requests. Using the embedded gateway (DBMS_EPG), the attributes are called "path-alias" and "path-alias-procedure" (the corresponding DAD attributes for mod_plsql are "PlsqlPathAlias" and "PlsqlPathAliasProcedure").

I will be using the embedded gateway for this example. Assuming you have an existing DAD called "devtest", run the following as user SYS (or another user who has the privileges to modify the EPG configuration).

begin
dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias', attr_value => 'rest-demo');
dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias-procedure', attr_value => 'rest_handler.handle_request');

end;
/




Then we need to create the procedure itself. Run the following in the schema associated with the DAD:


create or replace package rest_handler
as

/*

Purpose:      A simple example of RESTful web services with PL/SQL (see http://en.wikipedia.org/wiki/Representational_State_Transfer#RESTful_web_services)

Remarks:      The DAD must be configured to use a path-alias and path-alias-procedure

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

-- the main procedure that will handle all incoming requests
procedure handle_request (p_path in varchar2);

end rest_handler;
/





And then the package body (the example assumes that the EMP and DEPT demo tables exist in your schema; if not, then modify the code accordingly):

create or replace package body rest_handler
as

/*

Purpose:      A simple example of RESTful web services with PL/SQL (see http://en.wikipedia.org/wiki/Representational_State_Transfer#RESTful_web_services)

Remarks:      The DAD must be configured to use a path-alias and path-alias-procedure

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/


g_request_method_get           constant varchar2(10) := 'GET';
g_request_method_post          constant varchar2(10) := 'POST';
g_request_method_put           constant varchar2(10) := 'PUT';
g_request_method_delete        constant varchar2(10) := 'DELETE';

g_resource_type_employees      constant varchar2(255) := 'employees';
g_resource_type_departments    constant varchar2(255) := 'departments';


procedure handle_emp (p_request_method in varchar2,
p_id in number)
as
begin

/*

Purpose:      Specific handler for Employees

Remarks:

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

if (p_id is not null) then

if p_request_method = g_request_method_delete then

delete
from emp
where empno = p_id;

elsif p_request_method = g_request_method_get then

for l_rec in (select * from emp where empno = p_id) loop
htp.p(l_rec.empno || ';' || l_rec.ename || ';' || l_rec.sal);
end loop;

end if;

else

if p_request_method = g_request_method_get then

for l_rec in (select * from emp order by empno) loop
htp.p(l_rec.empno || ';' || l_rec.ename || ';' || l_rec.sal || '
');
end loop;

end if;

end if;

end handle_emp;


procedure handle_dept (p_request_method in varchar2,
p_id in number)
as
begin

/*

Purpose:      Specific handler for Departments

Remarks:

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

if (p_id is not null) then

if p_request_method = g_request_method_delete then

delete
from dept
where deptno = p_id;

elsif p_request_method = g_request_method_get then

for l_rec in (select * from dept where deptno = p_id) loop
htp.p(l_rec.deptno || ';' || l_rec.dname || ';' || l_rec.loc);
end loop;

end if;

else

if p_request_method = g_request_method_get then

for l_rec in (select * from dept order by deptno) loop
htp.p(l_rec.deptno || ';' || l_rec.dname || ';' || l_rec.loc || '
');
end loop;

end if;

end if;

end handle_dept;


procedure handle_request (p_path in varchar2)
as
l_request_method constant varchar2(10) := owa_util.get_cgi_env('REQUEST_METHOD');
l_path_elements  apex_application_global.vc_arr2;
l_resource       varchar2(2000);
l_id             number;

begin

/*

Purpose:      The main procedure that will handle all incoming requests

Remarks:      Parses the incoming path and calls a specific handler for each resource type

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

-- note that an extra delimiter is added to the path, in case the user leaves out the trailing slash
l_path_elements := apex_util.string_to_table (p_path || '/', '/');

begin
l_resource := l_path_elements(1);
l_id := l_path_elements(2);
exception
when value_error or no_data_found then
l_resource := null;
l_id := null;
end;

case lower(l_resource)
when g_resource_type_employees then
handle_emp (l_request_method, l_id);
when g_resource_type_departments then
handle_dept (l_request_method, l_id);
when 'apex-employees' then
-- we can also use this REST handler to make pretty, search-engine-friendly URLs for Apex applications without having to use Apache mod_rewrite
apex_application.g_flow_id := 104;
owa_util.redirect_url('http://127.0.0.1:8080/apex/f?p=104:2:' || apex_custom_auth.get_session_id_from_cookie || '::::P2_EMPNO:' || l_id, true);
else
owa_util.status_line(404, 'Resource type not recognized.', true);
end case;

end handle_request;


end rest_handler;
/





Now try the following URLs in your browser, and you should be able to see the familiar EMP and DEPT data, in all their RESTful glory! Note that for simplicity, the example code produces simple semicolon-separated values, but depending on your requirements and who will consume the service (machine or human), you will probably want to use XML or HTML as the output format.

http://127.0.0.1:8080/devtest/rest-demo/departments
http://127.0.0.1:8080/devtest/rest-demo/departments/10
http://127.0.0.1:8080/devtest/rest-demo/employees
http://127.0.0.1:8080/devtest/rest-demo/employees/7839






Bonus: Making the URLs in your Apex applications pretty


The URLs generated by Apex are not very friendly to users nor to search engines. You can use Apache with mod_rewrite to set up mapping between REST-style URLs to your Apex pages, but the example code above also shows how this can be accomplished using pure PL/SQL.

The example assumes that you have an Apex application with Application ID = 104, and that you have made a Form on the EMP table on Page 2 of the application.

Try the following link

http://127.0.0.1:8080/devtest/rest-demo/apex-employees/7839




and you should be redirected to the Apex application (and if you were already logged into the Apex application, you don't have to login again as it will reuse your existing session).


REST for inserts and updates

The four HTTP methods are GET, PUT, POST and DELETE. To create a REST web service that can update data as well as query it, we need to inspect the CGI environment variable REQUEST_METHOD and process the request accordingly (see the Wikipedia article for details). The example code implements the GET and DELETE methods for Employees and Departments.

However, I don't see how we can implement POST or PUT processing using the PathAlias technique. The problem is that the webserver/gateway only sends the URL to the PathAliasProcedure. Any data that is POSTed to the URL is simply discarded by the gateway. Ideally, the names and values of the request should be sent to the PathAliasProcedure in name/value arrays (just like the gateway does when using flexible parameter passing). If anyone from Oracle is reading this, it can be considered an enhancement request for the next version of mod_plsql and the embedded gateway!

Even with this limitation, the ability to expose (read-only) data from the database as RESTful web services using just PL/SQL is pretty cool, isn't it? :-)

Tuesday, June 16, 2009

The Fat Database (or Thick Database) Approach

I'm a big believer in the so-called "Fat Database" paradigm for data-centric business applications.



I think I first heard the term "Thick Database" in a presentation by Dr. Paul Dorsey at the ODTUG conference in 2007.
I prefer the slightly more hip term "Fat Database", and offer my own definition of the term:



"Building applications using the Fat Database approach means leveraging the full potential of the database engine and its features, rather than treating the database as a bit bucket. If a problem can be solved using the database, it should be solved using the database, rather than in a programming language outside the database."



In other words, the exact opposite of the current trend, which is to avoid any database feature except basic tables. The enterprise architecture astronauts would rather reinvent the wheel over and over again, using the latest silver bullet in the endless stream of "new and improved" languages and frameworks that appear (and disappear) every few years.
Benefits of the Fat Database approach include reduced cost and complexity, increased performance, and a degree of immunity against the need to constantly rewrite code in a rapidly changing technology landscape.

Here is a collection of links to presentations and papers related to the Fat Database approach:

Dr. Paul Dorsey, co-author of seven Oracle Press books on Designer, Database Design, Developer, and JDeveloper

Toon Koppelaars, co-author of Applied Mathematics for Database Professionals


Others

I will return to this subject in future postings on this blog.

Wednesday, June 10, 2009

The Oracle Database as Development Platform

There is really a lot of amazing stuff that you can do using just the Oracle database and PL/SQL these days. I've made this diagram to illustrate the Oracle database developer's toolbox (click image to enlarge):





Note that this is all native functionality in the database itself, it does not include anything from the Oracle Fusion (Java) technology stack (except JDeveloper, which is free and can be used for general database and web development, not just Java).