Home » SQL & PL/SQL » SQL & PL/SQL » Upload program, for uuploading a csv file
Upload program, for uuploading a csv file [message #42588] Mon, 14 April 2003 14:20 Go to next message
Nan
Messages: 12
Registered: August 2000
Junior Member
I need some assistance and advice on how to create a certain procedure. The purpose of the procedure is to upload a csv file with 8 columns : invoice number, po number, date, customer number, branch, cutomer, amount and table_id.

My first approach to try is to read a pl/sql table created by the loader file (not sure on what type of loader file, need some suggestions)and create a second pl/sql table to pass to an existing update routine. This would help I feel in any problems with having two different update programs.
Let me know your feedback or suggestions.
Nan
Re: Upload program, for uuploading a csv file [message #42589 is a reply to message #42588] Mon, 14 April 2003 14:39 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
By "upload", do you mean load a .csv text file into a table or tables?

A non-PL/SQL approach would be to just use the IMP (import) utility which can load the data directly.

A PL/SQL approach would involve using the UTL_FILE package to read the file. You would have to parse it and insert/update the table as needed.

I'm not sure why you feel that PL/SQL tables would be involved in the process.
Re: Upload program, for uuploading a csv file [message #42631 is a reply to message #42588] Sat, 19 April 2003 09:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte has a nice generic load_data function for loading data from a delimited file to a table using utl_file. All you have to do is pass a few parameters for the table name, column names, directory name, file name, and delimiter, instead of writing a bunch of code. Here is a link that includes the code and a demonstratin of its usage:

Re: Upload program, for uploading a csv file [message #240474 is a reply to message #42631] Fri, 25 May 2007 00:26 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
Please let me know how can I use the pl/sql approach using UTL_FILE package to upload data from csv/excel file to a table in oracle. I can't go for a sql loader option but have to write a sql code. Can anyone give me a simple example to understand this.



Regards,
Mona

[Updated on: Fri, 25 May 2007 00:35]

Report message to a moderator

Re: Upload program, for uploading a csv file [message #240486 is a reply to message #240474] Fri, 25 May 2007 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can't use SQL*Loader because it is an external program then use external table that allows to see a flat file as an Oracle table.
An overview is there: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref778, then follow the links.
No need of PL/SQL.

Regards
Michel
Re: Upload program, for uploading a csv file [message #240490 is a reply to message #240486] Fri, 25 May 2007 01:06 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks for your reply. But I have to do it through sql code only as this would involve some other things too.

Thanks,
Mona
Re: Upload program, for uploading a csv file [message #240493 is a reply to message #240490] Fri, 25 May 2007 01:12 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With external table you do it with SQL only, not even PL/SQL.
Just create table + select.

Regards
Michel
Previous Topic: Bitmap index
Next Topic: Materialized view ON COMMIT 12054 error - but it almost works!
Goto Forum:
  


Current Time: Fri Dec 02 20:30:12 CST 2016

Total time taken to generate the page: 0.10753 seconds