sqlplus by example. Generate dynamic csv file

====================
General
====================
Generate csv file from database.
The output file name is set dynamically according to current month.

====================
Files List
====================
main_aircel.bat
main_aircel.sql
main_hsc_295.sql

====================
Code
====================
main_aircel.bat
sqlplus user/pass@orainst @main_aircel.sql

main_aircel.sql
@main_hsc_295.sql
@main_hsc_299.sql
@main_hsc_312.sql
@main_hsc_320.sql

@main_hsc_354.sql

main_hsc_295.sql
SET LINESIZE 400
SET COLSEP ,
SET TERMOUT OFF
SET FEEDBACK OFF
SET HEADING OFF
SET NEWPAGE NONE
SET SHOW OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET UNDERLINE OFF


COL SERVICE FOR A10
COL SERVICE_PROVIDER FOR A30
COL PL_CODE FOR A10
COL INR_TO_USD_RATE FOR A20
COL BILLING_AMOUNT_USD FOR A20

COL report_name_col NEW_VALUE report_name NOPRINT
VAR report_month        VARCHAR2(6);

BEGIN
  SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') INTO :report_month FROM DUAL;  
END;
/

SELECT 'AIRCEL_HSC_REP_295_'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')||'.csv' report_name_col FROM DUAL;
--DEFINE report_name = SELECT 'AIRCEL_HSC_REP_295_'||'201512'||'.csv' FROM DUAL;
SPOOL &report_name


SELECT 'Circle Name', 'Month', 'Answer_date', 'Service', 'Dialed Country', 'Service Provider', 'PL Code', 'Subscriber ID', 'Dialed Number', 'Long Number', 'Chargeable Duration (sec)', 'Billing Amount', 'Taarif', 'INR to USD Rate', 'Starhome Revenue INR','Starhome Revenue USD' FROM DUAL;
  
SELECT   
  CUSTOMERS.origin_gate_name,
  REP_TABLE.month,
  TO_CHAR(REP_TABLE.answer_date,'YYYYMMDD hh24:mi;ss') as answer_date,
  SUBSTR(decode(REP_TABLE.price_plan_code, 1, 'HSC', 7, 'ICA',price_plan_code),1,10) as service,  
  REP_TABLE.dialed_country,
  RTRIM(DWNG.SERVICE_PROVIDERS.name) AS service_provider,
  DWNG.SERVICE_PROVIDERS.pl_code,
  DWNG.TRANSACTION.subscriber_id,
  DWNG.TRANSACTION.dialed_number,
  DWNG.TRANSACTION.long_number,
--  REP_TABLE.answer_data_loc,
  REP_TABLE.sec_chargeable_duration AS chargeable_duration_sec,
  REP_TABLE.transactionbilling AS billing_amount, 
  REP_TABLE.v_taarif AS taarif,
  TO_CHAR(REP_TABLE.inr_rate,'0.9999999') AS inr_to_usd_rate,
  TO_CHAR(REP_TABLE.sh_share,'000.9999999') AS starhome_reveniew,
  TO_CHAR(REP_TABLE.sh_share*REP_TABLE.inr_rate,'000.9999999') AS starhome_reveniew_usd
FROM
  DWNG.TRANSACTION,
  DWNG.SERVICE_PROVIDERS,
  CUSTOMERS,
  REP_AIRCEL_IND_BILL REP_TABLE,
  B_BILLING_GATES
WHERE 1=1
  AND CUSTOMERS.customer_id = '295'
  AND REP_TABLE.origin_gate_id = '295'
  AND CUSTOMERS.origin_gate_id = B_BILLING_GATES.gate_id  
  AND B_BILLING_GATES.gate_id  = REP_TABLE.origin_gate_id
  AND B_BILLING_GATES.gate_id = DWNG.TRANSACTION.origin_gate_id(+)    
  AND DWNG.TRANSACTION.associated_call_ref = REP_TABLE.associated_call_ref  
  AND DWNG.SERVICE_PROVIDERS.naming_convention=REP_TABLE.service_provider_name(+)    
  AND DWNG.TRANSACTION.completed_ind  =  1
  AND REP_TABLE.price_plan_code  =  1 --HSC
  AND REP_TABLE.month  =  :report_month
ORDER BY origin_gate_name, month, dialed_country, DWNG.SERVICE_PROVIDERS.name, DWNG.SERVICE_PROVIDERS.pl_code, DWNG.TRANSACTION.subscriber_id;


SPOOL OFF;