Styjun

I'm trying save multiples txt archives with querye result using a single .sql archive called by a batch-file for automate-work.

My Batch-file:

set d=%location%result.txt
set f=%location%result2.txt 
echo exit | sqlplus GOVMANPATCHORACLE/GOVMANPATCHORACLE@%SERVER%/%SERVICE% @C:Usersenrique.erbsDesktopJOBToolsextrator_parametrosqueryoracle.sql '%d%' '%f%'

*'%d%' contains the save locale to spool

My archive .sql (queryoracle.sql):

 SET PAGESIZE 50000
 SET LINESIZE 800
 SET VERIFY OFF
 SET ECHO ON
 SET HEAD ON
 SET SERVEROUTPUT ON;
 SET PAGES 0
 SET DEFINE ON;
 COLUMN COD_PARAMETRO FORMAT a100 HEADING COD_PARAMETRO;
 COLUMN DES_PARAMETRO FORMAT a100 HEADING DES_PARAMETRO;
 COLUMN DES_VALOR FORMAT a100 HEADING DES_VALOR;
 DECLARE 
 myvar1 varchar2(30);
myvar2 varchar2(30);
 BEGIN
 myvar1 := '$1';
myvar2 := '$2';
 SPOOL 'myvar1'
 SELECT TDFE_PARAMETRO_APLICACAO.COD_PARAMETRO AS COD_PARAMETRO, TDFE_PARAMETRO.DES_PARAMETRO AS DES_PARAMETRO, TDFE_PARAMETRO_APLICACAO.DES_VALOR AS DES_VALOR FROM TDFE_PARAMETRO_APLICACAO INNER JOIN TDFE_PARAMETRO ON(TDFE_PARAMETRO_APLICACAO.COD_PARAMETRO=TDFE_PARAMETRO.COD_PARAMETRO);
 SPOOL OFF
SPOOL 'myvar2'
 SELECT * FROM TDFE_PARAMETRO_APLICACAO;
 SPOOL OFF
 END;

I tryed some options and the most closest to get the result what i want is using ">> %location%result.txt" at end of sqlplus call.

Somebody can help me ?

You were not far at all. Actually, When executing SQLPLUS and passing him a file to execute and arguments, It will replace every single occurence of &1 By your first Argument, Every occurence of &2 by your second argument, etc ...

This should work :

 SET PAGESIZE 50000
 SET LINESIZE 800
 SET VERIFY OFF
 SET ECHO ON
 SET HEAD ON
 SET SERVEROUTPUT ON;
 SET PAGES 0
 SET DEFINE ON;

 COLUMN COD_PARAMETRO FORMAT a100 HEADING COD_PARAMETRO;
 COLUMN DES_PARAMETRO FORMAT a100 HEADING DES_PARAMETRO;
 COLUMN DES_VALOR FORMAT a100 HEADING DES_VALOR;

 SPOOL '&1'

 SELECT TDFE_PARAMETRO_APLICACAO.COD_PARAMETRO AS COD_PARAMETRO, TDFE_PARAMETRO.DES_PARAMETRO AS DES_PARAMETRO, TDFE_PARAMETRO_APLICACAO.DES_VALOR AS DES_VALOR FROM TDFE_PARAMETRO_APLICACAO INNER JOIN TDFE_PARAMETRO ON(TDFE_PARAMETRO_APLICACAO.COD_PARAMETRO=TDFE_PARAMETRO.COD_PARAMETRO);

 SPOOL OFF
 SPOOL '&2'

 SELECT * FROM TDFE_PARAMETRO_APLICACAO;

 SPOOL OFF

For reference : https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve044.htm#SQPUG127