Translating Messages Used in PL/SQL Procedures

If your application includes PL/SQL regions or PL/SQL processes or calls PL/SQL package, procedures, or functions, you may need to translate generated HTML. First, you define each message on the Translatable Messages page. Second, you use the HTMLDB_LANG.MESSAGE API to translate the messages from PL/SQL stored procedures, functions, triggers, or packaged procedures and functions.

Defining Translatable Messages

You create translatable messages on the Translate Messages page.

To define a new translation message:

Navigate to the Translate Application page. See "Step 1: Navigate to the Translate Application Page".
On the Translate Application page, select Optionally translate messages which are used by PL/SQL procedures and functions.
On the Translate Messages page, click Create.
On the Identify Text Message page:
1. In Name, type a name to identify the text message
2. In Language, select the language for which the message would be used
3. In text, type the text to be returned when the text message is called.
  
  For example, you could define the message GREETING_MSG in English as:
```
Good morning %0
```
  Or, you could define the message GREETING_MSG in German as:
```
Guten Tag %0
```
Click Create.

HTMLDB_LANG.MESSAGE API

Use the HTMLDB_LANG.MESSAGE API to translate text strings (or messages) generated from PL/SQL stored procedures, functions, triggers, packaged procedures and functions.

Syntax

HTMLDB_LANG.MESSAGE (
    p_name    IN    VARCHAR2 DEFAULT NULL,
    p0        IN    VARCHAR2 DEFAULT NULL,
    p1        IN    VARCHAR2 DEFAULT NULL,
    p2        IN    VARCHAR2 DEFAULT NULL,
    ...
    p9        IN    VARCHAR2 DEFAULT NULL,
    p_lang    IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 15-2 describes the parameters available in the HTMLDB_LANG.MESSAGE.

Table 15-2 HTMLDB_LANG.MESSAGE Parameters

Parameter Description

Parameter	Description
`p_name`	Name of the message as defined in Oracle HTML DB.
`p0` ... `p9`	Dynamic substitution value. `p0` corresponds to 0% in the message. `p1` corresponds to 1% in the message. p2 corresponds to2% in the message and so on.
`p_lang`	Language code for the message to be retrieved. If not specified, Oracle HTML DB uses the current language for the user as defined in the Application Language Derived From attribute. See Also: "Specifying the Primary Language for an Application"

p_name

Name of the message as defined in Oracle HTML DB.

p0

...

p9

Dynamic substitution value. p0 corresponds to 0% in the message. p1 corresponds to 1% in the message. p2 corresponds to2% in the message and so on.

p_lang

Language code for the message to be retrieved. If not specified, Oracle HTML DB uses the current language for the user as defined in the Application Language Derived From attribute.

Example

The following example assumes you have defined a message called GREETING_MSG in your application in English as Good morning%0 and in German as Guten Tag%1. The following example demonstrates how you could invoke this message from PL/SQL:

BEGIN
    --
    -- Print the greeting
    --
    HTMLDB_LANG.MESSAGE('GREETING_MSG', V('APP_USER'));
END;

How p_lang attribute is defined depends on how the HTML DB engine derives the Application Primary Language. For example, if you are running the application in German and the previous call is made HTMLDB_LANG.MESSAGE, the HTML DB engine first looks for a message called GREETING_MSG with a LANG_CODE of de. If it does not find anything, then it will revert to the Application Primary Language attribute. If it still does not find anything, the HTML DB engine looks for a message by this name with a language code of en-us.