Home » SQL & PL/SQL » SQL & PL/SQL » Data Type Issue (Oracle 10g)
Data Type Issue [message #404829] Sun, 24 May 2009 22:21 Go to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Hi All,

I am using UTL_FILE types to open a file in a server location and tried to get the data using get_line. But the variable i am using is varchar2 to get the data. The lenght is restricted to 32767 and i am trying to read data more than 32767. Is there any other approach i can follow to read the data. i tried with CLOB and i failed to find the method to read the data. Can anyone post a small example that i can refer.
Appreciates your valuable responses at earliest convineince.

Thanks & Regards
Joshi
Re: Data Type Issue [message #404830 is a reply to message #404829] Sun, 24 May 2009 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

How would an independent observer conclude your "problem" has been solved?

One possibility might be EXTERNAL TABLE, but I am to sure what needs to be done or how to decide problem has been solved.
Re: Data Type Issue [message #404839 is a reply to message #404829] Sun, 24 May 2009 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have text file with line greater than 32767! What kind of text file is this?

Always post your Oracle version (4 decimals).

Regards
Michel
Re: Data Type Issue [message #404845 is a reply to message #404839] Sun, 24 May 2009 23:26 Go to previous messageGo to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Michel,

That is a tilda seperated list, where the length is greater than 32767. I am using the following code:

v_in_file utl_file.file_type;
v_in_rec VARCHAR2(32767);

v_in_file := utl_file.fopen(p_dir_name, p_file_name, 'r','32767');

utl_file.get_line(v_in_file, v_in_rec);

Here since v_in_rec is of type varchar2(32767) i couldn't able to read the file. Please suggest me any alternate solution with small example.

Regards
Joshi
Re: Data Type Issue [message #404850 is a reply to message #404845] Sun, 24 May 2009 23:45 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As BlackSwan said, investigate on Use external table instead of utl_file.

Regards
Michel
Previous Topic: Problem with Materialized View Query Rewrite.
Next Topic: How To Remove Duplicates
Goto Forum:
  


Current Time: Sun Dec 04 00:21:10 CST 2016

Total time taken to generate the page: 0.21990 seconds