CODEWITHSUNDEEP
oracle-databaseplsqloracle10goracle-apex
antonpug
antonpug

Reputation: 14296

Loading data from a web-hosted CSV into Oracle?

I don't have time to write a perl or anything like that nor do I have admin access to the back end, so how can I get data from a file on the intranet (http://) and parse it to create a table? Maybe somehow via PL/SQL? Keep in mind I don't have much admin access.

Upvotes: 0

Views: 1522

Answers (3)

Mike McAllister
Mike McAllister

Reputation: 1549

Create an external table that references your CSV file. That means you can run select statements on the file from within Oracle.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

If you want it to be completely automated

  • You can use the UTL_HTTP package to retrieve the data from the HTTP server
  • You can either parse the CSV response yourself using INSTR and SUBSTR or you can use the UTL_FILE package to write the data to a file on the database server file system and then create an external table that parses the CSV file you just created.
  • You can then insert the parsed data into a table you've already created (I'm assuming that the CSV data is in the same format each time).
  • You can use the DBMS_SCHEDULER or DBMS_JOB package to schedule the job

The Oracle database account you're using will need to be granted access to all of these packages.

Upvotes: 4

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

You may download the file into your host machine and then use SQL*Loader to populate a table.

Other ways there are some wizards that may be easier than SQL*Loader, if you are using IDEs like PLSQLDeveloper(Tools->Text Importer) or SQLDeveloper(right click on Tables-> Import Data).

Upvotes: 0

Related Questions