How to import xml file from Internet directly to Oracle table

Question

I would like my Oracle to fetch an xml file directly from the Internet and insert it to a table (parsing can be omitted in this thread).

Example: https://cve.mitre.org/data/downloads/allitems-cvrf-year-1999.xml

    <Vulnerability
        xmlns="http://www.icasi.org/CVRF/schema/vuln/1.1" Ordinal="1">
        <Title>CVE-1999-0001</Title>
        <Notes>
            <Note Type="Description" Ordinal="1">ip_input.c in BSD-derived TCP/IP implementations allows remote attackers to cause a denial of service (crash or hang) via crafted packets.</Note>
            <Note Type="Other" Title="Published" Ordinal="2">2000-02-04</Note>
            <Note Type="Other" Title="Modified" Ordinal="3">2005-12-16</Note>
        </Notes>
        <CVE>CVE-1999-0001</CVE>
        <References>
            <Reference>
                <URL/>
                <Description>BUGTRAQ:19981223 Re: CERT Advisory CA-98.13 - TCP/IP Denial of Service</Description>
            </Reference>
            <Reference>
                <URL/>
                <Description>CERT:CA-98-13-tcp-denial-of-service</Description>
            </Reference>
            <Reference>
                <URL>http://www.openbsd.org/errata23.html#tcpfix</URL>
                <Description>CONFIRM:http://www.openbsd.org/errata23.html#tcpfix</Description>
            </Reference>
            <Reference>
                <URL>http://www.osvdb.org/5707</URL>
                <Description>OSVDB:5707</Description>
            </Reference>
        </References>
    </Vulnerability> *REPEAT*

First I create a table:

CREATE TABLE XML_TABLE
   (    ID NUMBER, 
    XML_DATA XMLTYPE
);

and then I would like to (if possible) INSERT a new row in the table for each new Vulnerability (everything in between the vulnerability tag in the xml).

To fetch the data I can use the following command (not sure if it's optimal for the purpose):

host curl https://cve.mitre.org/data/downloads/allitems-cvrf-year-1999.xml

The issue is to store it.

Ennio71 · Answer 1 · 2019-09-14T15:29:24.670

1

You can use UTL_HTTP package to fetch data from and to Internet and your DB.

Here the documentation Database PL/SQL Packages and Types Reference UTL_HTTP

Here an explanation with example Oracle base UTL_HTTP

If you are running on DB version >=11 be aware of the ACL as explained.

Optionally you can write your java procedure and the wrapper for PL/SQL ( as I made ) to interact with web service and return a Clob.

Take a look here

edited Sep 14 '19 at 15:29

answered Sep 14 '19 at 15:00

Ennio71

66
6

Thank you for your comment. I have been struggling now for a day without getting the HTTPS to work. I constantly get the ORA-28759: failure to open file. There are a lot threads on that...just need to be patient to find the right one :-) In your last link I get an error when trying to compile the function post. Is it because there are some prerequisites I just don't know about? – Hippo Sep 17 '19 at 19:16
I have basically followed the instructions provided with this example you have posted [Link](https://oracle-base.com/articles/misc/utl_http-and-ssl). I have configued the ACL accorind to [these instrcutions](https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1) In the sqlnet.ora file I have added this: ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE) (METHOD_DATA= (DIRECTORY=C:\Users\[USERNAME]\AppData\Local\Temp))) This is also where my wallet is situated. I have granted permissions to the wallet files and added the user ORA_DBA. – Hippo Sep 18 '19 at 06:26
Try moving wallet to another location on file system ( e.g c:\wallet) as stated [here](https://community.oracle.com/thread/2317808?start=0&tstart=0) – Ennio71 Sep 18 '19 at 07:06

How to import xml file from Internet directly to Oracle table

1 Answers1