DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: utl_http transfer time out issue

  1. #1
    Join Date
    Aug 2008
    Posts
    0

    utl_http transfer time out issue

    Hi All,

    I am trying to upload a file to the web server using oracle's utl_http package and I am getting a oracle error message "ORA - 29276 transfer timeout". In this process the data gets uploaded (I am saying because I have see the data by logging on to the server) and also I have seen the response sent by web server when I log onto the web server. The issue arises when I check for the response using utl_http.get_response procedure in my scripts that is when I get the transfer timeout error. I tried increasing the timeout value, but no use. The process waits utile the set time and errors out with the above message. If you have any ideas please share with me.

    Following is the code that I am using to upload a file
    DECLARE
    -- Created a Directory called "READ_LOB_DIR" pointing it to the actual file location
    l_dir CONSTANT VARCHAR2(30) := 'READ_LOB_DIR'; -- Input File
    l_fil CONSTANT VARCHAR2(30) := 'abc.txt'; -- Pointer to the BFILE
    l_loc BFILE; -- Current position in the file (file begins at position 1)
    l_pos NUMBER := 1; -- Amount of characters have been read
    l_sum BINARY_INTEGER := 32000; -- Read Buffer
    l_buf VARCHAR2(32767); -- Return value
    l_ret BOOLEAN := FALSE;
    clobSize INTEGER;
    req1 utl_http.req;
    resp utl_http.resp;
    value VARCHAR2(4000);
    l_input_file utl_file.file_type;
    l_string varchar2(4000);
    l_filename varchar2(100) := 'abc.txt';
    name varchar2(255);
    l_end_string varchar2(4000) := NULL;
    timeout NUMBER := 2700;
    -- l_post_data VARCHAR2(4000)
    l_post_data LONG := '--Bound' || 'sample data1 :' || chr(13) || chr(10) ||
    'sample data21 :' || chr(13) || chr(10) ||
    'sample data3 :';
    l number;
    BEGIN
    req1 := utl_http.begin_request('http://web.testwebserver.com',
    'POST',
    'HTTP/1.0');
    utl_http.set_header(req1, 'User-Agent', 'Mozilla/4.0');
    dbms_output.put_line('LENGTH :' || length(l_post_data));
    l_end_string := chr(13) || chr(10) || '--Bound' || chr(13) || chr(10);

    l := length(l_post_data) + length(l_end_string) + length(l_filename);

    utl_http.set_header(req1, 'Content-Length', 3 * l);
    utl_http.set_header(req1,
    'Content-Type',
    'multipart/form-data;boundary=Bound');
    utl_http.write_line(req1, l_post_data);

    -- Mapping the physical file with the pointer to the BFILE
    l_loc := BFILENAME(l_dir, l_fil);

    -- Check if the file exists
    l_ret := DBMS_LOB.fileexists(l_loc) = 1;
    IF (l_ret) THEN
    DBMS_OUTPUT.put_line('File ' || l_fil || ' in Directory ' || l_dir ||
    ' exists');

    -- Open the file in READ_ONLY mode
    DBMS_LOB.OPEN(l_loc, DBMS_LOB.lob_readonly);

    --Get the File Size
    clobSize := dbms_lob.getlength(l_loc);
    dbms_output.put_line('clobsize ' || to_char(clobsize));

    --Get the data from file in chunks of 32K
    DBMS_LOB.READ(l_loc, clobSize, l_pos, l_buf);
    UTL_HTTP.SET_TRANSFER_TIMEOUT(timeout);
    utl_http.write_line(req1, UTL_RAW.CAST_TO_VARCHAR2(l_buf));
    dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
    DBMS_LOB.CLOSE(l_loc);
    ELSE
    DBMS_OUTPUT.put_line('File ' || l_fil || ' in Directory ' || l_dir ||
    ' does not exist');
    END IF;
    dbms_output.put_line('Completed Sending Data .....');

    DBMS_LOB.CLOSE(l_loc);
    resp := utl_http.get_response(req1);

    --Get the Response
    --UTL_HTTP.SET_TRANSFER_TIMEOUT(timeout);
    FOR i IN 1 .. utl_http.get_header_count(resp) LOOP
    utl_http.get_header(resp, i, name, value);
    dbms_output.put_line(name || ': ' || value);
    END LOOP;

    --End Response
    utl_http.end_response(resp);
    EXCEPTION
    WHEN utl_http.end_of_body THEN
    Dbms_Output.Put_Line('Request_Failed 1: ' ||
    Utl_Http.Get_Detailed_Sqlerrm);
    WHEN OTHERS THEN
    Dbms_Output.Put_Line('Request_Failed 2: ' ||
    Utl_Http.Get_Detailed_Sqlerrm);
    DBMS_OUTPUT.put_line('Error:' || SQLERRM);
    DBMS_LOB.CLOSE(l_loc);
    END;


    RESULT
    ***********
    LENGTH :694
    File abc.txt in Directory READ_LOB_DIR exists
    clobsize 147
    Completed Sending Data ....
    Request_Failed 2: ORA-29276: transfer timeout
    Error:ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1222
    ORA-29276: transfer timeout

    PL/SQL procedure successfully completed.

    Thanks
    Kin

  2. #2
    Join Date
    Jul 2011
    Posts
    1

    reply to utl_http transfer timeout issue

    Dear Kin,

    I just ran into the same problem: posting a file goes well, but from time to time I get a timeout reading the response: this can lead to the incorrect conclusion that the file is not posted.

    In fact, looking into the code, this is not that surprising: the Utl_Http.Get_Response-command follows the write-operations, so inbetween connection can be broken resulting f.i. in a timout.

    I was wondering if you got any response to this yet, or any suggestions for making the procedure more robust.

    Thanks in advance,

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width