Two options save vijay.sql
declare
begin
execute immediate
'CREATE TABLE DMS_POP_WKLY_REFRESH_'||to_char(sysdate,'YYYYMMDD')||' NOLOGGING PARALLEL AS
SELECT wk.*,bbc.distance_km ,NVL(bbc.tactical_broadband_offer,0) tactical_broadband_offer ,
sel.tactical_select_executive_flag,
sel.agent_name,
res.DMS_RESIGN_CAMPAIGN_CODE,
pclub.tactical_select_flag
FROM spineowner.pop_wkly_refresh_20100201 wk,
dms_bb_coverage_102009 bbc,
dms_select_executive_group sel,
DMS_RESIGN_CAMPAIGN_26052009 res,
DMS_PRIORITY_CLUB pclub
WHERE wk.mpn = bbc.mpn(+)
AND wk.mpn = sel.mpn (+)
AND wk.mpn = res.mpn (+)
AND wk.mpn = pclub.mpn (+)'
end;
/
The above will generate table names automatically based on sysdate. If you still need to pass as variable, then save vijay.sql as
declare
begin
execute immediate
'CREATE TABLE DMS_POP_WKLY_REFRESH_'||&1||' NOLOGGING PARALLEL AS
SELECT wk.*,bbc.distance_km ,NVL(bbc.tactical_broadband_offer,0) tactical_broadband_offer ,
sel.tactical_select_executive_flag,
sel.agent_name,
res.DMS_RESIGN_CAMPAIGN_CODE,
pclub.tactical_select_flag
FROM spineowner.pop_wkly_refresh_20100201 wk,
dms_bb_coverage_102009 bbc,
dms_select_executive_group sel,
DMS_RESIGN_CAMPAIGN_26052009 res,
DMS_PRIORITY_CLUB pclub
WHERE wk.mpn = bbc.mpn(+)
AND wk.mpn = sel.mpn (+)
AND wk.mpn = res.mpn (+)
AND wk.mpn = pclub.mpn (+)'
end;
/
and then run as sqlplus -s username/password @vijay.sql '20100101'