UTL_FILE Delimitador

[LoadingXp]

Pessoal fiz uma proc que faz a leitura em um diretorio com o UTL_FILE, só que nesse arquivo tem diversos delimitadores ;

alguém sabe se tem alguma função do Oracle para eu fazer isso e importar meu arquivo tranquilamente sem utilizar o SQL*Loader e conseguir separar os campos???

Obrigado desde já.

[Trevisolli]

Brother,

Tenho uma dica...

Com a função INSTR, você consegue saber o nro. da posição deste delimitador...

Por exemplo:

Selecionar tudo

SELECT INSTR('PAULO','L') 
  FROM DUAL;
  
-- Ele retorna 4, pois o L está na quarta posição...

Acho q com isso, você consegue trabalhar teu arquivo, juntamente com a função SUBSTR.

Como exemplo:

Selecionar tudo

SELECT SUBSTR('PAULO', INSTR('PAULO','L'), 2) 
  FROM DUAL;

À partir da posição do delimitador, pegue N (no meu caso 2) casas.

qualquer coisa manda ai brother.

[LoadingXp]

Trevis, eu entendi, mas rola apenas um problema...

E quando eu tenho por exemplo

Selecionar tudo

SELECT INSTR('PAULO;MARIA;JOAO;Trevis;Glufke.Net',';') FROM DUAL; 

O primeiro é tranquilo:

Selecionar tudo

select substr('PAULO;MARIA;JOAO,Trevis,Glufke.Net',1,5) from DUAL;

e o segundo?? como eu devo fazer???

[Trevisolli]

Fala Brother, blzera? Tudo certo?

Seguinte, aí é que está o "X" da questão, rs...

1) Você terá que contar, até achar o primeiro ";", beleza? Daí você tem a primeira palavra.
2) Achou o primeiro, o teu nro. incial no Substr, será o ponto onde achou o ";" e assim por diante.

Por exemplo:

Selecionar tudo

PAULO;MARIA;

1)Conta até achar o primeiro ";".
2)À partir desta posição, procure o próximo e, o SUBSTR será de (6,INSTR DO PRÓXIMO).

Não sei se fui muito claro.

qualquer coisa me manda ai q dou uma força assim que puder.

[LoadingXp]

Putz verdade!!!!
Vlw Trevis!!! brigadão!!!!

[Trevisolli]

Bom dia, brother, blzera?


Cara, mais uma dica, MUITO IMPORTANTE, que esqueci de postar aqui ontem, me desculpe mesmo.

Na função INSTR, tem como você passar de que posição para a frente você deseja buscar o caracter.

Por exemplo:

Selecionar tudo

SELECT INSTR('PAULO TREVISOLLI','L',1) 
  FROM DUAL;
  
-- Retorna 4 (À partir da primeira posição, o próximo "L" é na Quarta posição)


SELECT INSTR('PAULO TREVISOLLI','L',5) 
  FROM DUAL;
  
-- Retorna 14 (À partir da quinta posição, o próximo "L" é na Décima-Quinta posição)

[dr_gori]

Galera,
desculpe a demora pra responder. Ontem eu não tive contato com o forum, mas hoje vai compensar

Eu tenho uma função chamada ELEMENTO, que retorna exatamente isso!

Selecionar tudo

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* -----------------------------------------------------------------------------------------
* FUNCTION  : elemento
* DESCRICAO : Retorna o elemento x de um string com com n elemento separados por
*             uma marca especial.
*
* PARAMETROS DE ENTRADA
* - p_string   : varchar2 contendo o string completo com os n elementos.
* - p_element  : pls_integer contendo o índice do elemento a ser extraído.
* - p_separador: varchar2 contendo a marca utilizada como separadora dos elementos.
*
* RETORNO
* - Varchar2 com o elemento solicitado por parâmetro. Se não existir, retorna NULL
*/
FUNCTION elemento( p_string VARCHAR2
                 , p_elemento PLS_INTEGER
                 , p_separador VARCHAR2 DEFAULT cte_SEPARADOR
                 ) RETURN VARCHAR2 AS
  v_string VARCHAR2(5000);
BEGIN
  v_string := p_string || p_separador;
  FOR i IN 1 .. p_elemento - 1
  LOOP
    v_string := SUBSTR(v_string,INSTR(v_string,p_separador)+LENGTH(p_separador));
  END LOOP;
  
  RETURN SUBSTR(v_string,1,INSTR(v_string,p_separador)-1);
END elemento; 

Agora, vamos aos exemplos:

Selecionar tudo

SQL> select elemento('aaa;bbbbbbb;cccc;dddd', 1, ';') x from dual;

X
--------
aaa

SQL> select elemento('aaa;bbbbbbb;cccc;dddd', 2, ';') x from dual;

X
--------
bbbbbbb

SQL> 

[LoadingXp]

Show sua função Gori...

Se não fosse o arquivo errado que me enviam eu conseguia resolver de um jeito mais simples, mas o arquivo é totalmente inconsistente.

Minha solução inicial era transformar o meu delimitador | por ',' e depois colocar um ' no inicio e no final.
Ex.

Selecionar tudo

Luciano|Trevis|Gori|Oracle
'Luciano','Trevis','Gori','Oracle'

E com isso eu iria concatenar com insert into tabela e executar com o execute imediate.

Mas como o arquivo vem incompleto eu adotei a segunda solução enviada pelo meu amigo Trevis, ai ficou assim:

Selecionar tudo

CREATE OR REPLACE PROCEDURE GPMADMIN.SP_INSVIVONET2 AS
  v_Count numeric :=0;
  vlValor varchar2(5000);
  i numeric :=0;
  
  pos_ini numeric :=0;
  pos_fim numeric :=0;


  PROTOCOLO                 NUMBER;
  DATA_ABERTURA             VARCHAR2(30);
  REGIONAL                  VARCHAR2(50);
  OPERADORA                 VARCHAR2(20);
  GRUPO_ABERTURA            VARCHAR2(50);

  Cursor Cur_perguntas is
    select valor from importacaovivonet;

BEGIN
  
  DELETE FROM IMPORTACAOVIVONET;
  
  IMPVIVONETFECHADOS();
  
  
  select count(1) into v_Count from (select * from importacaovivonet);
  OPEN Cur_Perguntas;
  while i <= v_Count
    LOOP
    pos_ini := 0;
    pos_fim := 0;
    FETCH Cur_Perguntas INTO vlValor;
    select instr(substr(vlValor,pos_ini,length(vlValor)),'|') into pos_fim from DUAL;
    select substr(vlValor,pos_ini,pos_fim-1) into PROTOCOLO from DUAL;
    
    pos_ini:=pos_ini+pos_fim+1;
    pos_fim:=0;
    
    select instr(substr(vlValor,pos_ini,length(vlValor)),'|') into pos_fim from DUAL;
    select substr(vlValor,pos_ini,pos_fim-1) into DATA_ABERTURA from DUAL;
    
    pos_ini:=pos_ini+pos_fim;
    pos_fim:=0;
    
    select instr(substr(vlValor,pos_ini,length(vlValor)),'|') into pos_fim from DUAL;
    select substr(vlValor,pos_ini,pos_fim-1) into REGIONAL from DUAL;
    
    pos_ini:=pos_ini+pos_fim;
    pos_fim:=0;
    
    select instr(substr(vlValor,pos_ini,length(vlValor)),'|') into pos_fim from DUAL;
    select substr(vlValor,pos_ini,pos_fim-1) into OPERADORA from DUAL;
    
    pos_ini:=pos_ini+pos_fim;
    pos_fim:=0;
    
    select instr(substr(vlValor,pos_ini,length(vlValor)),'|') into pos_fim from DUAL;
    select substr(vlValor,pos_ini,pos_fim-1) into GRUPO_ABERTURA from DUAL;


    INSERT INTO IMPORTACAOVIVONET2 VALUES(PROTOCOLO,DATA_ABERTURA,REGIONAL,OPERADORA,
                                   GRUPO_ABERTURA);

   END LOOP;
    
  CLOSE Cur_Perguntas; 
          
END;

Reduzi um pouco o codigo pois está bem extenço.
A unica duvida que ficou foi...

Quando dá algum problema no insert tem como eu ignorar e passar o proximo??

Desde já agradeço a ajuda de todos!

[Trevisolli]

Luciano,

Seria QUALQUER erro de insert, você deve ignorar?

Daí, neste caso, você poderia criar um exception da seguinte maneira:

Selecionar tudo

EXCEPTION 
  WHEN OTHERS THEN 
    NULL;

Nota importante: Estou dando a dica apenas para este caso, posto que essa exception irá ignorar qualquer erro, beleza?

PRecisando, conta com a gente.

[LoadingXp]

Deu certo implementei da seguinte forma:

Selecionar tudo

    EXCEPTION
      WHEN OTHERS THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE ('erro no protocolo: ' || PROTOCOLO);
        INSERT INTO IMPORTACAOVIVONET_ERROS
            SELECT VALOR FROM IMPORTACAOVIVONET WHERE ROWNUM=1;
        COMMIT;    
        DELETE FROM IMPORTACAOVIVONET WHERE ROWNUM=1;
        COMMIT;
        SP_INSVIVONET2;
        NULL;
     END;

O unico problema é que eu saio do loop de execução...

Mas vlw Trevis!!!

[dr_gori]

Tem que colocar o begin/exception apenas para o insert. Daí não sai do loop.
Exemplo:

Selecionar tudo

    .
    .
    .
    select instr(substr(vlValor,pos_ini,length(vlValor)),'|') into pos_fim from DUAL;
    select substr(vlValor,pos_ini,pos_fim-1) into OPERADORA from DUAL;
   
    pos_ini:=pos_ini+pos_fim;
    pos_fim:=0;
   
    select instr(substr(vlValor,pos_ini,length(vlValor)),'|') into pos_fim from DUAL;
    select substr(vlValor,pos_ini,pos_fim-1) into GRUPO_ABERTURA from DUAL;

    begin
      INSERT INTO IMPORTACAOVIVONET2 VALUES(PROTOCOLO,DATA_ABERTURA,REGIONAL,OPERADORA,
                                   GRUPO_ABERTURA);
    exception when others
    then 
       ...  --seu codigo aqui...

    end;

   END LOOP;
   
  CLOSE Cur_Perguntas;
         
END;

[HenriqueMachado]

Pessoal estou no mesmo caminho do exemplo acima,

Mais esta dando problemas com caracteres especias e as funções não estão funcionando

Para exempleficar

Selecionar tudo

DECLARE 
    arquivo_ler UTL_FILE.File_Type;
    AReg_Linha VARCHAR2(4000);
BEGIN
    DBMS_OUTPUT.ENABLE(100000);
    DBMS_OUTPUT.PUT_LINE('ok');
    arquivo_ler := UTL_FILE.FOPEN_NCHAR('DIR', '001662.TXT', 'r');    
    UTL_FILE.GET_LINE_NCHAR(arquivo_ler, AReg_Linha);
    DBMS_OUTPUT.PUT_LINE(getField (4, AReg_Linha));
END;

A função GetField é a mesma que elemento
Ou seja retorna a 4 possição entre os caracteres ";"

Obtem o resultado

Selecionar tudo

ok
0;253610672;200706;ASSOCIA�O DOS FISSURADOS DO VALE DO ITAJA�1;2;3;1;1;1;2;2;3;2;1;5
ASSOCIA�O DOS FISSURADOS DO VALE DO ITAJA�1

Mais a linha original do arquivo é

Selecionar tudo

0;253610672;200706;ASSOCIAÇÃO DOS FISSURADOS DO VALE DO ITAJAÍ;1;2;3;1;1;1;2;2;3;2;1;5

alguém poderia me ajudar?

[Tineks]

Henrique,

tenta ai mudar o idioma da sua sessão utilizando o nls_language, talves ele reconheça esses caracteres.. por exemplo.

Selecionar tudo

   DECLARE
      .
      .
      .
   BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET nls_language=''brazilian portuguese''';
      .
      .
   END;

[]'s

[HenriqueMachado]

Ola Tineks,


Tentei mudar o idioma como você sugeriu, mais ainda não funcionou. Obtive o mesmo problema.

[Trevisolli]

Henrique,

Você poderia trocar esses caracteres especiais por normais, ou tem que, obrigatóriamente vir assim?

Se puder, o TRANSLATE te ajuda.
Tem vários exemplos aqui no fórum.
Se não puder manda ai.

[HenriqueMachado]

Preciso da informação com acentuação