Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Convert CSV data held in a CLOB into columns
On Sun, 9 Dec 2007, jeremy0505_at_gmail.com wrote:
> In article <uzlwk5sum.fsf_at_rcn.com>, Galen Boyer says...
>> On Sat, 8 Dec 2007, jeremy0505_at_gmail.com wrote: >> >> > Alternatively, might we take the CLOB content, write it out via >> > UTL_FILE then refer to it as an external table? >> > >> > As I said, I would certainly like to be able to do all the work >> > without any depdency on referencing files on the o/s. >> >> You most certainly could do this, but you would have to reference >> files on the OS, but maybe, you don't mind referencing files on the >> OS through PL/SQL? >>
>> But, it sounds as though the files are different, ie, you would need >> a dynamic process to get them in, get the first record, create an >> external table from it and then load the file, or something like >> that? If this is the case, I don't get the final mapping of the >> data. Lets say you dynamically create an external table to reference >> a file, each time you recieve a new one. Those columns would mean >> something? Where do you map those columns to your final data-model? >> >> I'd think you would have a set format for your CSV files, define >> external tables before hand, and the only thing you would do at >> runtime is accept a clob and write it out to a particular filename in >> the particular directory and then select from the external table. >>
Well, an external table resolves down to sqlloader calls, so you could say oracle did the same. I say that Oracle put a SQL-based interface on sqlloader and that is a very good thing.
> - and in a very limited way I guess this is true. But it is important
> to have the complete process managed within the application. - no
> going off to the server to run sqlloader or ftp an excel file in.
Well, PL/SQL writing a file isn't "going off to the server". Its referencing an Oracle directory object. Have the process accept the file, code up the reciept of that, marshall it to a directory into a file matching the name in the external table and then kick off a select clause.
I find that much more easily managed all within the database than ftp'n, kicking off sqlloader and ...
Basically, do any of your shell operations within PL/SQL.
-- Galen BoyerReceived on Sun Dec 09 2007 - 11:34:01 CST
![]() |
![]() |