1 2 Previous Next 24 Replies Latest reply on Sep 29, 2011 10:04 AM by MichaelS

    Language Change Through PL/SQL

    San
      Hi,

      I have a requirment like in my table i have data's in English Language, When i fetched the data i have to convert into other language and show it to my screen.

      Is it possible to do through PL/SQL .???

      Can anyone give me a suggestion on this??


      Cheers,
      Shan

      Edited by: Shan on 31 Aug, 2010 3:53 PM
        • 1. Re: Language Change Through PL/SQL
          Billy~Verreynne
          Shan wrote:

          I have a requirment like in my table i have data's in English Language,
          What data? Text? Dates? Currency? Something else?
          When i fetched the data i have to convert into other language and show it to my screen.
          What other language(s)? How does the Oracle server side determine what language to display data in?
          Is it possible to do through PL/SQL .???
          That depends on the problem definition - which is still lacking. But PL/SQL is as capable as any other language in this regard.
          Can anyone give me a suggestion on this??
          Well, first detail the exact problem description. What needs to be converted? What languages? Are you going to use a service/3rd party s/w to perform the language conversion for you? Or do you have conversion tables in Oracle? Are you referring to the display of column names and text only and not the actual database data (e.g. localisation of the user interface)?
          • 2. Re: Language Change Through PL/SQL
            Lokanath Giri
            Should be possible!!!
              
              DECLARE
                BEGIN
                execute immediate 'ALTER SESSION SET NLS_LANGUAGE= '||'''GERMAN''';
                DBMS_OUTPUT.PUT_LINE(sysdate+100);
               end;
            SQL> /
            09-DEZ-10
            
            PL/SQL procedure successfully completed.
            
            SQL> ed
            Wrote file afiedt.buf
            
                DECLARE
                BEGIN
                execute immediate 'ALTER SESSION SET NLS_LANGUAGE= '||'''AMERICAN''';
                DBMS_OUTPUT.PUT_LINE(sysdate+100);
               end;
            /
            09-DEC-10
            
            PL/SQL procedure successfully completed.
            1 person found this helpful
            • 3. Re: Language Change Through PL/SQL
              San
              Hi Billy,

              Only Text's i want to convert.

              I want to convert into Hindhi its an Indian Language.

              I am going to display a report in BI publisher. there is no 3rd party software to convert. direclty i have to convert through PL/SQL procedure.

              We are not maintaining any conversion table.

              ex: select * from emp;

              It will give the output like

              Empno Empname
              1 shan
              2 Raja

              Now i want to convert that shan and raja inti Hindhi language...



              Cheers,
              Shan
              • 4. Re: Language Change Through PL/SQL
                San
                Hi Lokanth,

                Is it possible change it into Hindhi . Its an Indian language?

                Cheers,
                Shan
                • 5. Re: Language Change Through PL/SQL
                  Lokanath Giri
                  Please log this issue in BI section.

                  There are lots on example available in oracle site on BI-Publisher you can refer that. You have to create some XML files where you can keep diff. translations.

                  Refer this link
                  http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html

                  Edited by: Lokanath Giri on ३१ अगस्त, २०१० ४:१४ अपराह्न
                  • 6. Re: Language Change Through PL/SQL
                    San
                    Hi,

                    I know that it is possible through BI publisher .

                    But i want to know is it possible to do this in PL/SQl???

                    Cheers,
                    Shan
                    • 7. Re: Language Change Through PL/SQL
                      Billy~Verreynne
                      Shan wrote:

                      Only Text's i want to convert.
                      I want to convert into Hindhi its an Indian Language.
                      You can, from PL/SQL, use a translation service. This example uses Google:
                      SQL> create or replace function TranslateToFrench( sentence varchar2 ) return varchar2 is
                        2          buffer  clob;
                        3          line    varchar2(32767);
                        4          i1      integer;
                        5          i2      integer;
                        6  begin
                        7          --make a http call to get the html page result as a clob
                        8          buffer :=  WebClob('http://translate.google.co.za/translate_t?hl=&ie=UTF-8&text=~'||replace(sentence,' ','+')||'~&sl=en&tl=fr#');
                        9  
                       10          -- ugly hack to find the translated sentence (should be done using regexp_substr)
                       11          i1 := instr( buffer, '<textarea name=utrans' );
                       12          line := substr( buffer, i1, 1000 );
                       13          i1 := instr( line, '~', 1 ) +1;
                       14          i2 := instr( line, '~', -1 ) -1;
                       15  
                       16          -- returns the translated sentence
                       17          return(
                       18                  trim( substr( line, i1, i2-i1 ) )
                       19          );
                       20  end;
                       21  /
                      
                      Function created.
                      
                      SQL> show errors
                      No errors.
                      SQL> 
                      SQL> select TranslateToFrench( 'The chicken crosses the road' ) as FRENCH from dual;
                      
                      FRENCH
                      --------------------------------------------------
                      Le poulet traverse la route
                      SQL> 
                      The WebClob function is base on the source code I posted in {message:id=4205205} - with the only difference being returning the HTTP response as a CLOB and not XML.
                      • 8. Re: Language Change Through PL/SQL
                        Rob van Wijk
                        Great example, Billy! Thanks.
                        • 9. Re: Language Change Through PL/SQL
                          Billy~Verreynne
                          But broken - as translate.google.com seems to change HTML output dynamically every now and then. I do not see the same HTML data stream today for the same URL that I did yesterday.

                          So one will need to use regular expressions (and not my instr/substr hack) to deal with the dynamic response from Google. And yes, I am regular expression challenged.. ;-)
                          • 10. Re: Language Change Through PL/SQL
                            Rob van Wijk
                            Billy  Verreynne  wrote:
                            But broken - as translate.google.com seems to change HTML output dynamically every now and then. I do not see the same HTML data stream today for the same URL that I did yesterday.

                            So one will need to use regular expressions (and not my instr/substr hack) to deal with the dynamic response from Google. And yes, I am regular expression challenged.. ;-)
                            That's too bad. But you know where to put your questions if you have a regular expression challenge ;-)
                            • 11. Re: Language Change Through PL/SQL
                              MichaelS
                              But broken - as translate.google.com seems to change HTML output dynamically every now and then
                              Better to use google's translate api then:
                              SQL> var q varchar2(100)
                              
                              SQL> exec :q := utl_url.escape('The chicken crossed the road')
                              PL/SQL procedure successfully completed.
                              
                              SQL> select :q q, 'french' lang, regexp_substr(httpuritype ('http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q='||:q||'&langpair=en|fr').getclob (),'".*?"' ,1,3) trans  from dual union all
                              select :q q, 'spanish', regexp_substr(httpuritype ('http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q='||:q||'&langpair=en|es').getclob (),'".*?"' ,1,3) trans from dual union all
                              select :q q, 'german', regexp_substr(httpuritype ('http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q='||:q||'&langpair=en|de').getclob (),'".*?"' ,1,3) trans from dual union all
                              select :q q, 'netherland', regexp_substr(httpuritype ('http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q='||:q||'&langpair=en|nl').getclob (),'".*?"' ,1,3) trans from dual
                              /
                              Q                                                  LANG       TRANS                                                         
                              -------------------------------------------------- ---------- --------------------------------------------------------------
                              The%20chicken%20crossed%20the%20road               french     "Le poulet a traversé la route"                               
                              The%20chicken%20crossed%20the%20road               spanish    "El pollo cruzó la carretera"                                 
                              The%20chicken%20crossed%20the%20road               german     "Das Huhn überquerte die Straße"                              
                              The%20chicken%20crossed%20the%20road               netherland "De kip stak de weg over"                                     
                              
                              4 rows selected.
                              Much easier to parse ;)
                              • 12. Re: Language Change Through PL/SQL
                                Rob van Wijk
                                Excellent! And indeed much easier.
                                • 13. Re: Language Change Through PL/SQL
                                  Billy~Verreynne
                                  MichaelS wrote:
                                  But broken - as translate.google.com seems to change HTML output dynamically every now and then
                                  Better to use google's translate api then:
                                  Ah.. I was wondering about where the API reference info was and did not spot it on the translate.google.com page - and so used to having to parse HTML responses (e.g. no nice APIs when for example extract data via HTTP from IP phones) that I quickly hacked a parsed response from the translation page.

                                  Thanks for the example Michael - this one is definitely being filed for future reference (together with a bunch of others you've posted through the years). :-)
                                  • 14. Re: Language Change Through PL/SQL
                                    824763
                                    Could you please provide the SQL statement for Chinese Language?

                                    Regards
                                    Laj
                                    1 2 Previous Next