CODEWITHSUNDEEP
sqloracle-databasesqlplusspool
Denis Babichev
Denis Babichev

Reputation: 61

sqlplus spool file with parameter

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!

Upvotes: 3

Views: 10417

Answers (1)

Aleksej
Aleksej

Reputation: 22969

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

Upvotes: 5

Related Questions