UTL File - TXT File Reading

[E105826]

Hi guys, how are you?

I have a doubt, I researched the internet, including here in the forum and I did not find anything.

Next, I'm reading a txt file that is in the X directory. Up to this point without problems, I can open the file and check the first line.

My doubt, when I read the first line and populus the necessary variables need to leave for the next line.
But I checked, through DBMS, that the result it takes me is always the value of the first line.

Someone would know a command that says something like going to next line?

follows my code:

Select all

 
declare 
	v_arquivo		utl_file.file_type; 
	v_origem		varchar2(200)	:=	'/oracle8/banco/trace/bdump'; 
	v_nome_arquivo		varchar2(50)	:=	'xyz.TXT'; 
 
	v_linha			varchar2(32767); 
	v_cd_banco			number; 
	v_nr_lote			number; 
	v_id_registro			number; 
	v_ds_cnab_1			varchar2(9); 
	v_id_tipo_inscr_empr		number; 
	v_nr_inscricao_empresa		number; 
	v_id_convenio_banco		varchar2(20); 
	v_cd_agencia_mantenedora	number; 
	v_id_dig_agencia		varchar2(1); 
	v_nr_conta_corrente		number; 
	v_id_dig_conta_corrente		varchar(1); 
	v_id_dig_conta_agencia		varchar2(1); 
	v_nm_empresa			varchar2(30); 
	v_nm_banco			varchar2(30); 
	v_ds_cnab_2			varchar2(10); 
	v_cd_remessa			number; 
	v_dt_geracao_arquivo		date; 
	v_hr_geracao_arquivo		varchar2(6); 
	v_nr_seq_arquivo		number; 
	v_nr_vs_layout			number; 
	v_nr_densidade_gravacao_arq	number; 
	v_ds_reservado_banco		varchar2(20); 
	v_ds_reservado_empresa		varchar2(20); 
	v_ds_cnab_3			varchar2(29); 
	v_registro_anterior		number;                      
	cont				number	:=	0;                
 
begin 
v_arquivo	:=	utl_file.fopen	(v_origem,v_nome_arquivo,'R'); 
 
	utl_file.get_line	(v_arquivo,v_linha,240); 
 
	while	cont <= 20	loop 
		v_cd_banco			                    := 	    substr(v_linha,1,3); 	     
		v_nr_lote				            :=      substr(v_linha,4,4);	         
		v_id_registro			                    :=      substr(v_linha,8,1);	                             
		v_ds_cnab_1			                    :=	    substr(v_linha,9,9);	      
		v_id_tipo_inscr_empr		            	    :=	    substr(v_linha,18,1); 	    
		v_nr_inscricao_empresa		          	    :=	    substr(v_linha,19,14); 	   
		v_id_convenio_banco		             	    :=	    substr(v_linha,33,20); 	   
		v_cd_agencia_mantenedora		            :=	    substr(v_linha,53,5); 	    
		v_id_dig_agencia			            :=      substr(v_linha,58,1); 	     
		v_nr_conta_corrente		             	    :=	    substr(v_linha,59,12); 	   
		v_id_dig_conta_corrente		         	    :=	    substr(v_linha,71,1); 	    
		v_id_dig_conta_agencia		          	    :=	    substr(v_linha,72,1); 	    
		v_nm_empresa			                    :=      substr(v_linha,73,30); 	    
		v_nm_banco			                    := 	    substr(v_linha,103,30); 	  
		v_ds_cnab_2			                    :=	    substr(v_linha,133,10); 	  
		v_cd_remessa			                    :=      substr(v_linha,143,1); 	    
		v_dt_geracao_arquivo		            	    :=	    substr(v_linha,144,8); 	   
		v_hr_geracao_arquivo		            	    :=	    substr(v_linha,152,6); 	   
		v_nr_seq_arquivo			            :=      substr(v_linha,158,6); 	    
		v_nr_vs_layout			                    := 	    substr(v_linha,164,3); 	   
		v_nr_densidade_gravacao_arq	      		    :=	    substr(v_linha,167,5); 	   
		v_ds_reservado_banco		            	    :=	    substr(v_linha,172,20); 	  
		v_ds_reservado_empresa		          	    :=	    substr(v_linha,192,20); 	  
		v_ds_cnab_3			                    :=	    substr(v_linha,212,29); 	  
 
Dbms_Output.Put_Line(substr(v_linha,9,9)|| substr(v_linha,18,1)|| substr(v_linha,19,14) ||substr(v_linha,33,20) ||substr(v_linha,53,5)||	substr(v_linha,58,1)||	substr(v_linha,59,12)||	substr(v_linha,71,1)||	substr(v_linha,72,1)||	substr(v_linha,73,30)||	substr(v_linha,103,30)||	substr(v_linha,133,10)||	substr(v_linha,143,1)||	substr(v_linha,144,8)||	substr(v_linha,152,6)||	substr(v_linha,158,6)||	substr(v_linha,164,3)||	substr(v_linha,167,5)||	substr(v_linha,172,20)||	substr(v_linha,192,20)||	substr(v_linha,212,29)); 
		cont	:=	cont	+	1; 
	end	loop; 
 
	utl_file.fclose	(v_arquivo); 
 
exception 
	when	others	then 
	Dbms_Output.Put_Line(sqlerrm); 
	utl_file.fclose_all; 
 
end; 

Thanks !!!

[senaha.ricardo]

E105826,

I had this problem ... my script only read the 1st line ...
then I created a variable q receives itself and concatena with the file line and ends when you do not find more lines.
In my case, pick up the result and saved in a table q has a column of type clob.
It was like this, however, I made some changes to post here ...

Select all

 
--CREATE TABLE wt_teste_rs ( X CLOB ); 
DECLARE 
   l_vDir     VARCHAR2(10000) := '/usr/tmp'; 
   l_fArquivo utl_file.file_type; 
   l_vTxt     VARCHAR2(1000) := 'geracao.txt'; 
   l_vLine    VARCHAR2(4000); 
   l_cFile    CLOB; 
BEGIN   
   l_fArquivo := utl_file.fopen( l_vDir, l_vTxt, 'R'); 
   LOOP 
     BEGIN 
       -- 
       utl_file.get_line( l_fArquivo, l_vLine ); 
       l_cFile := l_cFile || l_vLine || chr(10); 
       -- 
     EXCEPTION 
       WHEN no_data_found THEN 
         EXIT; 
     END; 
   END LOOP; 
   utl_file.fclose(l_fArquivo); 
   INSERT INTO wt_teste_rs VALUEs( l_cFile ); 
END; 

See if this helps you ...

[E105826]

Hello everyone,

today with the head cleaner I found the problem.

In fact my opening to catch the line is out of while, with that, I open the first line and I get a loop on it 20 times.

I put it inside while and all the lines were read normally.

follows as it was:

Select all

 
	while	v_linha	is	not	null	loop 
	     
		utl_file.get_line	(v_arquivo,v_linha); 
 
		Dbms_Output.Put_Line(v_linha); 
	end	loop;