sqlplus spool file with parameter

Question

I have an sql file which is executed from command line, it works fine.

sqlplus username/password@DBInstance @filename.sql

My filename.sql looks about this:

set colsep ';'
set echo off
set feedback off
set sqlprompt ''
set trimspool on
set headsep off
set termout off
--set define off
set serveroutput on
set verify off
set autoprint off
set pagesize 0
set linesize 9999 
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';

spool STOCK.csv

SELECT ils.item
  || ';'
  || ';'
  || ';'
... 
Where loc = store_no
--store_no is typed in the script by hand--
...;
spool off
exit

After running sqlplus command, i get csv file with all selected rows, that are delimetered by ';' for specific value store_no, everything works fine. What i need is to send store_no as a parameter and for each parameter get new corresponding *csv file. So i could run, for example:

sqlplus username/password@DBInstance @filename.sql 3

And get *csv file where store_no = 3. I've searched for some solutions and didn't get the right one. I think i'm not far with this solution:

set colsep ';'
set echo off
set feedback off
set sqlprompt ''
set trimspool on
set headsep off
set termout off
--set define off
set serveroutput on
set verify off
set autoprint off
set pagesize 0
set linesize 9999
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';

spool STOCK.csv

DECLARE
store_no number := &1;
BEGIN

SELECT ils.item
      || ';'
      || ';'
      || ';'
    ... 
    Where loc = store_no
    ... ;
END;
/
spool off
exit

As output i get this: PLS-00428: an INTO clause is expected in this SELECT statement

Considering to such output, i need variable to store there whole output. But i don't know how to keep correctly reading *csv file using DBMS_OUTPUT.PUT_LINE Can anyone please help how to perform this task? Thanks in advance!

Aleksej · Accepted Answer · 2018-09-11T15:44:47.293

You don't need a PL/SQL block for this.

Say you have a table like this:

create table yourTable(loc number, item varchar2(10));
insert into yourTable values (1, 'one');
insert into yourTable values (2, 'two');
insert into yourTable values (3, 'three');

With a script test.sql like this:

set colsep ';'
set echo off
set feedback off
set sqlprompt ''
set trimspool on
set headsep off
set termout off
set serveroutput on
set verify off
set autoprint off
set pagesize 0
set linesize 9999
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';

spool d:\temp\STOCK.csv

SELECT item
      || ';'
      || ';'
      || ';'
    from yourTable 
    Where loc = &1;
spool off
exit

and this call:

sqlplus usr/pwd@DB @test.sql 3

you get a file like:

three;;;

If you want to spool to different files based on the parameter value, you can edit the spool command, for example this

spool d:\temp\STOCK_&1..csv

will give a file STOCK_3.csv if you pass 3 as parameter value

It worked, thank you! Maybe you know how to pass parametres into spooled filename? I need, for example, to create *csv file with such name: STOCK_{store_no}_{current_date}_{launch time}.csv, where {store_no},{current_date},{launch time} are parametrs. I know how to do this using bash and --sed-- command, but in that case it will generate *.sql file for each launch_time. What i want is to run everything using only one script. — Denis Babichev, Sep 11 '18 at 15:41
You can use the parameter quite the same way; just edited to show you an example — Aleksej, Sep 11 '18 at 15:45

sqlplus spool file with parameter

1 Answers1