This content has been marked as final. Show 7 replies
Horsefeathers. You can use SQL Loader or External Tables, both of which are covered in the documentation.
.I know I have to use UTL _FILE but if someone could give me the code it would be great .
Thanks da.BTW what's horsefeathers all about ?
Neways I have the solution with External Tables .I specifically wanted the code for opening ,Reading ,Inserting and then Closing the CSV file (Delimited by , and each line ending in newline) using UTL_FILE.
I have a deadlien to meet and don't have time to experiment with codes .I will propose the externale table solution but I needed a backup solution too .
BTW ...Will visit your blog for sure .Am a writer myself caught up in the web of coding !
Thanks for your reply !
An external table will take less time to code than the equivalent UTL_FILE solution. It is unlikely anybody here is going to give you a PL/SQL program you can use because your requirements are bespoke.
I have a deadlien to meet and don't have time to experiment with codes
I use it to mean "nonsense" but it's probably a euphemism for something stronger.
.BTW what's horsefeathers all about ?
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.