SELECT * FROM spreadsheet (or How to parse a CSV file using PL/SQL)

I recently needed to retrieve/download a comma-separated values (CSV) file from a website, and insert the data in an Oracle database table.

After googling around a bit, I found various pieces of the solution on AskTom, ExpertsExchange and other sites, which I put together in the following generic utility package for CSV files.

Usage

Because I have implemented the main parsing routine as a pipelined function, you can process the data either using straight SQL, or in a PL/SQL program.

For example, you can retrieve a download a CSV file as a clob directly from the web and return it as a table with a single statement:

select *
from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))

And maybe do a direct insert via INSERT .. SELECT :

insert into my_table (first_column, second_column)
select c001, c002
from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))

You can of course also use SQL to filter the results (although this may affect performance):

select *
from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))
where c002 = 'Chevy'

Or you can do it in a more procedural fashion, like this:

create table x_dump
(clob_value clob,
 dump_date date default sysdate,
 dump_id number);


declare
  l_clob clob;

  cursor l_cursor
  is
  select csv.*
  from x_dump d, table(csv_util_pkg.clob_to_csv(d.clob_value)) csv
  where d.dump_id = 1;

begin

  l_clob := httpuritype('http://www.foo.example/bar.csv').getclob();
  insert into x_dump (clob_value, dump_id) values (l_clob, 1);
  commit;
  dbms_lob.freetemporary (l_clob);

  for l_rec in l_cursor loop
    dbms_output.put_line ('row ' || l_rec.line_number || ', col 1 = ' || l_rec.c001);
  end loop;

end;

Auxiliary functions

There are a few additional functions in the package that are not necessary for normal usage, but may be useful if you are doing any sort of lower-level CSV parsing. The csv_to_array function operates on a single CSV-encoded line (so to use this you would have to split the CSV lines yourself first, and feed them one by one to this function):

declare
  l_array t_str_array;
  l_val varchar2(4000);
begin

  l_array := csv_util_pkg.csv_to_array ('10,SMITH,CLERK,"1200,50"');

  for i in l_array.first .. l_array.last loop
    dbms_output.put_line('value ' || i || ' = ' || l_array(i));
  end loop;

  -- should output SMITH
  l_val := csv_util_pkg.get_array_value(l_array, 2);
  dbms_output.put_line('value = ' || l_val);

  -- should give an error message stating that there is no column called DEPTNO because the array does not contain seven elements
  -- leave the column name out to fail silently and return NULL instead of raising exception
  l_val := csv_util_pkg.get_array_value(l_array, 7, 'DEPTNO');
  dbms_output.put_line('value = ' || l_val);

end;

Installation

In order to compile the package, you will need these SQL types in your schema:

create type t_str_array as table of varchar2(4000);
/

create type t_csv_line as object (
  line_number  number,
  line_raw     varchar2(4000),
  c001         varchar2(4000),
  c002         varchar2(4000),
  c003         varchar2(4000),
  c004         varchar2(4000),
  c005         varchar2(4000),
  c006         varchar2(4000),
  c007         varchar2(4000),
  c008         varchar2(4000),
  c009         varchar2(4000),
  c010         varchar2(4000),
  c011         varchar2(4000),
  c012         varchar2(4000),
  c013         varchar2(4000),
  c014         varchar2(4000),
  c015         varchar2(4000),
  c016         varchar2(4000),
  c017         varchar2(4000),
  c018         varchar2(4000),
  c019         varchar2(4000),
  c020         varchar2(4000)
);
/

create type t_csv_tab as table of t_csv_line;
/

UPDATE 04.04.2012: The latest version of the package itself (CSV_UTIL_PKG) can be found as part of the Alexandria Utility Library for PL/SQL.

Performance

On my test server (not my laptop), it takes about 35 seconds to process 12,000 rows in CSV format. I don't consider this super-fast, but probably fast enough for many CSV processing scenarios.

If you have any performance-enhancing tips, do let me know!

Bonus: Exporting CSV data

You can also use this package to export CSV data, for example by using a query like this.

select csv_util_pkg.array_to_csv (t_str_array(company_id, company_name, company_type)) as the_csv_data
from company
order by company_name

THE_CSV_DATA
--------------------------------
260,Acorn Oil & Gas,EXT
261,Altinex,EXT
262,Amerada Hess,EXT
263,Atlantic Petroleum,EXT
264,Beryl,EXT
265,BG,EXT
266,Bow Valley Energy,EXT
267,BP,EXT

This might come in handy, even in these days of XML and JSON ... :-)

Using TRUNC and ROUND on dates

Maybe this is old news to some, but I recently became aware that it is possible to use TRUNC and ROUND not just on a NUMBER, but also on a DATE value.

For example, you can get the start of the month for a given date (using TRUNC), or the "closest" start of the month, rounded forward or backwards in time appropriate (using ROUND):

select sysdate,
  trunc(sysdate, 'YYYY') as trunc_year,
  trunc(sysdate, 'MM') as trunc_month,
  round(sysdate, 'MM') as round_month,
  round(sysdate + 15, 'MM') as round_month2
from dual

The above gives the following results:

SYSDATE                   TRUNC_YEAR                TRUNC_MONTH               ROUND_MONTH               ROUND_MONTH2              
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- 
06.04.2010 20:10:56       01.01.2010 00:00:00       01.04.2010 00:00:00       01.04.2010 00:00:00       01.05.2010 00:00:00       

Somewhat related to this topic is the relatively obscure (?) EXTRACT function, which allows you to extract a part of a DATE:

select sysdate,
  extract(day from sysdate) as extract_day,
  extract(month from sysdate) as extract_month,
  extract(year from sysdate) as extract_year
from dual

Which gives the following results:

SYSDATE                   EXTRACT_DAY            EXTRACT_MONTH          EXTRACT_YEAR           
------------------------- ---------------------- ---------------------- ---------------------- 
06.04.2010 20:13:01       6                      4                      2010                   

If you try to extract the "hour", "minute" or "second" from a DATE, however, you get an ORA-30076: invalid extract field for extract source.

For some reason, these only work on TIMESTAMP values, not on the DATE datatype (which seems like an arbitrary limitation to me). Nevertheless:

select systimestamp,
  extract(hour from systimestamp) as extract_hour,
  extract(minute from systimestamp) as extract_minute,
  extract(second from systimestamp) as extract_second
from dual

The above gives the following results:

SYSTIMESTAMP  EXTRACT_HOUR           EXTRACT_MINUTE         EXTRACT_SECOND         
------------- ---------------------- ---------------------- ---------------------- 
06.04.2010 20.17.12,047000000 +02:00 18                     17                     12,047                 

jQGrid Integration Kit for PL/SQL and Apex

I started developing applications back in the good (?) old client/server days. I was fortunate enough to discover Delphi quite early. Even from the start, the lowly 16-bit Delphi version 1 had a kick-ass DBGrid control which allowed you to quickly and easily build data-centric applications. Just write a SQL statement in a TDataSet component, connect it to the grid, and voila! Instant multi-row display and editing out of the box, without any coding.

Fast forward a decade. While I do enjoy building web applications (with PL/SQL and Apex) these days, I've always missed the simplicity of that DBGrid in Delphi. Creating updateable grids with Apex is pretty tedious work (not being entirely satisfied with the built-in updateable tabular forms, I've employed a combination of the apex_item API, page processes for updates and deletes, and custom-made Javascript helpers). It doesn't help that you have to refer to the tabular form arrays by number, rather than by name (g_f01, g_f02, etc.), and that you are restricted to a total of 50 columns per page.

Enter jQGrid, "an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web".

jQGrid can be integrated with any server-side technology, so I decided to integrate it with PL/SQL and Apex.

Features

As of version 1.0, the jQGrid for PL/SQL and Apex has the following features:

• Single line of PL/SQL code to render grid


• Populate data based on REF CURSOR or SQL text (with or without bind variables). The REF CURSOR support is based on my REF Cursor to JSON utility package.


• Define display modes (read only, sortable, editable) and edit types (checkbox, textarea, select list) per column


• Store grid configuration in database, or specify settings via code (for read-only grids)


• Ajax updates (insert, update, delete) based on either automatic row processing (dynamic SQL) or against your own package API


• Multiple grids per page


• Integrated logging and instrumentation


• Usable without Apex (for stand-alone PL/SQL Web Toolkit applications) or with Apex, optionally integrated with Apex session security

The jQGrid Integration Kit for PL/SQL is free and open source. Download and try it now!.

REF Cursor to JSON

REF Cursors are cool. They allow you to encapsulate SQL queries behind a PL/SQL package API. For example, you can create a function called GET_EMPLOYEES that returns a SYS_REFCURSOR containing the employees in a specific department:

function get_employees (p_deptno in number) return sys_refcursor
as
l_returnvalue sys_refcursor;
begin

open l_returnvalue
for
select empno, ename, job, sal
from emp
where deptno = p_deptno;

return l_returnvalue;

end get_employees;

The client (an application written in Java, .NET, PHP, etc.) can call your API and process the returned REF Cursor just as if it was a normal result set from a SQL query. The benefits are legion. The client no longer needs to contain embedded SQL statements, or indeed know anything about the actual database structure and query text. Privileges on the underlying tables can be revoked. The API can be shared and reused among different clients, whether they are written in Java, .NET, or any number of other languages.

That is, unless your client is Oracle Application Express (Apex). Apex unfortunately lacks the ability to process REF Cursors, or, more accurately, you cannot create report regions in Apex based on REF Cursors. For standard reports, you have to either embed the SQL statement in the region definition, or return the SQL text string from a function (and hope that the string you built is valid SQL when it gets executed). For interactive reports, only embedded SQL statements are supported.

I dislike having to scatter literal SQL statements all around my Apex applications, and not be able to take advantage of a package-based, shared and reusable PL/SQL API to encapsulate queries. I submitted a feature request to the Apex team back in 2007, asking for the ability to base report regions on REF Cursors, but so far this has not been implemented.

The problem, as far as I know, is that Apex uses (and must use) DBMS_SQL to "describe" a SQL statement in order to get the metadata (column names, data types, etc.) for a report region. But not until Oracle 11g did DBMS_SQL include a function (TO_CURSOR_NUMBER) that allows you to convert a REF Cursor into a DBMS_SQL cursor handle. So, as long as the minimum supported database version for Apex is Oracle 10g, support for REF Cursors is unlikely to be implemented.

In the meantime, there are a couple of alternatives:

Option 1: Pipelined functions

It's possible to encapsulate your queries behind a PL/SQL API by using pipelined functions. For example, the above example could be rewritten as...

create type t_employee as object (
empno number(4),
ename varchar2(10),
job varchar2(9),
sal number
);

create type t_employee_tab as table of t_employee;

function get_employees (p_deptno in number) return t_employee_tab pipelined
as
begin

for l_rec in (select empno, ename, job, sal from emp where deptno = p_deptno) loop
pipe row (t_employee (l_rec.empno, l_rec.ename, l_rec.job, l_rec.sal));
end loop;

return;

end get_employees;

And used from Apex (in a report region) via the TABLE statement:

select *
from table(employee_pkg.get_employees (:p1_deptno))

Option 2: XML from REF Cursor

The DBMS_XMLGEN package can generate XML based on a REF Cursor. While this does not "describe" the REF Cursor per se, it does give us a way (from PL/SQL) to find the column names of an arbitrary REF Cursor query, and perhaps infer the data types from the data itself. A couple of blog posts from Tom Kyte explain how this can be used to generate HTML based on a REF Cursor.

So back to Apex, you could generate a "report" based on a PL/SQL region with code similar to this:

declare
l_clob clob;
l_rc   sys_refcursor;
begin
l_rc := get_employees (:p1_deptno);
l_clob := fncRefCursor2HTML (l_rc);
htp_print_clob (l_clob);
end;

It would also be possible to pass your own XLST stylesheet into the conversion function (perhaps an Apex report region template fetched from the Apex data dictionary?) to control the appearance of the report.

I put "report" in quotes above, because until the Apex team implements report regions based on REF Cursors, you will miss all the nice built-in features of standard (and interactive) reports, such as sorting, paging, column formatting, linking, etc.

Option 3: JSON from REF Cursor

Bear with me, I am finally getting to the point of this blog post.

JSON is cool, too, just like REF Cursors. It's the fat-free alternative to XML, and JSON data is really easy to work with in Javascript.

For triple coolness, I want to use an API based on REF Cursors in PL/SQL, client-side data manipulation based on JSON, and Apex to glue the two together.

What I need is the ability to generate JSON based on a REF Cursor.

Apex does include a few JSON-related procedures in the APEX_UTIL package, including JSON_FROM_SQL. Although this procedure does support bind variables, it cannot generate JSON from a REF Cursor. (Also, the fact that is is a procedure rather than a function makes it less flexible than it could be. Dear Apex Team, can we please have overloaded (function) versions of these JSON procedures?)

REF Cursor to JSON: The (10g) solution

So I came up with this solution: Use DBMS_XMLGEN to generate XML based on a REF Cursor, and then transform the XML into JSON by using an XSLT stylesheet.

Note: As mentioned above, in Oracle 11g you can use DBMS_SQL to describe a REF Cursor, so you could write your own function to generate JSON from a REF Cursor, without going through XML first. (And perhaps in Oracle 12g the powers that be at Redwood Shores will provide us with a built-in DBMS_JSON package that can both generate and parse JSON?)

In the meantime, for Oracle 10g, I created the JSON_UTIL_PKG package.

Here is the code for the REF_CURSOR_TO_JSON function:

function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
p_max_rows in number := null,
p_skip_rows in number := null) return clob
as
l_ctx         dbms_xmlgen.ctxhandle;
l_num_rows    pls_integer;
l_xml         xmltype;
l_json        xmltype;
l_returnvalue clob;
begin

/*

Purpose:    generate JSON from REF Cursor

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     30.01.2010  Created

*/

l_ctx := dbms_xmlgen.newcontext (p_ref_cursor);

dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);

-- for pagination
if p_max_rows is not null then
dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
end if;

if p_skip_rows is not null then
dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
end if;

-- get the XML content
l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);

l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);

dbms_xmlgen.closecontext (l_ctx);

close p_ref_cursor;

if l_num_rows > 0 then
-- perform the XSL transformation
l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
l_returnvalue := l_json.getclobval();
else
l_returnvalue := g_json_null_object;
end if;

l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);

return l_returnvalue;

end ref_cursor_to_json;

Examples of usage

Get a small dataset

declare
l_clob clob;
l_cursor sys_refcursor;
begin
l_cursor := employee_pkg.get_employees (10);
l_clob := json_util_pkg.ref_cursor_to_json (l_cursor);
dbms_output.put_line (substr(l_clob, 1, 200));
end;

{"ROWSET":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10}]}

A large dataset, with paging

declare
l_clob clob;
l_cursor sys_refcursor;
begin
l_cursor := test_pkg.get_all_objects;
l_clob := json_util_pkg.ref_cursor_to_json (l_cursor, p_max_rows => 3, p_skip_rows => 5000);
dbms_output.put_line (substr(l_clob, 1, 1000));
end;


{"ROWSET":[{"OBJECT_ID":5660,"OBJECT_NAME":"LOGMNRT_SEED$","OBJECT_TYPE":"TABLE","LAST_DDL_TIME":"07.02.2006"},{"OBJECT_ID":5661,"OBJECT_NAME":"LOGMNRT_MDDL$","OBJECT_TYPE":"TABLE","LAST_DDL_TIME":"07.02.2006"},{"OBJECT_ID":5662,"OBJECT_NAME":"LOGMNRT_MDDL$_PK","OBJECT_TYPE":"INDEX","LAST_DDL_TIME":"07.02.2006"}]}

It works with nested datasets, too.. !

select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d

declare
l_json clob;
begin
l_json := json_util_pkg.sql_to_json ('select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d');
dbms_output.put_line (substr(l_json, 1, 10000));
end;

{"ROWSET":[{"DEPTNO":10,"DNAME":"ACCOUNTING",
"THE_EMPS":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},
{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},
{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10}]},
{"DEPTNO":20,"DNAME":"RESEARCH",
"THE_EMPS":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"SALESMAN","MGR":7902,"HIREDATE":"17.12.1980","SAL":880,"COMM":null,"DEPTNO":20},
{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02.04.1981","SAL":2975,"COMM":null,"DEPTNO":20},
{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09.12.1982","SAL":3000,"COMM":null,"DEPTNO":20},
{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12.01.1983","SAL":1100,"COMM":null,"DEPTNO":20},
{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03.12.1981","SAL":3000,"COMM":null,"DEPTNO":20},
{"EMPNO":9999,"ENAME":"BRATEN","JOB":"CLERK","MGR":7902,"HIREDATE":"05.05.2009","SAL":1000,"COMM":null,"DEPTNO":20},
{"EMPNO":9998,"ENAME":"DOE","JOB":"CLERK","MGR":7902,"HIREDATE":"25.04.2009","SAL":500,"COMM":null,"DEPTNO":20}]},
{"DEPTNO":30,"DNAME":"SALES",
"THE_EMPS":[{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20.02.1981","SAL":1600,"COMM":300,"DEPTNO":30},
{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22.02.1981","SAL":3200,"COMM":500,"DEPTNO":30},
{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28.09.1981","SAL":1250,"COMM":1400,"DEPTNO":30},
{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01.05.1981","SAL":2850,"COMM":null,"DEPTNO":30},
{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08.09.1981","SAL":1500,"COMM":0,"DEPTNO":30},
{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7788,"HIREDATE":"03.12.1981","SAL":950,"COMM":null,"DEPTNO":30}]},
{"DEPTNO":40,"DNAME":"OPERATIONS",
"THE_EMPS":null}]}

Passing a REF Cursor directly to the function call by using the CURSOR function:

select json_util_pkg.ref_cursor_to_json(cursor(select * from emp))
from dual

{"ROWSET":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"SALESMAN","MGR":7902,"HIREDATE":"17.12.1980","SAL":880,"COMM":null,"DEPTNO":20},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20.02.1981","SAL":1600,"COMM":300,"DEPTNO":30},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22.02.1981","SAL":3200,"COMM":500,"DEPTNO":30},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02.04.1981","SAL":2975,"COMM":null,"DEPTNO":20},{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28.09.1981","SAL":1250,"COMM":1400,"DEPTNO":30},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01.05.1981","SAL":2850,"COMM":null,"DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09.12.1982","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08.09.1981","SAL":1500,"COMM":0,"DEPTNO":30},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12.01.1983","SAL":1100,"COMM":null,"DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7788,"HIREDATE":"03.12.1981","SAL":950,"COMM":null,"DEPTNO":30},{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03.12.1981","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7934,"ENAME":"MILLERø","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10},{"EMPNO":9999,"ENAME":"BRATEN","JOB":"CLERK","MGR":7902,"HIREDATE":"05.05.2009","SAL":1000,"COMM":null,"DEPTNO":20},{"EMPNO":9998,"ENAME":"DOE","JOB":"CLERK","MGR":7902,"HIREDATE":"25.04.2009","SAL":500,"COMM":null,"DEPTNO":20}]}

Download the package

You can download the complete package, including the XSLT stylsheet, here (spec) and here (body).

Update 12.02.2011: This package can now be downloaded as part of the Alexandria library for PL/SQL.

Note that to compile the packages you need the following SQL type defined in your schema:

create type t_str_array as table of varchar2(4000);
/

My first Apex 4 plugin: Flight Info from Web Service

One of the exciting new features in Apex 4 is the support for plugin regions and items. This feature has huge potential, and will make development with Apex even more efficient, productive, and fun. There are already several plugins out there, and I think we will see a lot of interesting work in this area after Apex 4 is released.

Here is my own first attempt at a (useful) plugin: A region plugin that displays up-to-date flight information for airports in Norway, based on public flight data provided by Avinor, the company that operates the Norwegian airport network.

Avinor has a simple web service that provides flight information in XML format.

I am sure there are similar (web) services for flight information in other countries (feel free to leave a comment below if you know of any).

Here is the PL/SQL code behind the plugin:

procedure render_my_plugin (
p_region              in apex_plugin.t_region,
p_plugin              in apex_plugin.t_plugin,
p_is_printer_friendly in boolean )
as
l_clob clob;
l_airport_code varchar2(20) := p_region.attribute_01;
l_direction    varchar2(20) := p_region.attribute_02;
begin

l_clob := apex_web_service.make_rest_request(
p_url => 'http://flydata.avinor.no/XmlFeed.asp',
p_http_method => 'GET',
p_parm_name => apex_util.string_to_table('airport:direction'),
p_parm_value => apex_util.string_to_table(l_airport_code || ':' || l_direction )
);

if l_direction = 'D' then
htp.p('<p><b>Departures from ' || l_airport_code || '</b></p>');
else
htp.p('<p><b>Arrivals to ' || l_airport_code || '</b></p>');
end if;

htp.p('<table width="100%">');
htp.p('<tr><td>AIRLINE</td><td>FLIGHT</td><td>AIRPORT</td><td>TIME</td><td>GATE</td></tr>');

for l_rec in (
SELECT *
FROM XMLTABLE ('//airport/flights/flight'
PASSING XMLTYPE(l_clob)
COLUMNS unique_id       varchar2(100) path '@uniqueID',
airline         varchar2(10) path 'airline',
flight_id       varchar2(20) path 'flight_id',
airport         varchar2(20) path 'airport',
schedule_time   varchar2(100) path 'schedule_time',
gate            varchar2(100) path 'gate')
ORDER BY airline, flight_id) loop

htp.p('<tr><td>' || l_rec.airline || '</td><td>' || l_rec.flight_id || '</td><td>' || l_rec.airport || '</td><td>' || l_rec.schedule_time || '</td><td>' || l_rec.gate || '</td></tr>');

end loop;

htp.p('</table>');

htp.p('<a href="http://www.avinor.no">Flight data from Avinor.</a> Last updated: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));


end render_my_plugin;

The code illustrates several concepts:

• How to render a region plugin using the PL/SQL Web Toolkit (HTP.P) calls
• How to retrieve values from the attributes defined for the plugin
• Using the new APEX_WEB_SERVICE.MAKE_REST_REQUEST function to retrieve a web page as a CLOB
• Using the XMLTABLE function to transform XML into a recordset that can be used in a SELECT

An export of my plugin can be downloaded here, and installed into your own Apex 4 application.

After the plugin has been installed, using the plugin is as simple as adding a Region (of type Plugin) to the page, and configuring the values for Airport and Direction (the plugin attributes) in the region definition.



You can see a live demo of the plugin here (public page, does not require authentication):

http://tryapexnow.com/apex/f?p=test4ea:plugin_demo:0

Note that for this page, I've also taken advantage of the built-in region caching feature of Apex. The region cache duration is set to 10 minutes, which prevents us from hitting the remote web service for every page view. I really like that you can switch on region caching in Apex without writing a single line of code.

Conclusion: Apex 4 plugins rock!

ODP.NET minimal, non-intrusive install

This might be of interest for those who use .NET to connect to Oracle databases. (Including yours truly, who wrote the Thoth Gateway, a mod_plsql replacement that runs on Microsoft IIS, using C# and ODP.NET.)

A while back, Microsoft officially deprecated their ADO.NET driver for Oracle (System.Data.OracleClient).

Fortunately, Oracle offers its own .NET driver, known as the Oracle Data Provider for .NET (ODP.NET). This driver is a better choice for Oracle connectivity, since it supports a wider range of Oracle-specific features, and improved performance.

However, ODP.NET, unlike, say, the thin JDBC drivers, still requires the normal Oracle client to be present on the machine. This Oracle client can be something of a beast, with the install package upwards of 200 megabytes. Couple this with the fact that you may have several diffent Oracle client versions installed on your machine (or application server), all specific to some application that you dare not touch for fear of it breaking.

A non-intrusive install

So, here is how you can use ODP.NET with the following advantages:

• Small footprint (between 30 and 100 megabytes)
• XCopy deployment
• No dependency on shared files, all files in your own application's folder
• No registry or system environment changes required
• No tnsnames.ora file required
• No interference from other Oracle client installs on the same machine

Sounds good, doesn't it? Let's see how this can be accomplished...

1. Download ODP.NET (xcopy version)

Download from here:

http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html

Unzip the file and locate the following 2 files:

• OraOps11w.dll
• Oracle.DataAccess.dll

Copy these files to your application's "bin" folder.

2. Download Oracle Instant Client

Download from here:

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

You have a choice between the following two versions of the Instant Client

a) Instant Client Basic (approx. 100 megabytes)

Unzip the file and locate the following 3 files:

• oci.dll
• orannzsbb11.dll
• oraociei11.dll

b) Instant Client Basic Lite (approx. 30 megabytes): This version is smaller but only supports certain character sets (WE8MSWIN1252 and AL32UTF8 are among them). It only has English messages, so in case you wonder what "ORA-06556: The pipe is empty" sounds like in your own language, go for the non-Lite version.

Unzip the file and locate the following 3 files:

• oci.dll
• orannzsbb11.dll
• oraociicus11.dll

Whichever version you choose, copy these files to your application's "bin" folder. You now have a total of 5 new files in your "bin" folder.

3. Connection string

In your .NET program, use a connect string in the following format, to make sure you don't need to rely on any network configuration files (tnsnames.ora, etc.).

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=database_host_name)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=database_service_name)))

4. Configuration

This is mostly relevant if you have other Oracle client installations already on the same machine/server.

In your configuration file (web.config), you can explicitly set the path to the Oracle DLLs you want to use. Set the "DllPath" parameter to the name of your "bin" folder.

<configuration>
<oracle.dataaccess.client>
<settings>
<add name="DllPath" value="c:\my_app_folder\bin"></add>
<add name="FetchSize" value="65536"></add>
<add name="PromotableTransaction" value="promotable"></add>
<add name="StatementCacheSize" value="10"></add>
<add name="TraceFileName" value="c:\temp\odpnet2.log"></add>
<add name="TraceLevel" value="0"></add>
<add name="TraceOption" value="0"></add>
</settings>
</oracle.dataaccess.client>
</configuration>

5. That's it!

You should now be able to run your ODP.NET application from your "bin" folder.



References

• Installing Oracle Data Provider for .NET
• ODP.NET Configuration
• http://alderprogs.blogspot.com/2009/04/deploying-odpnet-with-oracle-instant.html

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 :-)