Besides the application labels, there is more to translate in an application. Data for instance.
In this recipe, we will see an example of this. To accomplish this, we will use a built-in way of translating the session language that is new in APEX 4.0.
Start by creating some new database objects. Remember that this is a crude setup. In a production environment, this should be more elaborate.
First, we will create a copy of the EMP table, but with a change. The JOB column will now be called JOB_NO and it's content will reference to a EMP_JOB_TITLES table.
create table EMP_LANG ( empno NUMBER(4) not null, ename VARCHAR2(10), job_no NUMBER, mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) );
[emp_lang.sql]
Also create the EMP_JOB_TITLES table. This table contains a LANGUAGE column that will hold the language in which the job_title is entered for that row.
This also means that job_no is not a unique column in this table, but job_no in combination with language is the unique key.
create table EMP_JOB_TITLES data translation,in applicationstarting with( job_no NUMBER, job_title VARCHAR2(32), language VARCHAR2(10) );
[emp_job_titles.sql]
Next, it will need data. First the EMP_JOB_TITLES. This script will fill the table with the original 5 job titles from the EMP table with language 'en' and add 5 translations in Dutch for the same titles.
insert into EMP_JOB_TITLES (job_no, job_title, language) values (1, 'PRESIDENT', 'en'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (2, 'MANAGER', 'en'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (3, 'ANALYST', 'en'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (4, 'CLERK', 'en'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (5, 'SALESMAN', 'en'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (1, 'Directeur', 'nl'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (2, 'Manager', 'nl'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (3, 'Analist', 'nl'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (4, 'Klerk', 'nl'), insert into EMP_JOB_TITLES (job_no, job_title, language) values (5, 'Verkoper', 'nl'),
[emp_job_titles_data.sql]
For the EMP_LANG table, copy the data from the EMP table, but replace the contents of the JOB column with the reference number as it was entered in the EMP_JOB_TITLES table (for example; PRESIDENT will become 1, CLERK will become 4).
Some examples of this data are:
insert into EMP_LANG (empno, ename, job_no, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 1, null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP_LANG (empno, ename, job_no, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 2, 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
[emp_lang_data.sql]
The emp_lang_data.sql script contains 12 more rows.
When there is a datamodel available that allows data to be saved in multiple languages, the hardest part is writing smart queries to get this data out in the right language for the user. We will create a page based on such a query for our simple datamodel.
The first step is getting the application ready. To use the Session option, take the following steps.
- Go to the Application Properties.
- Go to the Globalization tab.
- In the select list Application Language Derived From select Session.
- Click Apply Changes.
Next step is to create a page based on a language-driven query.
- On the application overview, click Create Page.
- Select Report and click Next.
- Select Classic Report and click Next.
- Enter a number and name for the page and click Next.
- Select Do Not Use Tabs and click Next.
- Enter the query that will drive the Page.
select emp.empno , emp.ename , job.job_title , emp.hiredate , emp.sal from emp_lang emp , emp_job_titles job where emp.job_no = job.job_no and upper(job.language) = upper(apex_util.get_session_lang)
- Click Next a few times until the Finish button appears and click it.
To test the changes, Run the page by altering the URL to accept a p_lang parameter like so:
For the Dutch language, enter the URL like this:
http://server:port/apex/f?p=app_id:page_id:session&p_lang=nl
Using the Session option for Globalization, allows developers to take advantage of some additional built-in functions and procedures.
apex_util.set_session_lang(p_lang in varchar2)- This procedure will set the language for the session to the value in the parameter
apex_util.get_session_lang- This function will get the current session language. It can also be called using the variable v('BROWSER_LANGUAGE')
apex_util.reset_session_lang- This procedure will clear the session language
In this recipe we took advantage of the apex_util.get_session_lang procedure to retrieve the correct job titles in our report. It doesn't take a lot of imagination to see the possibilities of this kind of construction. We have now seen how to put translatable data into a separate table and making it unique by combining an ID column like the JOB_NO column in our example to a LANGUAGE column.
Of course this can be used for any kind of data. Just remember to keep a keen eye on the performance