Home » SQL & PL/SQL » SQL & PL/SQL » utl_file
utl_file [message #376335] Wed, 17 December 2008 01:00 Go to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
Hi all,

I have an input file which contains the characters in the below format...

~122332
|1kjfdsajfsa
~122334
|1kjfdsajfsafdsaf
~122335
|1kjfdsajfsa
~12233765
|1kjfdsajfsa9483

I need to process these characters.. If the second line is not present i.e
~122332

~122334

~122335

~122337

I have given an exception stating when no_data_found then null to find the end of file...

The program is processed for only one record. for other records its not executing...pls help me on this...
I need to get the output in this format..
~122332
Blank line found
~122334
Blank line found
~122335
Blank line found
~122337
Blank line found
Re: utl_file [message #376339 is a reply to message #376335] Wed, 17 December 2008 01:15 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi, Please post here what you have already done, then probably the experts here can specifically help you.

Thanks,
Wilbert
Re: utl_file [message #376860 is a reply to message #376335] Thu, 18 December 2008 22:47 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,
This may be help you.

Declare
 input_buffer UTL_FILE.file_type;
input_file  := UTL_FILE.fopen ('/usr/tmp', 'Infile.txt', 'R');
output_file := UTL_FILE.fopen('/usr/tmp','Outfile.txt','W');

LOOP
UTL_FILE.get_line (input_file, input_buffer);
 If input_buffer is not null then
   UTL_FILE.put_line (input_file, output_buffer);
 else
   UTL_FILE.put_line (input_file, 'Blank Line Found');
 end if;
END LOOP;
Re: utl_file [message #376864 is a reply to message #376860] Thu, 18 December 2008 23:37 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Siva,

Create a Directory DDD before proceeding

SQL> ed
Wrote file afiedt.buf

  1  Declare
  2  input_buffer varchar2(100);
  3  input_file  UTL_FILE.file_type;
  4  output_file UTL_FILE.file_type;
  5  begin
  6  input_file  := UTL_FILE.fopen ('DDD', 'Infile.txt', 'R');
  7  output_file := UTL_FILE.fopen('DDD','Outfile.txt','W');
  8  LOOP
  9  begin
 10  UTL_FILE.get_line (input_file, input_buffer);
 11   If Regexp_instr(input_buffer,'[:alpha:]',1) >0   then
 12     UTL_FILE.put_line (output_file, 'Blank Line Found');
 13  else
 14     UTL_FILE.put_line (output_file, input_buffer);
 15   end if;
 16  exception
 17  when NO_DATA_FOUND then
 18  UTL_FILE.FCLOSE(input_file);
 19  UTL_FILE.FCLOSE(output_file);
 20  exit;
 21  end;
 22  end loop;
 23* end;
SQL> /
Re: utl_file [message #376917 is a reply to message #376864] Fri, 19 December 2008 02:57 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
after my opinion code should be:
 If Regexp_instr(input_buffer,'[:alpha:]',1) is null   then
     UTL_FILE.put_line (output_file, 'Blank Line Found');


because:
select 
  case when Regexp_instr('','[:alpha:]',1) is null then 'blank' 
       else 'not blank' end
	from dual;
Previous Topic: Query with Row Generator
Next Topic: Like with decode (merged)
Goto Forum:
  


Current Time: Wed Dec 07 10:24:31 CST 2016

Total time taken to generate the page: 0.07475 seconds