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.
Related articles.
- All XML Articles
- XMLTYPE Datatype
- Load XMLTYPE From File
- Parse XML Documents in Oracle 9i
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
- UTL_HTTP and SSL (HTTPS) using Oracle Wallets
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:
- All XML Articles
- XMLTYPE Datatype
- Load XMLTYPE From File
- Parse XML Documents in Oracle 9i
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
- UTL_HTTP and SSL (HTTPS) using Oracle Wallets
Hope this helps. Regards Tim...