This content has been marked as final. Show 6 replies
if you want to know how to import csv file in Oracle, this is quite a common question and reported in the FAQ: SQL and PL/SQL FAQ
Read that one and if something is still not clear try to post details about what you are trying to do.
jeneesh wrote:I've just split it off to a thread of it's own. ;)
And, please don't "hijack" 5 year old thread..Better start a new one..
I have a Clob file as a in parameter in my PROC. . File is comma separated.need procedure that would parse this CLOB variable and populate in oracle table .You don't have a "clob file" as there's no such thing. CLOB is a datatype for storing large character based objects. A file is something on the operating system's filesystem.
So, why have you stored comma seperated data in a CLOB?
Where did this data come from? If it came from a file, why didn't you use SQL*Loader or, even better, External Tables to read and parse the data into structured format when populating the database with it?
If you really do have to parse a CLOB of data to pull out the comma seperated values, then you're going to have to write something yourself to do that, reading "lines" by looking for the newline character(s), and then breaking up the "lines" into the component data by looking for commas within it, using normal string functions such as INSTR and SUBSTR or, if necessary, REGEXP_INSTR and REGEXP_SUBSTR. If you have string data that contains commas but uses double quotes around the string, then you'll also have the added complexity of ignoring commas within such string data.
Like I say... it's much easier with SQL*Loader of External Tables as these are designed to parse such CSV type data.
986420 wrote:If it's not a multi-user functionality that's required, simply save the CLOB to a known file on the database server, and then use an External Table definition to read it.
I’m going to do HTTP request to Ogone and get text file in response I have this file in variable as CLOB. File is comma separated. I need procedure that would parse this CLOB variable and populate in oracle table.
If it's required for multi-user functionality, then it becomes a little more complicated.