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.
Asked
Active
Viewed 1,452 times
0
-
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 Feb 21 '12 at 18:50
-
I want it to be automated. But from what I understand I can use PL/SQL scheduler to do that. – antonpug Feb 21 '12 at 18:53
-
duplicate of http://stackoverflow.com/questions/6198863/oracle-import-csv-file-using-sqlplus – Nir Alfasi Feb 21 '12 at 23:05
-
@alfasin...not a duplicate...different question. – antonpug Feb 23 '12 at 15:12
3 Answers
4
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.
Justin Cave
- 227,342
- 24
- 367
- 384
0
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).
Alessandro Rossi
- 2,432
- 17
- 24
0
Create an external table that references your CSV file. That means you can run select statements on the file from within Oracle.
Mike McAllister
- 1,479
- 2
- 12
- 15