UTL_FILE Delimitador

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
LoadingXp
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 65
Registrado em: Sex, 30 Mar 2007 11:57 am
Localização: SP
Contato:
Att.

Luciano Alvarenga M. Pires
DBA ORACLE CERTIFICADO
http://fulloracle.blogspot.com
------------------------------------
Dinheiro é o combustivel da sociedade industrial. Mas na sociedade da informática o combustivel, o poder, é o conhecimento.

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
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 65
Registrado em: Sex, 30 Mar 2007 11:57 am
Localização: SP
Contato:
Att.

Luciano Alvarenga M. Pires
DBA ORACLE CERTIFICADO
http://fulloracle.blogspot.com
------------------------------------
Dinheiro é o combustivel da sociedade industrial. Mas na sociedade da informática o combustivel, o poder, é o conhecimento.

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
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 65
Registrado em: Sex, 30 Mar 2007 11:57 am
Localização: SP
Contato:
Att.

Luciano Alvarenga M. Pires
DBA ORACLE CERTIFICADO
http://fulloracle.blogspot.com
------------------------------------
Dinheiro é o combustivel da sociedade industrial. Mas na sociedade da informática o combustivel, o poder, é o conhecimento.

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

:D
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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)

Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

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> 
:-o
LoadingXp
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 65
Registrado em: Sex, 30 Mar 2007 11:57 am
Localização: SP
Contato:
Att.

Luciano Alvarenga M. Pires
DBA ORACLE CERTIFICADO
http://fulloracle.blogspot.com
------------------------------------
Dinheiro é o combustivel da sociedade industrial. Mas na sociedade da informática o combustivel, o poder, é o conhecimento.

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
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 65
Registrado em: Sex, 30 Mar 2007 11:57 am
Localização: SP
Contato:
Att.

Luciano Alvarenga M. Pires
DBA ORACLE CERTIFICADO
http://fulloracle.blogspot.com
------------------------------------
Dinheiro é o combustivel da sociedade industrial. Mas na sociedade da informática o combustivel, o poder, é o conhecimento.

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!!!
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

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
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 48
Registrado em: Seg, 29 Mai 2006 1:24 pm
Localização: Blumenau - SC
Abraços,
Henrique Machado Muller

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
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 365
Registrado em: Ter, 24 Mai 2005 2:24 pm
Localização: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

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
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 48
Registrado em: Seg, 29 Mai 2006 1:24 pm
Localização: Blumenau - SC
Abraços,
Henrique Machado Muller

Ola Tineks,


Tentei mudar o idioma como você sugeriu, mais ainda não funcionou. Obtive o mesmo problema.
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 48
Registrado em: Seg, 29 Mai 2006 1:24 pm
Localização: Blumenau - SC
Abraços,
Henrique Machado Muller

Preciso da informação com acentuação
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 27 visitantes