Home » SQL & PL/SQL » SQL & PL/SQL » Extension of UTL_File (Oracle 10g)
Extension of UTL_File [message #375753] Sun, 14 December 2008 02:30 Go to next message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member

Hi all

I want to import some data from a webpage source file. The data is in the form

 i city1  apple   200       banana   100       carrot   400 i city2  sweet   150  peach   200



and so on....

I have gone through the forum help regarding UTL_file but wasn't able to understand that how can i read chracter by chracter to search the specific city and their products and price

I have to write this data in the format in the text file


city1,apple,200
city1,banana,100
city1,carrot,400
city2,sweet,150
city2,peach,200$



Can anyone help me out... i have only end of today for this assignment to complete
Re: Extension of UTL_File [message #375756 is a reply to message #375753] Sun, 14 December 2008 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to use Oracle to read a file, format the line and write into a file?
Use OS program like "awk".

And if it is just a homework on PL/SQL and UTL_FILE, you have to show us what you tried.

Regards
Michel

[Updated on: Sun, 14 December 2008 03:44]

Report message to a moderator

Re: Extension of UTL_File [message #375766 is a reply to message #375753] Sun, 14 December 2008 05:49 Go to previous messageGo to next message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member

I have a bulk of related data and i cannot do it manually.
May i know some other way of doing the same
owk? what it is

thanks for your reply
Re: Extension of UTL_File [message #375773 is a reply to message #375766] Sun, 14 December 2008 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
man awk
or Google for it.

Regards
Michel
Re: Extension of UTL_File [message #375798 is a reply to message #375753] Sun, 14 December 2008 15:12 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
There are probably a lot of ways you could do this. They all involve replacing your i's with linefeeds and your whitespace with commas. You could open a text file in notepad and do that with a few global replaces. Or you could use utl_file to read and replace and write. Or you could load the whole thing into one clob, replace, then dump back. Or you could use SQL*Loader or an external table to load it in then spool it back out. You need to either make some attempt of your own or explain why you need to do this or what the ultimate goal is. If this is a homework assignment and it is required to be done in a certain way then say so. If not, then explain what the text file is used for once the data is in it as there may be a simpler way to reach the ultimate goal. For example, if you are trying to format it for easy loading into a table, then you might as well skip that step and load it as is, specifying whitespace as field delimiters and i as your record delimiter.

[Updated on: Sun, 14 December 2008 15:12]

Report message to a moderator

Previous Topic: forum thread like query using connect by...
Next Topic: problem in inserting as well as selecting
Goto Forum:
  


Current Time: Thu Dec 08 02:05:46 CST 2016

Total time taken to generate the page: 0.16896 seconds