Tenho uma tabela que recebe alguns dados de outras tabelas.
A noite rodamos um JOB onde pega os dados e joga em arquivos .txt e um outro processo pega esses .TXT'S e popula outras tabelas de outros schemas.
Porem na hora de gerar o .TXT se a tabela o Campo detalhe recebe dados de varias tabelas e tem um flag que fala que tipo de processo vai pagar o registro.
O que eu preciso é quando gerar o .TXT as linhas terem o tamanho do registro que ele esta recebendo.
Olha o Registro 2 ele esta com ate a coluna 740 porem ele esta sendo usando ate a coluna 42
Olha o Registro 3 ele esta com ate a coluna 741 porem ele esta sendo usando ate a coluna 77
Registro 1) 100060636900046384003CLAUDILENE RAMIRES RIBEIRO 2212197801734346140 00067338664550006732516116 679813249624122012PAULO CELESTINO 74 CONJUNTO AERO RANCHO CAMPO GRANDE MS79084390AUTONOMA PAULO CELESTINO 74 CONJUNTO AERO RANCHO CAMPO GRANDE MS79084390 VANIO 0000000000000000000000000000000000000000006733834121MAIKON 000000000000000000000000000000000000000 000679144114924122017001043254SSPMSMS 8125CR 8125CR1180 81 00000142 R R
Registro 2) 6792800084;LAURA;6733931959;;
Registro 3) ELDIENE NUNES SIQUEIRA 38384132593838413383
Segue o arquivo .sql que gera o .TXT
-------------------------------------
-------------------------------------
=====================================
-- Gera Arquivo.txt no diretorio indicado
SET SERVEROUTPUT ON SIZE 1000000
SET PAGESIZE 0
SET LINESIZE 800
SET TERMOUT OFF
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
--SET TRIM ON
SET TRIMOUT ON
COLUMN arqperiodo noprint new_value nomearquivo;
---Cria o nome do arquivo texto da Carta de Cobranca
SELECT 'CARTAS_'||TO_CHAR(SYSDATE,'yyyymmdd')||'.txt' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT detalhe FROM crd.TEMPDETCARTA where flg_carta is null order by linha;
update crd.tempdetcarta set flg_carta = sysdate where flg_carta is null;
commit;
---Cria o nome do arquivo texto para sistema de Recuperação ADAPTOR - INCLUSAO
SELECT 'CRDINCLUSAO'||TO_CHAR(SYSDATE,'yyyymmdd')||'.rem' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT detalhe FROM crd.TEMPDETCOBRA where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '00'
order by linha;
update crd.tempdetcobra set flg_cobra = sysdate where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '00';
commit;
---Cria o nome do arquivo texto para sistema de Recuperação ADAPTOR - INCLUSAO PRODUTO 22
SELECT 'CRDINCLUSAO22'||TO_CHAR(SYSDATE,'yyyymmdd')||'.rem' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT detalhe FROM crd.TEMPDETCOBRA where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '03'
order by linha;
update crd.tempdetcobra set flg_cobra = sysdate where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '03';
commit;
---Cria o nome do arquivo texto para sistema de Recuperação ADAPTOR - NEGATIVACAO
SELECT 'CRDNEGATIVACAO'||TO_CHAR(SYSDATE,'yyyymmdd')||'.rem' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT detalhe FROM crd.TEMPDETCOBRA where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '01'
order by linha;
update crd.tempdetcobra set flg_cobra = sysdate where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '01';
commit;
---Cria o nome do arquivo texto para sistema de Recuperação ADAPTOR - EXCLUSAO
SELECT 'CRDEXCLUSAO'||TO_CHAR(SYSDATE,'yyyymmdd')||'.rem' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT detalhe FROM crd.TEMPDETCOBRA where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '02'
order by linha;
update crd.tempdetcobra set flg_cobra = sysdate where flg_cobra is null and flg_atualiza = '9' and flg_arquivo = '02';
commit;
---Cria o nome do arquivo texto para sistema de Recuperação Externa
SELECT 'COBRANCA1'||TO_CHAR(SYSDATE,'yyyymmdd')||'.rem' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT detalhe FROM crd.TEMPDETCOBRA where flg_cobra is null and flg_atualiza = '1'
order by linha;
update crd.tempdetcobra set flg_cobra = sysdate where flg_cobra is null and flg_atualiza = '1';
commit;
------------INCLUIR O BLOCO ABAIXO---------------------------------
---Cria o nome do arquivo texto para Envio de Mensagem SMS
SELECT 'CRDSMS'||TO_CHAR(SYSDATE,'yyyymmdd')||'.TXT' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT trim(detalhe) FROM crd.TEMPDETCOBRA where flg_cobra is null and flg_arquivo = '80' order by linha;
update crd.tempdetcobra set flg_cobra = sysdate where flg_cobra is null and flg_arquivo = '80';
commit;
-------------FIM DA INCLUSAO DO BLOCO------------------------------
---Cria o nome do arquivo texto para Negativacao
SELECT 'CRD'||TO_CHAR(SYSDATE,'yyyymmdd')||'.REM' arqperiodo from dual;
spool &2\&&nomearquivo;
SELECT detalhe FROM crd.TEMPDETSPC where flg_spc is null order by detalhe;
update crd.tempdetspc set flg_spc = sysdate where flg_spc is null;
commit;
---Cria o nome do arquivo texto para Envio de Mensagem de VOZ
---Manter esse bloco abaixo sempre no ultimo processo a ser efetuado
---
set head off;
set feed off;
set trimspool on;
set linesize 32767;
set pagesize 0;
set sqlprompt ''
set termout off;
SELECT 'CRDVOZ'||TO_CHAR(SYSDATE,'yyyymmdd')||'.CSV' arqperiodo from dual;
spool &2\&&nomearquivo;
select substr(rtrim(detalhe),1,45),';', substr(detalhe,46,2),';', substr(detalhe,48,8),';',
substr(detalhe,56,2) ,';', substr(detalhe,58,8),';'
from crd.tempdetcobra where flg_cobra is null and flg_arquivo = '81' order by linha;
update crd.tempdetcobra set flg_cobra = sysdate where flg_cobra is null and flg_arquivo = '81';
commit;
spool off;
-------------------------------------
-------------------------------------
-------------------------------------
-------------------------------------
=====================================
Segue meu arquivo .BAT
-------------------------------------
-------------------------------------
=====================================
@ECHO OFF
CLS
REM
REM ###########################################################################
REM # Arquivo Bat : ATUALIZA_COBRANCA_DIARIA EM PRODUCAO #
REM # Objetivo : Executa Atualizacao de Dados de Cobranca do Consumidor#
REM # Arquivo Saida : CobDiaria.lst #
REM # #
REM # ----------------------------------------------------------#
REM # CONTROLE DE VERSõES : 1.3 #
REM # ----------------------------------------------------------#
REM # SOLICITANTE # #
REM # #
REM ###########################################################################
REM
REM ##################################
REM DEFINICAO DE VARIAVEIS DE AMBIENTE
REM ##################################
REM # Caminhos de Producao ==> c:\SISTEMAS\OPERACAO\
REM # Caminhos de Homologacao ==> c:\SISTEMAS\HOMOLOG\
REM ##################################
set DIA=%date:~4,2%
set mês=%date:~7,2%
set ANO=%date:~10,4%
REM ############################################################
SET BANCO=DBDESENV
SET CARTAS=c:\sistemas\homolog\siscred\log\CARTAS_%ANO%%mês%%DIA%.txt
SET COBRANCA_INCLUSAO=c:\sistemas\homolog\siscred\log\CRDINCLUSAO%ANO%%mês%%DIA%.rem
SET COBRANCA_INCLUSAO22=c:\sistemas\homolog\siscred\log\CRDINCLUSAO22%ANO%%mês%%DIA%.rem
SET COBRANCA_NEGATIVACAO=c:\sistemas\homolog\siscred\log\CRDNEGATIVACAO%ANO%%mês%%DIA%.rem
SET COBRANCA_EXCLUSAO=c:\sistemas\homolog\siscred\log\CRDEXCLUSAO%ANO%%mês%%DIA%.rem
SET COBRANCA1=c:\sistemas\homolog\siscred\log\COBRANCA1%ANO%%mês%%DIA%.rem
SET CARTA_ACSP=c:\sistemas\homolog\siscred\log\CRD%ANO%%mês%%DIA%.rem
SET SMS=c:\sistemas\operacao\homolog\log\CRDSMS%ANO%%mês%%DIA%.txt
SET VOZ=c:\sistemas\operacao\homolog\log\CRDVOZ%ANO%%mês%%DIA%.CSV
REM ############################################################
REM ### executavel que envia e-mail
REM ############################################################
SET BLAT=c:\sistemas\homolog\blat.exe
REM ############################################################
REM ### LOG do envio de e-mail
REM ############################################################
SET LOG=c:\sistemas\homolog\siscred\log\blat.log
SET DIR=c:\sistemas\homolog\siscred\log
REM ############################################################
REM ### saída do LOG da execução
REM ############################################################
SET LOG1=c:\sistemas\homolog\siscred\log\hcobdiaria.lst
SET LOG2=c:\sistemas\homolog\siscred\log\msg_h_carta.lst
SET LOG3=c:\sistemas\homolog\siscred\log\msg_h_cobi.lst
SET LOG10=c:\sistemas\homolog\siscred\log\msg_h_cobi22.lst
SET LOG4=c:\sistemas\homolog\siscred\log\msg_h_cobn.lst
SET LOG5=c:\sistemas\homolog\siscred\log\msg_h_cobe.lst
SET LOG6=c:\sistemas\homolog\siscred\log\msg_h_cob1.lst
SET LOG7=c:\sistemas\homolog\siscred\log\msg_h_ascp.lst
SET LOG8=c:\sistemas\homolog\siscred\log\msg_h_sms.lst
SET LOG9=c:\sistemas\homolog\siscred\log\msg_h_voz.lst
REM ############################################################
REM ### destinatário do e-mai da execucao da rotina
REM ############################################################
REM SET MAILTO=elcio@
REM SET MAILTO1=elcio@
REM SET MAILTO2=elcio@
REM SET MAILTO3=elcio@
REM ############################################################
SET MAILTO=elcio@
SET MAILTO1=elcio@
SET MAILTO2=elcio@
SET MAILTO3=elcio@
SET MAILTO4=elcio@
SET MAILTO5=elcio@
SET MAILTO6=elcio@
SET FROM=elciofrancisco@
rem cls
echo.
echo.
echo.
echo.
echo.
echo. ==============================================================
echoI I
echoI PARA INICIAR A EXECUCAO ATUALIZA_COBRANCA_DIARIA_TL11.BAT I
echoI I
echoI TECLE ENTER I
echoI I
echo. ==============================================================
echo.
echo.
echo.
echo.
echo.
pause
REM ############################################################
REM ### linha de comando de execução do SQL = script_atualiza_cobranca_diaria.sql
REM ############################################################
rem sqlplus80w /@%BANCO% @SCRIPT_ATUALIZA_COBRANCA_DIARIA_TL11_1.sql %LOG1% %DIR%
sqlplus /@%BANCO% @SCRIPT_ATUALIZA_COBRANCA_DIARIA_TL11_1.sql %LOG1% %DIR%
REM ############################################################
REM ### verificando se houve ERRO na execução
REM ############################################################
If NOT [%ERRORLEVEL%]==[0] (
REM ############################################################
REM ### mostrar essa mensagem na tela
REM ############################################################
echo ERRO NA ROTINA ATUALIZA_COBRANCA_DIARIA_TL11
REM ############################################################
REM ### caso haja enviar o e-mail definindo o subject
REM ############################################################
SET SBJ="ROTINA ATUALIZA_COBRANCA_DIARIA_TL11 no %BANCO% - ERRO"
) ELSE (
REM ############################################################
REM ### caso contrario, mostrar a mensagem na tela
REM ############################################################
echo ROTINA ATUALIZA_COBRANCA_DIARIA_TL11 OK
REM ############################################################
REM ### caso contrario, enviar o e-mail com outro subject
REM ############################################################
SET SBJ="ROTINA ATUALIZA_COBRANCA_DIARIA_TL11 no %BANCO%"
)
REM ############################################################
REM #### ENVIANDO CARTAS #######
REM ############################################################
echo Senhores,> %LOG2%
echo.>>%LOG2%
echo Segue em anexo o arquivo de CARTAS_TL11 >>%LOG2%
echo.>>%LOG2%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo. >>%LOG2%
echo.
SET SBJ1="CARTAS MULTICREDITO / TELEDATA"
%BLAT% %LOG2% -from %FROM% -to %MAILTO2% -subject %SBJ1% -attach %CARTAS% -log %LOG%
REM ############################################################
REM #### ENVIANDO COBRANCA INCLUSAO #######
REM ############################################################
echo Senhores,> %LOG3%
echo.>>%LOG3%
echo Segue em anexo o arquivo de COBRANCA_INCLUSAO_TL11 >>%LOG3%
echo.>>%LOG3%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo.>>%LOG3%
SET SBJ2="ARQUIVOS de COBRANCA-INCLUSAO"
%BLAT% %LOG3% -from %FROM% -to %MAILTO% -subject %SBJ2% -attach %COBRANCA_INCLUSAO% -log %LOG%
REM ############################################################
REM #### ENVIANDO COBRANCA INCLUSAO22 #######
REM ############################################################
echo Senhores,> %LOG10%
echo.>>%LOG10%
echo Segue em anexo o arquivo de COBRANCA_INCLUSAO22_TL11 >>%LOG10%
echo.>>%LOG10%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo.>>%LOG10%
SET SBJ9="ARQUIVOS de COBRANCA-INCLUSAO22"
%BLAT% %LOG10% -from %FROM% -to %MAILTO1% -subject %SBJ9% -attach %COBRANCA_INCLUSAO22% -log %LOG%
REM ############################################################
REM #### ENVIANDO COBRANCA NEGATIVACAO #######
REM ############################################################
echo Senhores,> %LOG4%
echo.>>%LOG4%
echo Segue em anexo o arquivo de COBRANCA_NEGATIVACAO_TL11 >>%LOG4%
echo.>>%LOG4%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo.>>%LOG4%
SET SBJ3="ARQUIVOS de COBRANCA-NEGATIVACAO"
%BLAT% %LOG4% -from %FROM% -to %MAILTO% -subject %SBJ3% -attach %COBRANCA_NEGATIVACAO% -log %LOG%
REM ############################################################
REM #### ENVIANDO COBRANCA EXCLUSAO #######
REM ############################################################
echo Senhores,> %LOG5%
echo.>>%LOG5%
echo Segue em anexo o arquivo de COBRANCA_EXCLUSAO_TL11 >>%LOG5%
echo.>>%LOG5%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo.>>%LOG5%
SET SBJ4="ARQUIVOS de COBRANCA-EXCLUSAO"
%BLAT% %LOG5% -from %FROM% -to %MAILTO% -subject %SBJ4% -attach %COBRANCA_EXCLUSAO% -log %LOG%
REM ############################################################
REM #### ENVIANDO COBRANCA 1 #######
REM ############################################################
echo Senhores,> %LOG6%
echo.>>%LOG6%
echo Segue em anexo o arquivo de COBRANCA1_TL11 >>%LOG6%
echo.>>%LOG6%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo.>>%LOG6%
echo
SET SBJ5="ARQUIVOS de COBRANCA1"
%BLAT% %LOG6% -from %FROM% -to %MAILTO4% -subject %SBJ5% -attach %COBRANCA1% -log %LOG%
REM ############################################################
REM #### ENVIANDO CARTA_ASCP #######
REM ############################################################
echo Senhores,> %LOG7%
echo.>>%LOG7%
echo Segue em anexo o arquivo de CARTA_ASCP_TL11>>%LOG7%
echo.>>%LOG7%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo.>>%LOG7%
echo.
SET SBJ6="ARQUIVOS de CARTA-ACSP"
%BLAT% %LOG7% -from %FROM% -to %MAILTO5% -subject %SBJ6% -attach %CARTA_ACSP% -log %LOG%
REM ############################################################
REM #### ENVIANDO MENSAGENS SMS #######
REM ############################################################
echo Senhores,> %LOG8%
echo.>>%LOG8%
echo Segue em anexo o arquivo de MENSAGENS SMS_TL11>>%LOG8%
echo.>>%LOG8%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo. >>%LOG8%
echo.
SET SBJ7="ARQUIVOS de MENSAGENS SMS"
%BLAT% %LOG8% -from %FROM% -to %MAILTO% -subject %SBJ7% -attach %SMS% -log %LOG%
REM ############################################################
REM #### ENVIANDO MENSAGENS DE VOZ #######
REM ############################################################
echo Senhores, > %LOG9%
echo.>> %LOG9%
echo Segue em anexo o arquivo de VOZ_TL11 >> %LOG9%
echo.>> %LOG9%
echo Caso o arquivo esteja vazio, favor desconsiderá-lo.>> %LOG9%
echo.>> %LOG9%
SET SBJ8="ARQUIVOS de VOZ"
%BLAT% %LOG9% -from %FROM% -to %MAILTO6% -subject %SBJ8% -attach %VOZ% -log %LOG%
REM ############################################################
REM #### ENVIANDO E-MAIL DA EXECUCAO #######
REM ############################################################
%BLAT% %LOG1% -from %FROM% -to %MAILTO1% -subject %SBJ% -log %LOG%
REM cls
echo.
echo.
echo.
echo.
echo.
echo.
echo. ===============================================================
echoI I
echoI Termino da execucao do ATUALIZA_COBRANCA_DIARIA.BATI
echoI I
echoI Tecle ENTER para FINALIZAR I
echoI I
echo. ===============================================================
echo.
echo.
echo.
echo.
echo.
echo.
echo.
echo.
pauseMuito Obrigado
Elcio Francisco
