I have created below sql script where i am trying to parameterized it using zone and market ans saved this script as wrapper.sql
define zone1= &1.
define market1= &2.
set lines etc
SET ECHO OFF
SET FEEDBACK OFF
SET HEAD OFF
SET LIN 256
SET WRAP OFF
SET PAGES 0
spool H:\extract_&zone1&market1..csv
select 'zone' ||','||
'market' ||','||
'id'
result from dual
union all
select zone ||','||
market ||','||
id
from soltuion_report
where rownum <5 and zone = &zone1 and market = &market1;
/
spool off
Now as i have 23 zones with respect to the market i have created 23 wrapper script and saved it in file wrapper_script.sql. I want to generate 23 excel files for each zone with respect to their market.
@C:\Users\raw\Desktop\wrapper.sql As Asia
.
.
.
@C:\Users\raw\Desktop\wrapper.sql AF Africa
But when i am trying to run this wrapper_script.sql then its generating the 23 csv files but there is no data in the file. I am using oracle sql developer client to run this scripts as i dont have access to sql plus.
I am not sure whether i am using spool on the wrong place or i am calling it wrong as i am passing string values in wrapper?
Also is there any other way around so that instead of creating 23 wrapper files it can optimize the work.
Below is my select query result and i believe i have to format the string for zone and market column.
zone,market,boutique_id
EUR,Belgium,1233
EUR,Portugal,1345
EUR,Portugal,1567