Menu de navegação

[elciodbaParticipante]

Boa tarde, gostaria de uma ajuda. 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 daddos 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 ter 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:SISTEMASOPERACAO
REM # Caminhos de Homologacao ==> c:SISTEMASHOMOLOG
REM ##################################

set DIA=%date:~4,2%
set MES=%date:~7,2%
set ANO=%date:~10,4%

REM ############################################################

SET BANCO=DBDESENV
SET CARTAS=c:sistemashomologsiscredlogCARTAS_%ANO%%MES%%DIA%.txt
SET COBRANCA_INCLUSAO=c:sistemashomologsiscredlogCRDINCLUSAO%ANO%%MES%%DIA%.rem
SET COBRANCA_INCLUSAO22=c:sistemashomologsiscredlogCRDINCLUSAO22%ANO%%MES%%DIA%.rem
SET COBRANCA_NEGATIVACAO=c:sistemashomologsiscredlogCRDNEGATIVACAO%ANO%%MES%%DIA%.rem
SET COBRANCA_EXCLUSAO=c:sistemashomologsiscredlogCRDEXCLUSAO%ANO%%MES%%DIA%.rem
SET COBRANCA1=c:sistemashomologsiscredlogCOBRANCA1%ANO%%MES%%DIA%.rem
SET CARTA_ACSP=c:sistemashomologsiscredlogCRD%ANO%%MES%%DIA%.rem
SET SMS=c:sistemasoperacaohomologlogCRDSMS%ANO%%MES%%DIA%.txt
SET VOZ=c:sistemasoperacaohomologlogCRDVOZ%ANO%%MES%%DIA%.CSV

REM ############################################################
REM ### executavel que envia e-mail
REM ############################################################

SET BLAT=c:sistemashomologblat.exe

REM ############################################################
REM ### LOG do envio de e-mail
REM ############################################################

SET LOG=c:sistemashomologsiscredlogblat.log
SET DIR=c:sistemashomologsiscredlog

REM ############################################################
REM ### saída do LOG da execução
REM ############################################################

SET LOG1=c:sistemashomologsiscredloghcobdiaria.lst
SET LOG2=c:sistemashomologsiscredlogmsg_h_carta.lst
SET LOG3=c:sistemashomologsiscredlogmsg_h_cobi.lst
SET LOG10=c:sistemashomologsiscredlogmsg_h_cobi22.lst
SET LOG4=c:sistemashomologsiscredlogmsg_h_cobn.lst
SET LOG5=c:sistemashomologsiscredlogmsg_h_cobe.lst
SET LOG6=c:sistemashomologsiscredlogmsg_h_cob1.lst
SET LOG7=c:sistemashomologsiscredlogmsg_h_ascp.lst
SET LOG8=c:sistemashomologsiscredlogmsg_h_sms.lst
SET LOG9=c:sistemashomologsiscredlogmsg_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.

pause

Muito Obrigado

Elcio Francisco

[rmanParticipante]

@elciodba

Juro que li várias vezes e não entendi nada. 😯

[Sergio WilliansMestre]

Tenho que admitir que também não consegui assimilar o problema.

Você gostaria de fixar um tamanho padrão para os campos ? É isso @elciodba ?

Poderia nos explicar melhor o problema ?

[Autor]

Posts