8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Load XMLTYPE From URL

A simple method to load XMLTYPE data from a URL.

Create Schema Objects
Create Load Procedure
Create Network ACL
Test It
Using SSL

Create Schema Objects

First we create the necessary schema objects.

CONN test/test

CREATE TABLE xml_tab (
  id        NUMBER(10),
  url       VARCHAR2(4000),
  xml       XMLTYPE
);

ALTER TABLE xml_tab ADD (
  CONSTRAINT xml_tab_pk PRIMARY KEY (id)
);

CREATE SEQUENCE xml_tab_seq;

Create Load Procedure

Next we create a procedure to load a URL into an XMLTYPE.

CONN test/test

CREATE OR REPLACE PROCEDURE load_xmltype_from_url (
  p_url     IN            VARCHAR2,
  p_xmltype IN OUT NOCOPY XMLTYPE)
AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_clob           CLOB;
  l_text           VARCHAR2(32767);
  l_buffer         PLS_INTEGER := 10000;
BEGIN
  -- Initialize the CLOB.
  DBMS_LOB.createtemporary(l_clob, FALSE);

  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);

  -- Copy the response into the CLOB.
  BEGIN
    LOOP
      UTL_HTTP.read_text(l_http_response, l_text, l_buffer);
      DBMS_LOB.writeappend (l_clob, LENGTH(l_text), l_text);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_http_response);
  END;

  -- Insert the data into the table.
  p_xmltype := XMLTYPE(l_clob);

  -- Relase the resources associated with the temporary LOB.
  DBMS_LOB.freetemporary(l_clob);
EXCEPTION
  WHEN OTHERS THEN
    p_xmltype := NULL;
    UTL_HTTP.end_response(l_http_response);
    DBMS_LOB.freetemporary(l_clob);
    RAISE;
END load_xmltype_from_url;
/

Create Network ACL

If you are working with Oracle 11g or above, we need to create a network ACL to allow us to make a callout to the URL. I the following example we allow the user TEST to access "oracle-base.com" on port 80.

CONN / AS SYSDBA
#ALTER SESSION SET CONTAINER = pdb1;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'oracle-base-acl.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'TEST',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'oracle-base-acl.xml',
    host        => 'oracle-base.com', 
    lower_port  => 80,
    upper_port  => NULL); 

  COMMIT;
END;
/

Test It

Assuming the emp.xml is present in the appropriate location it can be loaded using the following code.

CONN test/test

DECLARE
  l_xmltype XMLTYPE;
  l_url     VARCHAR2(4000);
BEGIN
  l_url := 'http://oracle-base.com/webservices/emp.xml';

  load_xmltype_from_url(l_url, l_xmltype);

  INSERT INTO xml_tab (id, url, xml)
  VALUES ( xml_tab_seq.NEXTVAL, l_url, l_xmltype);

  COMMIT;
END;
/

The contents of the XMLTYPE can be checked using the following query.

SET LONG 5000
SELECT xml
FROM   xml_tab;

Using SSL

If you need to make calls using HTTPS, you will need to configure and open a wallet, as described here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.