OCP Blog
Super DBA
Load XML file to oracle database in varchar2 type
by Vazha Mantua Wednesday, December 21, 2011 3:30 PM
    • Good Day all,
    • Today we show example of possibility of loading xml file to oracle10g database to text.
    • This method is very easy of getting some data from XML file to oracle database.
    • declare
    •   v_req   utl_http.req;
    •   v_resp  utl_http.resp;
    •   v_value VARCHAR2(1024);
    •   v_url varchar2(100);
  • begin
    • ---
    •   v_url:='';
    •   v_req := utl_http.begin_request(v_url);
    •   v_resp := utl_http.get_response(v_req);
    •   utl_http.read_text(v_resp, v_value);
    •   dbms_output.put_line(v_value);
    •   utl_http.end_response(v_resp);
    • ---
    • end;

Let see packages which we used on this example:


Begins a new HTTP request. When the function returns, the UTL_HTTP  package has established the network connection to the target Web server, or the proxy server if a proxy server is to be used, and has sent the HTTP request line. The PL/SQL program should continue the request by calling some other API to complete the request.


Reads the HTTP response and processes the status line and HTTP response headers. The status code, reason phrase and the HTTP protocol version are stored in the response record.


Reads the HTTP response body in text form and returns the output in the caller-supplied buffer. The end_of_body exception will be raised if the end of the HTTP response body is reached. Text data is automatically converted from the response body character set to the database character set.


Ends the HTTP response completing the HTTP request and response. Unless a HTTP 1.1 persistent  connection is used in this request, the network connection is closed.


Tags: , ,

Filter by APML


<<  July 2020  >>

View posts in large calendar