| Create a Table from .csv File [message #266148] |
Mon, 10 September 2007 00:51  |
rajaobj
Messages: 9
Registered: September 2006
Location: Chennai
|
Junior Member |
|
|
Hi Friends,
My requirement is
1.I will be getting a .csv file which has the details about the columns and datatypes.
2.I want to create a table based on this.
3.How to automate this process.
Can anybody help me out on this.
Thanks,
Raja
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Create a Table from .csv File [message #266206 is a reply to message #266148] |
Mon, 10 September 2007 03:47  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
If file you are getting is the same all the time (not data, but its description), I'd rather pre-create this table (connect to SQL*Plus and issue CREATE TABLE statement). If your database version does NOT support external tables feature, you'd use SQL*Loader to take data from a file and insert those records into a table. Depending upon requirements you probably know, you might APPEND new records (which would leave old records in a table) or REPLACE old records with new ones.
If external tables feature is available to you, you might create external table and work with it as if it was an "ordinary" Oracle table; I believe you'd use some kind of SELECT (whether in SQL or even PL/SQL script).
However, if file description differs all the time, you'd first have to parse a record to find out how it looks like and - with a lot of pain (from my point of view) - create (and DROP, perhaps?) a table dynamically using the EXECUTE IMMEDIATE.
I hope (and believe) this is NOT the case - just pre-create a table, write a control file and load data using SQL*Loader.
What do you call "automating the process"? Once table is created and control file written, you'd only have to run SQL*Loader. If this file is available, for example, every day on 8:00 AM, you'd use your operating system scheduling utility.
If, on the other hand, you choose to use external tables, you might run this (PL/)SQL script using DBMS_JOB (or DBMS_SCHEDULER).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
