Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Darshankumar.

Asked: June 23, 2021 - 1:05 pm UTC

Last updated: July 09, 2021 - 3:34 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

The URL is to Fetch All Data from the OPEN URL Given, Can it be possible to use SQL or PL/SQL via API or something?

Link to fetch the data: https://tfr.faa.gov/tfr2/list.html

It has one HyperLink on Column 'NOTAM', We need to fetch the data inside to that also, so we need to fetch all data + inside of that notam.

--DB VERSION

select* from v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

--Expectation of all data(Main URL+ Inside of that link) in One Oracle Table.
---Sorry if this is a stupid question for SQL and PL/SQL Lovers :) I think it's possible via something as I read articles of APIs, but not OPEN URLs.

and Connor said...

If I'm reading this correctly, you want to do a recursive retrieve of the page and linked pages.

Rather than trying to do this in SQL and PL/SQL, I would call out to the OS to do the hard work.

- You can get a scheduler job to call a shell script (or batch file).
- The batch can do a recursive wget to download the data and the linked pages as well
- An external table can use a preprocessor script to get a listing of files downloaded

After that, I'm not sure what you plan on doing with the data...

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Darshankumar Prajapati, June 30, 2021 - 8:38 am UTC

Thanks, Conor For the response, I am planning to use these data in DIM and FACT Further, and We have ODI as a TOOL here, but the problem is given URL is GOVT URL and we don't have any APIs for the same, so what do you suggest to Digest this Data in Oracle Table?
Connor McDonald
July 06, 2021 - 1:48 am UTC

My point is - the data is *not* on that page, it is on multiple pages that are linked to that page etc. Database aren't web crawlers - better to use a tool that is better suited to that, to get your data in a flat form, and then load it into the database from there.

approaches

FreyaBir, July 03, 2021 - 5:19 am UTC

fetch(url)
.then(function() {

})
.catch(function() {

});

https://bit.ly/337sMWi
Connor McDonald
July 06, 2021 - 1:48 am UTC

sigh

Darshankumar Prajapati, July 08, 2021 - 4:14 am UTC

Thank you so much for your valuables comments :)
Connor McDonald
July 09, 2021 - 3:34 am UTC

:-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library