/* Formatted on 07-09-2012 09:19:33 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE FUNCTION trans_jef (v_RawText    IN VARCHAR2,
                                      v_SrcLang    IN VARCHAR2,
                                      v_DestLang   IN VARCHAR2)
   RETURN CLOB
IS
    v_EscRawText    VARCHAR2 (3000);
   v_XlateUrl      VARCHAR (3000);
   --v_SrcLang Varchar(10) ;-- := 'de';
   --v_DestLang Varchar(10) ; -- := 'en';
   v_XlateResult   CLOB;
   l_result        CLOB;
BEGIN
   DBMS_OUTPUT.ENABLE (10000000);
    v_EscRawText := UTL_URL.escape (v_RawText, TRUE, 'UTF-8');
   v_XlateUrl :=
         'http://translate.google.com/translate_a/t?client=t&text='
      || v_EscRawText
      || '&hl='
      || v_DestLang
      || '&sl='
      || v_SrcLang
      || '&tl='
      || v_DestLang
      || '&ie=UTF-8&oe=UTF-8&multires=1&oc=1&prev=btn&ssel=0&tsel=0&sc=1';
     v_XlateResult := httpuritype (v_XlateUrl).getclob ();
   -- dbms_output.put_line (v_XlateResult);
   l_result := TRIM (BOTH '"' FROM REGEXP_SUBSTR (v_XlateResult, '"[^"]+"'));
    RETURN l_result;
END;
/

--------------------------------------------------------------------------------------

/* Formatted on 07-09-2012 09:21:05 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE PROCEDURE TEST.PRC_TRANS_GERMAN (TO_LANG1     IN VARCHAR2,
                                                   FROM_LANG1   IN VARCHAR2)
IS
   P_CHINESE         VARCHAR2 (3000);
   FROM_LANG         VARCHAR2 (3000);
   FROM_LANG_CODE    VARCHAR2 (3000);
   TO_LANG_CODE      VARCHAR2 (3000);
   FROM_LANG_CODE1   VARCHAR2 (3000);
   TO_LANG_CODE1     VARCHAR2 (3000);
   cnt               NUMBER := 1;
   counter           NUMBER;

   CURSOR C1
   IS
      SELECT RAW_VAL_TXT FROM T_TRANS_VAL;
 
   VC1               C1%ROWTYPE;

BEGIN
   OPEN C1;

   LOOP
      FETCH C1 INTO VC1;

      EXIT WHEN C1%NOTFOUND;

      BEGIN
 
         TO_LANG_CODE :=
            CASE TO_LANG1
               WHEN 'English' THEN 'en'
               WHEN 'Chinese Simplified' THEN 'zh-CN'
               WHEN 'Chinese Tradational' THEN 'zh-TW'
               WHEN 'German' THEN 'de'
               WHEN 'Japanese' THEN 'ja'
            --other languages

            END;
         FROM_LANG_CODE :=
            CASE FROM_LANG1
               WHEN 'English' THEN 'en'
               WHEN 'Chinese Simplified' THEN 'zh-CN'
               WHEN 'Chinese Tradational' THEN 'zh-TW'
               WHEN 'German' THEN 'de'
               WHEN 'Japanese' THEN 'ja'
            /*
            other languages
            */
            --                 else 'auto'
            END;
 
         SELECT trans_jef (VC1.RAW_VAL_TXT, FROM_LANG_CODE, TO_LANG_CODE)
           INTO P_CHINESE
           FROM DUAL; 
-- same translator function is used. Language used is simplied chinese. 
-- We can use traditional Chinese as well ('zh-TW'), which works perfectly.

  
         FROM_LANG_CODE1 := --decode(from_lang,'en','English','ar','Arabic', from_lang);
            CASE FROM_LANG_CODE
               WHEN 'en' THEN 'English'
               WHEN 'zh-CN' THEN 'Chinese Simplified'
               WHEN 'zh-TW' THEN 'Chinese Tradational'
               WHEN 'de' THEN 'German'
               WHEN 'ja' THEN 'Japanese'
            --other languages

            END;

         TO_LANG_CODE1 := --decode(from_lang,'en','English','ar','Arabic', from_lang);
            CASE TO_LANG_CODE
               WHEN 'en' THEN 'English'
               WHEN 'zh-CN' THEN 'Chinese Simplified'
               WHEN 'zh-TW' THEN 'Chinese Tradational'
               WHEN 'de' THEN 'German'
               WHEN 'ja' THEN 'Japanese'
            --  other languages

            END;
 
         INSERT INTO t_trans
              VALUES (FROM_LANG_CODE1,
                      VC1.RAW_VAL_TXT,
                      TO_LANG_CODE1,
                      P_CHINESE);

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
             INSERT INTO excep_trans (P_WORD_EXP, UPDATE_TIME, COUNTER)
                 VALUES (VC1.RAW_VAL_TXT, SYSDATE, cnt);

            INSERT INTO error_message
                 VALUES (DBMS_UTILITY.FORMAT_ERROR_STACK);

             COMMIT;
      END;
    END LOOP;

   CLOSE C1;
 
END;
/