Menu de navegação

[MrandersonParticipante]

Boa noite senhores!

Estou tentando montar um spool para geração de delimited e eu queria colocar variáveis para rodar em loop (For Each) nele para geração de arquivos de acordo com a seleção.
Vou exemplificar (perdoem se estiver errado):

main.sql

CLEAR SCREEN;
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0 EMBEDDED ON
SET SQLFORMAT DELIMITED ; " "
DEFINE DIR = 'C:\Users\Sql\local_da_query\'
CD 'C:\Users\Sql\local_onde_salvo_os_arquivos_gerados\'
BEGIN
FOR i IN (SELECT local, rota FROM LOCALIDADE) LOOP      --as variáveis local e rota no loop (são números inteiros)
@@ query.sql
END LOOP;
END;

A query do loop:

query.sql

SPOOL Arquivo_$local_$rota.dsv                          --as variáveis local e rota no nome do arquivo dsv
SELECT
*
FROM TABLE
WHERE LOCAL = $local AND ROTA = $rota;                  --as variáveis local e rota no filtro da query
SPOOL OFF

Atualmente eu estou gerando esse loop por meio de job no Pentaho gerando arquivos csv, mas pelo script SQL seria muito mais rápido.
Se esse procedimento funcionar, vou eliminar 397 loops.
OBS: utilizo o SQL Developer.

Desde já, agradeço!

[José Laurindo ChiappaModerador]

Absolutamente não vai funcionar o que vc codificou : veja, o comando CD só existe no DOS, não dentro de um script sqlplus…. Igualmente, a instrução @@ para vc executar um script só existe num prompt de comando sqlplus , NÂO dentro de um bloco BEGIN/END como vc tenta usar : dentro de bloco BEGIN/END só podem ser colocados comando PL/SQL, o que vc PODE verificar no manual PL/SQL, não contempla o @ ….. IGUALMENTE, nem imagino o que siginifica esses $ no meio do SELECT :

SELECT
*
FROM TABLE
WHERE LOCAL = $local AND ROTA = $rota;

==> seja num comando SQL, seja num comando PL/SQL, “$” não é um Operador válido….

Pelo (pouco) que entendi, teu objetivo é gerar um arquivo .SQL com comandos tipo :

SPOOL nomedearquivo1.txt
SELECT .. FROM tabela1;
SPOOL OFF
SPOOL nomedearquivo2.txt
SELECT … FROm tabelaoutra;
SPOOL OFF
…..
exit
/

==> Eu RECOMENDARIA que vc OU escrevesse um bloco PL/SQL que crie o arquivo desejado via UTL_FILE, OU que gere o arquivo de script com os comandos do sqlplus….

[]s

Chiappa

[José Laurindo ChiappaModerador]

Um exemplo usando recursos do sqlplus : eu vi que no caso vc quer gerar arquivos diferentes pesquisando na tabela TABLE para cada local e rota : no meu exemplo, como eu não tenho esse tipo de dado, eu vou fazer uma demonstração supondo que eu quero gerar arquivos diferentes consultando a tabela EMP para CADA DEPTNO cadastrado na tabela DEPT com cada arquivo contendo os empregados de um dado departamento :

scott@DESENV:SQL>ed
Gravou file afiedt.buf

1 select ‘SPOOL C:\EMPS_DEPTO_’ || to_char(T. DEPTNO, ‘FM00’) || ‘.TXT’
2 || chr(13) || CHR(10) || ‘SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=’ || T.DEPTNO || ‘;’
3* || chr(13) || chr(10) || ‘SPOOL OFF’ as LINHA from dept T
scott@DESENV:SQL>/

LINHA

SPOOL C:\EMPS_DEPTO_10.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=10;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_20.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=20;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_30.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=30;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_40.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=40;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_42.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=42;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_43.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=43;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_44.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=44;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_75.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=75;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_77.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=77;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_78.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=78;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_79.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=79;
SPOOL OFF

11 linhas selecionadas.

scott@DESENV:SQL>

==> pronto, basta eu pedir pro sqlplus gravar essa saída na tela :

C:\Desenvolvimento\sqlplus>type run.sql
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0 EMBEDDED ON HEAD OFF
SPOOL C:\GERA_ARQS.SQL
select ‘SPOOL C:\EMPS_DEPTO_’ || to_char(T. DEPTNO, ‘FM00’) || ‘.TXT’
|| chr(13) || CHR(10) || ‘SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=’ || T.DEPTNO || ‘;’
|| chr(13) || chr(10) || ‘SPOOL OFF’ as LINHA from dept T;
SPOOL OFF
EXIT

=> executo esse ‘script que gera outro script’ :

C:\Desenvolvimento\sqlplus>sqlplus scott/tiger@desenv @run.sql

SQL*Plus: Release 10.2.0.5.0 – Production
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Conectado a:
Oracle Database 10g Release 10.2.0.5.0 – 64bit Production

Desconectado de Oracle Database 10g Release 10.2.0.5.0 – 64bit Production

==> PRONTO, olha lá gerado o sctipt que faz TODOS os meus SPOOLs pra cada depto lido :

C:\Desenvolvimento\sqlplus>type c:\GERA_ARQS.SQL
SPOOL C:\EMPS_DEPTO_10.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=10;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_20.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=20;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_30.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=30;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_40.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=40;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_42.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=42;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_43.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=43;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_44.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=44;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_75.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=75;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_77.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=77;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_78.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=78;
SPOOL OFF

SPOOL C:\EMPS_DEPTO_79.TXT
SELECT DEPTNO, EMPNO, ENAM FROM EMP WHERE DEPTNO=79;
SPOOL OFF

C:\Desenvolvimento\sqlplus>

==> Eu PODERIA executar esse script c:\GERA_ARQS.SQL dentro do próprio RUN.SQL, se quisesse….

Okdoc ?? E óbvio, isso é só um EXEMPLO, há muitas Outras técnicas de programação que podem ser usadas no sqlplus…

[]s

Chiappa

OBS : eu usei sqlplus aqui no meu exemplo, mas a MAIORIA dos comandos também é aceito no SQL DEVELOPER executado em modo texto (com F5) ou no SQLCL – experimente aí e veja….

[Autor]

Posts