How to do dynamic spool's using sqlplus by batch-file

Question

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:\Users\enrique.erbs\Desktop\JOB\Tools\extrator_parametros\queryoracle.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 ?

Answer 1

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