Delimitadores para campos da query no spool do SQL*Plus (ponto e virgula, pipe ou TAB)

outubro 4, 2012 - Oracle Database, PL/SQL, Scripts - Tagged: colsep, cvs, delimitador, delimitadores, delimiter, ponto, spool, sqlplus, tab - no comments

Para gera um arquivo texto tipo “cvs” contendo delimitadores entre os campos como por exemplo o pipe ou mesmo o ponto e virgula, para poder ser utilizando num Excel, Access ou qualquer outro integrador, podemos utilizar no SQL*Plus (aplicativo instalado junto do produto do oracle client ou database) setando parametros para os delimitadores “COLSEP”, seguindo os exemplos:

Para a virgula

set COLSEP ,

ou para utilizar o ponto e virgula

set COLSEP ";"

Exemplo prático:

set pages 0 feed off
set colsep ,
select
object_name,
object_type,
status,
created
from ALL_OBJECTS
where owner='SYS'
AND OBJECT_TYPE='TABLE'
and rownum < 10  /* retornar somente 9 linhas para o exemplo */
;

O seu resultado será:

ACCESS$                       ,TABLE              ,VALID  ,20-DEC-11
ALERT_QT                      ,TABLE              ,VALID  ,20-DEC-11
APPLY$_CHANGE_HANDLERS        ,TABLE              ,VALID  ,20-DEC-11
APPLY$_CONF_HDLR_COLUMNS      ,TABLE              ,VALID  ,20-DEC-11
APPLY$_CONSTRAINT_COLUMNS     ,TABLE              ,VALID  ,20-DEC-11
APPLY$_DEST_OBJ               ,TABLE              ,VALID  ,20-DEC-11
APPLY$_DEST_OBJ_CMAP          ,TABLE              ,VALID  ,20-DEC-11
APPLY$_DEST_OBJ_OPS           ,TABLE              ,VALID  ,20-DEC-11
APPLY$_ERROR                  ,TABLE              ,VALID  ,20-DEC-11

Já utilizando o ponto e virgula temos que informar como um caracter utilizando-se das aspas:

set pages 0 feed off
set colsep ";"
select
object_name,
object_type,
status,
created
from ALL_OBJECTS
where owner='SYS'
AND OBJECT_TYPE='TABLE'
and rownum < 10 ;

O resultado será:

ACCESS$                       ;TABLE              ;VALID  ;20-DEC-11
ALERT_QT                      ;TABLE              ;VALID  ;20-DEC-11
APPLY$_CHANGE_HANDLERS        ;TABLE              ;VALID  ;20-DEC-11
APPLY$_CONF_HDLR_COLUMNS      ;TABLE              ;VALID  ;20-DEC-11
APPLY$_CONSTRAINT_COLUMNS     ;TABLE              ;VALID  ;20-DEC-11
APPLY$_DEST_OBJ               ;TABLE              ;VALID  ;20-DEC-11
APPLY$_DEST_OBJ_CMAP          ;TABLE              ;VALID  ;20-DEC-11
APPLY$_DEST_OBJ_OPS           ;TABLE              ;VALID  ;20-DEC-11
APPLY$_ERROR                  ;TABLE              ;VALID  ;20-DEC-11

Já para a delimitação por tabulação, deve ser feito da seguinte forma:

set pages 0 feed off
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"

select
object_name,
object_type,
status,
created
from ALL_OBJECTS
where owner='SYS'
AND OBJECT_TYPE='TABLE'
and rownum < 10 ;

O resultado será:

ALERT_QT                        TABLE                   VALID   20-DEC-11
APPLY$_CHANGE_HANDLERS          TABLE                   VALID   20-DEC-11
APPLY$_CONF_HDLR_COLUMNS        TABLE                   VALID   20-DEC-11
APPLY$_CONSTRAINT_COLUMNS       TABLE                   VALID   20-DEC-11
APPLY$_DEST_OBJ                 TABLE                   VALID   20-DEC-11
APPLY$_DEST_OBJ_CMAP            TABLE                   VALID   20-DEC-11
APPLY$_DEST_OBJ_OPS             TABLE                   VALID   20-DEC-11
APPLY$_ERROR                    TABLE                   VALID   20-DEC-11

Note que utilizei somente uma query para demonstrar a saida do nosso select que pode ser armazenado em um arquivo cvs utilizando o spool do SQL*Plus.

Autor: Rafael Stoever

Bacharel em Sistema de Informação pela Uniasselvi, atualmente cursando Gerenciamento de Projetos em TI pela Pós Graduação Uniasselvi. Atuo como Analista de suporte a banco de dados – DBA pela Lumina Serviços em TI residente de Blumenau/ SC, OPN Certified Specialist, Certificado OCP 10g/11g/12c, OCE RAC10g e Linux 10g. Conhecimentos em Microsoft SqlSever, Mysql e programação web (php,asp).