Hi What i meant to ask was not just for characters. to put a broader scene, i want to store say the statement "This is a problem in query" this statement must appear with proper grammer and syntax in the other five languages! how do i acheive this without creating seperate tables/ database is my main concern as we do not wish to maintain seperate tables/ databases for the same.
If the messages you wish translated are your own, and depending on the amount of data you have you could consider adding a language column to each table ('GB','DE') or whatever and creating views on the tables for reading data. Hold a package variable for the language.
It's pseudocode so you'll have to work out the details/syntax....
create or replace package utils is
LangCode VARCHAR(2) := 'GB'; -- GB is default for new connections
procedure SetLanguage( inLangCode .... )
function GetLanguage return varchar2 ...
pragma restrict_references( SetLanguage.... )
pragma restrict_references( GetLanguage.... )
create or replace view MyTableLang as
where languagecode = utils.GetLanguage;
create or replace trigger MyTableIns_Trg
before insert on MyTable
for each row
:new.Languagecode := utils.GetLanguage;
In this example GB is always the default language but any session can call utils.SetLanguage to change it.
Your table "MyTable" may look something like
MCODE LANGUAGE MESSAGE
100 'GB' An error
100 'DE' Ein fehler
101 'GB' Hello
101 'DE' Hallo
Your old primary key of MCODE would become (MCODE,LANGUAGE)
You'll have to consider/check performance issues for any large tables etc, although if your selects are generally returning small datasets it shouldn't be an issue.