Tokenizer - Browsing string w / sep fields. per ";"

[Filipe_Geissler]

Good afternoon!!

I am reading an Excel worksheet in Forms, with PL / SQL using Text_IO. Each line has the fields that are separator by ";", and I have to get each of these fields and play in a variable. I believe that someone must have done this, or something like that, could anyone give me a tip ????
PS: I know that with SQLLoader is very easy, I'vê done it, but in this case it has to be like that, in hand!

Thanks for the attention !!

[dr_gori]

I have an interesting routine, but I do not think it applies to your case. This routine is more useful in the passage of parameters when the number of input parameters is not known ...

Take a look:

Select all

set ver off 
def Del='@' 
def String="@0@11@222@3333@44444@555555@6666666@77777777@888888888" 
 
select 
Rownum Row#, 
SUBSTR(A.Str||'&Del' 
, INSTR(A.Str||'&Del' , '&Del', 1, Rownum ) +1 
, INSTR(A.Str||'&Del' , '&Del', 1, Rownum+1) 
-INSTR(A.Str||'&Del' , '&Del', 1, Rownum ) -1 
) Token 
From 
( 
select 
'&String' Str 
from dual 
) 
A, 
all_objects B 
where 
Rownum< length(A.Str)-length(REPLACE(A.Str,'&Del'))+1 
; 
 
 
ROW#       TOKEN 
---------- ------------------------------------------------------- 
1          0 
2          11 
3          222 
4          3333 
5          44444 
6          555555 
7          6666666 
8          77777777 
9          888888888 
 

I believe you will be able to do this your ... put a loop and gets the substr between one; and another ...
: -O

[Filipe_Geissler]

I was able to:

Select all

begin 
	 
	in_file := Text_IO.Fopen(filename, 'r'); 
	-- loop para percorrer todas a linhas do arquivo 
	LOOP 
    begin 
    	Text_IO.Get_Line(in_file, linebuf); 
    	i := 0; 
    	numseparador := 1; 
    	aux := null; 
    	-- for para percorrer a linha caracter à caracter 
			for i in 1..length(linebuf) loop 
    		caracter := ( substr(linebuf, i, 1)); 
    		-- cada vez que achar o separador ";", separa o campo 
    		if caracter = ';' then 
    			if numseparador = 1 then  
    				ano := aux; 
    			elsif numseparador = 2 then  
    				mês := aux; 
    			elsif numseparador = 3 then  
    				pv := aux; 
    			elsif numseparador = 4 then 
    				valor_fat := aux; 
    			end if; 
    			-- incrementa o separador, que vai indicar o proximo campo 
    			numseparador := numseparador + 1; 
    			aux := null;    			 
    		-- se não achar o separador, vai montando o campo ate achar o separador 
    		else 
    			aux := aux || caracter; 
    		end if;		   		 
    	end loop; 
    	message(ano || ' - ' || mês || ' - ' || pv || ' - ' || valor_fat); 
    	-- validações aqui... 
		exception 
		  when no_data_found then 
		    Text_IO.Fclose(in_file); 
		    exit; 
		end; 
	END LOOP; 
	 
end;

This code reads the file line A and runs the line Character to find the ";" Play the string mounted on "aux" in the variable ...
worked very well. I posted here to leave as a consultation for another person !!
Any questions just talk !!!

falow

[leobbg]

For those who like to use pl / table there it goes ..

Select all

 
/********************************************************************************************/ 
  /* Rotina responsável em separar "cargas" de dados que utilizam um separador em um "array"  */ 
  /********************************************************************************************/ 
  procedure prc_separa_texto( pTexto in varchar2, pSeparador in varchar2 default null ) is 
    -- 
    pSeparador varchar2(1); 
    -- 
    vContador number := 1; 
	  vInicio   number := 1; 
	  vFim      number := 0; 
	  -- 
	  type tG_Array_Texto is table of varchar2(4000) index by binary_integer; 
    vG_Texto_Separado tG_Array_Texto; 
    -- 
	begin 
	  -- 
	  loop 
	    -- 
	    vFim := instr( pTexto, pSeparador, 1, vContador ); 
	    -- 
	    exit when vFim = 0; 
	    -- 
	    vG_Texto_Separado(vContador) := substr( pTexto, vInicio, vFim - vInicio ); 
	    -- 
	    vInicio   := vFim      + 1; 
	    vContador := vContador + 1; 
	    -- 
	  end loop; 
    -- 
  end; 

[dr_gori]

Another form of "tokenizing".
It is necessary to create TYPE TP_VARCHAR2 with the command below:

Select all

CREATE TYPE TP_VARCHAR2 AS TABLE OF VARCHAR2(2000) 

Select all

CREATE OR REPLACE FUNCTION FNC_TOKENIZER(P_STRING    IN VARCHAR2, 
                                         P_SEPARADOR IN VARCHAR2) 
   RETURN TP_VARCHAR2 PIPELINED IS 
   l_string LONG DEFAULT p_string || p_separador; 
   n        NUMBER; 
   /*----------------------------------------------------------------------*/ 
   /* Nome : FNC_PHW_TOKENIZER                                             */ 
   /* Descrição : Função para separação, em linhas diferentes, de valo-    */ 
   /* res de uma lista que utilize algum tipo de separador                 */ 
   /* Ex.: "A, B, C", separa cada letra num linha diferente                */ 
   /* Utilização:                                                          */ 
   /* select column_value valor from TABLE(fnc_tokenizer('A,B,C',','))     */ 
   /* Autor : Getulio Holtz (TechnoCorp)                                   */ 
   /* Data : 27 de Setembro de 2006                                        */ 
   /* É necessário a criação do tipo TP_VARCHAR2 com o comando abaixo:     */ 
   /* CREATE TYPE TP_VARCHAR2 AS TABLE OF VARCHAR2(2000)                   */ 
   /*----------------------------------------------------------------------*/ 
 
 
BEGIN 
   LOOP 
      EXIT WHEN nvl(l_string,' ') = ' '; 
      n := instr(l_string, 
                 p_separador); 
      dbms_output.put_line(n); 
      PIPE ROW(ltrim(rtrim(substr(l_string, 
                                  1, 
                                  n - 1)))); 
      l_string := substr(l_string, 
                         n + 1); 
   END LOOP; 
   RETURN; 
END FNC_TOKENIZER; 
/

[dr_gori]

Here is another form of tokenizing, Paulo Diniz's contribution (Mato Grosso do Sul)
This uses regular expressions and connect by


99] Exit:

Select all

LISTA 
-------------------------------- 
paulo@email.com 
thomas@email.com 

has a document of it that explains how to do the opposite of this, that is: pick up several lines and group in one, delimited by a character. (Using the native 11G function named Listagg). https://docs.google.com/file/d/0B7CiEAx ... t?hl=en_US or http://www.linkedin.com/groups/Converte ... RITM-title