Import file from a remote database using utl_http

Peter Goldthorp June 2002

The following code uses utl_http to call a procedure in a remote database and insert the contents into a local table. The remote procedure returns an xml file. The file may be long so a temporary lob is created to hold it. When all the data has been retrieved the contents of the file is written to an xmltype column in a table called xml_load_data.

This program is based on an example in the Oracle doc for utl_http. It uses an exeption (utl_http.end_of_body) to break out of the loop.

DECLARE
  req          utl_http.req;
  resp         utl_http.resp;
  value        VARCHAR2(1024);
  temp_clob     clob;
  v_first      boolean := TRUE;

BEGIN
    dbms_lob.createtemporary(temp_clob, TRUE, dbms_lob.call);
    dbms_lob.open(temp_clob, dbms_lob.lob_readwrite);

--  utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com');

  req := utl_http.begin_request('http://devsrv.goldthorp.com:7777/pls/casexml.xml_export');
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
  resp := utl_http.get_response(req);
  LOOP
    utl_http.read_line(resp, value, TRUE);

    if v_first then
       v_first := FALSE;
       dbms_lob.write(temp_clob, length(value), 1, value);
    else
       dbms_lob.writeappend(temp_clob,length(value),value);
    end if;

  END LOOP;
  utl_http.end_response(resp);
  dbms_lob.close(temp_clob);
  dbms_lob.freetemporary(temp_clob);

 EXCEPTION
   WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
  dbms_output.put_line('end loop');
  dbms_output.put_line(dbms_lob.getlength(temp_clob));
  insert into xml_load_data values (xml_element_seq.nextval, sys.XMLType.createXML(temp_clob));

  dbms_lob.close(temp_clob);
  dbms_lob.freetemporary(temp_clob);
END;
/