jeremy wrote:
> In article <93697bc7-3f2d-4230-992e-db710f35ae57
> @y43g2000hsy.googlegroups.com>, Mark D Powell says...
>> On Dec 7, 5:05 pm, jeremy <jeremy0..._at_gmail.com> wrote:
>>> Oracle 10gR2
>>>
>>> Would like to be able to support upload of a CSV file and then populate
>>> a "staging" table with its contents.
>>>
>
>> What is the primary reason you want to be able to parse the column
>> names and then receive the data in one file? For a target why can't
>> missing data values just be missing as in two delimiters in a row with
>> no value between them?
>
> Just looking to see how flexible we can be - the design isn't completed
> yet.
>> One idea to consider might be to define a generic load table
>> (external) where every column is varchar2(4000) and then just use sql
>> to convert each column to the proper target table type.
>>
>> If you open the select on the external table as a cursor you could
>> select the first line and use the column names (and maybe target table
>> name) to build a dynamic sql insert statement string with bind
>> variables. Then for cursor rows 2 to EOF you just fire the dynamic
>> insert.
>>
>
> This was definitely one approach.
>
>> Unless the data source does a good job formatting and verifying the
>> data handling errors can be a problem with overly generic approaches.
>>
>> You should also consider just defining a sqlldr control file for each
>> expected external file that you are going to want to load into a table
>> or holding (processing) table.
>
> WOuld rather keep the entire thing within pl/sql.
>> Perl and Pro*C could be used at the OS level to parse the file and
>> submit the records to the database via an insert if you need to
>> control the timing of the task execution outside the db. The external
>> table would be good when you want to trigger the load from within a db
>> task.
>
> Definitely do not want to use these tools.
>
>
> Can a CLOB be referred to as an external table? If so then we can keep
> the entire process IN the database and cut out steps like establishing
> directory objects, placing CSV file in corresponding o/s directory etc.
In theory an external table should be able to handle anything that
SQL*Loader can handle. They are essentially the same technology.
http://tahiti.oracle.com
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Dec 08 2007 - 12:02:05 CST
