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