Home » Infrastructure » Windows » ORA-29270: too many open HTTP requests (Oracle 11g)
ORA-29270: too many open HTTP requests [message #565823] Thu, 06 September 2012 23:04 Go to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
Hi,

We have a requirement like, we will get bulk data (2 GB) data file with different languages(Russian, chinese, Japanese, German) etc. and we need to load these data into our databse also we have to translate the same.

We are using Oracle 11g.

We had used Oracle httpuritype - And the below mentioned procedure PROCEDURE TEST.PRC_TRANS_GERMAN will call the function - trans_jef

Input parameter for PRC_TRANS_GERMAN - > To_language and from Language. and this procedure will store the translated word in the seperate table.
Input parameter for the function trans_jef - > Word to be translated , from languae, to_language. And this function will return the translated word.

The issue I am facing is when I am passing the table of around 10000 records, around 4000 rows are not getting translated. So when i try to capture the error in exception- the error is


ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1819
ORA-29270: too many open HTTP requests

When I try to re execute the missed words, they were translated. But they are not getting translated in one go because of the too many http open request.

So could you please let me know how to resolve this ORA-29270 errror.


Translator
/* 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;
/

[Updated on: Thu, 06 September 2012 23:59] by Moderator

Report message to a moderator

Re: ORA-29270: too many open HTTP requests [message #565830 is a reply to message #565823] Thu, 06 September 2012 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-29270: too many open HTTP requests
 *Cause:  Too many HTTP requests were opened.
 *Action: End some HTTP requests and retry the HTTP request.

Regards
Michel
Re: ORA-29270: too many open HTTP requests [message #565832 is a reply to message #565830] Fri, 07 September 2012 01:00 Go to previous message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
Michel, If you could see the trans_jef function- i had just called the httpuritype. And since I am new to using http in oracle could you please advise me how to end the request and retry the request in trans_jef function. Thanks
Previous Topic: MS Project to Oracle Database
Next Topic: OPTIONALLY ENCLOSED BY
Goto Forum:
  


Current Time: Thu Mar 28 14:11:19 CDT 2024