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.
4
-
Is this a one-time load? An ongoing process? Are you trying to completely automate it or is a process that involves human intervention acceptable?Justin Cave– Justin Cave2012-02-21 18:50:38 +00:00Commented Feb 21, 2012 at 18:50
-
I want it to be automated. But from what I understand I can use PL/SQL scheduler to do that.antonpug– antonpug2012-02-21 18:53:48 +00:00Commented Feb 21, 2012 at 18:53
-
duplicate of stackoverflow.com/questions/6198863/…Nir Alfasi– Nir Alfasi2012-02-21 23:05:41 +00:00Commented Feb 21, 2012 at 23:05
-
@alfasin...not a duplicate...different question.antonpug– antonpug2012-02-23 15:12:27 +00:00Commented Feb 23, 2012 at 15:12
Add a comment
|
3 Answers
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_SCHEDULERorDBMS_JOBpackage to schedule the job
The Oracle database account you're using will need to be granted access to all of these packages.
Comments
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).
Comments
Create an external table that references your CSV file. That means you can run select statements on the file from within Oracle.
