Menu de navegação

[Bruno Cavalcanti FreitasParticipante]

Bom dia! Estou com problemas para realizar a exportação do resultado de um select para um arquivo txt com os campos separados por ;. Imaginem que eu tenho a seguinte tabela:

CREATE TABLE TESTE
(
ID DECIMAL
, DESCRICAO VARCHAR2(40)
);

e que nesta tabela eu tenhas as seguintes linhas de dados:

ID	DESCRICAO
1	Descricao 1
2	Descricao 2
3	Descricao 3

Neste cenário, eu preciso exportar essas linhas dados no seguinte formato em um arquivo texto:

ID;DESCRICAO
1;Descricao 1
2;Descricao 2
3;Descricao 3

o máximo que eu consegui chegar foi no seguinte script:


set pagesize 0
set linesize 500
spool teste.txt
select ID || ';' || DESCRICAO from TESTE;
spool out;


Tentei usar o hint /*txt*/ mas funciona somente pelo sqlDeveloper e, mesmo que funcionasse eu não sei como passar o parâmetro de qual separador usar e para retirar as aspas (“”) dos campos VARCHAR2(como na opção delimited na exportação pelo sqldeveloer )

Obrigado pela ajuda!!

[José Laurindo ChiappaModerador]

Bem, se a sua necessidade é pontual (ie, vc precisa disso Eventualmente, apenas) sim, o Oracle SQL Developer é ** excelente ** pra isso, é é ridiculamente Fácil de usar e se fazer : é só colocar o HINT de DELIMITED (*** não é o de TXT apenas***) E se vc não quer o default vc Indica nas configs do SQL Developer o delimitador que vc quer, cfrme http://www.thatjeffsmith.com/archive/2013/04/sql-developer-preferences-for-delimited-text-exports/ explica / mostra…. Bico….
PORÉM, o Oracle SQL Developer é uma GUI, portanto vc tem que manualmente clickar onde vc quer, sendo DIFÍCIL portanto vc fazer isso programaticamente, automaticamente, principalmente se isso é um trabalho rotineiro que vc precisa fazer Rotineiramente com queries diferentes : aí sim, vc vai ter que Programar um pouquinho pra isso….
O sqlplus serve sim pra isso, muito embora a linguagem de programação via script dele seja um tanto limitada : vou demonstrar aqui como fazer no sqlplus MAS vou depois indicar outras opções (gratuitas, apenas) que não possuem as limitações do sqlplus…

a) sqlplus :

a principal vantagem do sqlplus é que ele é padrão numa instalação Oracle, mas a questão é que ele é uma tool de Report e Consulta de dados, portanto ele ** sempre ** vai gerar linhas com colunas de mesma largura, assim sendo se uma coluna não foi totalmente preenchida ele VAI ter espaços para esses casos… Se isso não te incomoda (o Excel, por exemplo, Absoluta e Completamente IGNORA por default espaços antes ou depois de cada coluna, no sqlplus a resposta é esta abaixo, com a IMPORTANTE adição que vc deve chamar o script de fora do sqlplus, assim :

==> olha o script :

C:Usersjlchi_000>type gera_delimit.sql

set colsep ‘;’
set headsep off
set pagesize 0
set trimspool on
set linesize 1000
spool delimit.txt
select deptno, dname, loc from dept;
exit
/
C:Usersjlchi_000>

==> O caracter / no final do script é exigência do sqlplus… Vou chamar o script, a partir da linha de comando, num ambiente onde (*** Óbvio ***) já tenho as variáveis de ambiente PATH, ORACLE_SID e ORACLE_HOME setadas :

C:Usersjlchi_000>sqlplus scott/tiger @gera_delimit.sql

SQL*Plus: Release 11.2.0.2.0 Production on Qua Jan 25 12:18:25 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Conectado a:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production

    10;ACCOUNTING    ;NEW YORK
    20;RESEARCH      ;DALLAS
    30;SALES         ;CHICAGO
    40;OPERATIONS    ;BOSTON

Desconectado de Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production

C:Usersjlchi_000>

==> Olha só o arquivo gerado :

C:Usersjlchi_000>dir delimit.txt
O volume na unidade C não tem nome.
O Número de Série do Volume é 340C-E794

Pasta de C:Usersjlchi_000

25/01/2017 12:18 141 delimit.txt
1 arquivo(s) 141 bytes
0 pasta(s) 460.942.860.288 bytes disponíveis

C:Usersjlchi_000>type delimit.txt
10;ACCOUNTING ;NEW YORK
20;RESEARCH ;DALLAS
30;SALES ;CHICAGO
40;OPERATIONS ;BOSTON

C:Usersjlchi_000>

==> O Fórum talvez reformate isso e vc não veja, mas o TYPE tá mostrando EXATAMENTE o que eu falei acima : os ponto-e-vírgula tão CERTINHO na mesma coluna, cada coluna tem a mesma Exata largura sempre, e são introduzidos espaços ou antes ou depois para “forçar” cada coluna a ficar com a mesma largura… Só pra teste, importei esse arquivo numa Planilha Excel e foi de boas…

b) outra opção gratuita que aceita programação via script é a ferramente Oracle SQL Command Line (SQLcl), disponível na mesma página de download do Oracle SQL Developer (ie, http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html) : eu não usei ela ainda mas em http://www.thatjeffsmith.com/archive/2015/12/more-set-sqlformat-fun-in-sqlcl/ o Autor mostra as opções, tenta lá…

c) ainda outra opção é vc usar a linguagem de programação interna default do RDBMS Oracle, a linguagem PL/SQL : ela possui uma package (biblioteca de rotinas) chamada UTL_FILE que serve para isso… O único senão é que, como essa linguagem é interna no database, ela só pode gravar o arquivo no Servidor do RDBMS Oracle, e não nalguma máquina-cliente : se isso não te afeta, https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:68212348056 tem um bom exemplo…

d) a opção final é vc programar nalguma linguagem de programação externa ao banco mas que permita conectar : https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:459020243348 tem um exemplo em PRO*C, se vc googlar vc encontra exemplos em Java, .NET e muitas outras…. A vantagem de se fazer algo personalizado é que você está no controle total, então vc formata como bem quiser, MAS a desvantagem óbvia é que vc Precisa entender de programação…

[]s

Chiappa

[Bruno Cavalcanti FreitasParticipante]

Muito obrigado Chiappa, vou verificar agora as dicas apresentadas…

[Bruno Cavalcanti FreitasParticipante]

Então… analisando as soluções apresentadas, optei por usar a opção de realizar a exportação por meio de uma linguagem de programação. Abaixo irei resumir o motivo de eu não usar as outras soluções:

a) a opção da exportação via sqlPlus não me seria viável porque o arquivo gerado (com o spool) vem com uma séria de informações extras (não viriam puramente os dados exportados) que eu precisaria tratar após essa exportação. O problema está em que os arquivos gerados teriam por volta de 20GB, o que me geraria um segundo procedimento com o custo de tempo alto

b)o SQLcl (que eu não conhecia e foi o maior ganho de conhecimento pra mim neste post) não atendeu somente pelo fato de que não é possível retirar os delimitadores de campos texto (enclosure), fato que tive conhecimento neste LINK, o que implicaria em um segundo tratamento nos dados exportados, caindo no mesmo problema da opção ‘a’. Mas essa ferramenta é incrível e me possibilitou uma séria de outras possibilidades.

c) a utilização do PL/SQL não foi possível por uma questão que o próprio Chiappa citou: eu não tenho acesso ao servidor do banco de dados.

Novamente gostaria de agradecer ao auxílio prestado, foi de extrema utilidade.

Até breve!! 🙂

[José Laurindo ChiappaModerador]

Só complementando :

1) NÃO é verdade que com sqlplus vc obtém “uma série de informações extras”, não senhor : como vc deve ter visto no meu exemplo, meu script *** NÂO *** veio com nada mais extra além dos dados exceto os espaços em branco antes e depois dos dados para fazer a coluna ficar de tamanho fixo…
Até, se são esses espaços em branco que estão te pegando E as colunas não são tantas assim, nós sempre temos no sqlplus a opção de concatenar: pra vc testar, no meu exemplo troque o :

select deptno, dname, loc from dept;

por :

select to_char(deptno, ‘FM0009’) || dname || loc LINHA from dept;

que vc verá que não serão gerados os espaços em branco entre cada coluna….

Evidentemente, se for grande o número de colunas isso repidamente fica inviável…

2) vc não cita o SQL developer então DEDUZO que vc precisa fazer isso Rotineiramente, programaticamente, a cada vez com query diferente, por isso a opção de GUI não te atendeu

3) não usei quase nada ainda o SQLcl mas sei que ele foi escrito pelo mesmo time do SQL Developer : então se o SQL developer tem opção de vc escolher qual delimitador vc quer (que podem SIM ser gerados sem aspas nem nada mais que o dado puro) eu ** ACHO ** que o SQLcl deve ter alguma coisa pra isso também, veja na doc dele e no site que indiquei , bem como nos helps dele

4) sim, por padrão o PL/SQL via UTL_FILE só pode gerar arquivos no servidor Oracle : sempre há a possibilidade de vc programar um FTP do servidor pra sua máquina (ou vice-versa), ou ter pastas Compartilhadas (talvez via NFS) entre a sua máquina e o servidor mas essas coisas DEMANDAM autorizações e setups extras, que apenas o DBA e o sysadmin podem fazer, realmente como desenvolvedor vc não consegue : bate um papo sobre isso com seu DBA e com seu sysadmin mas ninguém garante

[]s

Chiappa

[Autor]

Posts