How can i pass parameter to sql script ?

Question

CREATE TABLE DMS_POP_WKLY_REFRESH_20100201 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 (+)

vijay.sql ( name given to above sql script)

How can i pass a parameter for 20100201 in side the sql script, I need to change every time this date format. how can i call this sql script and run it in unix by passing date as parameter. so that date in the script should be modified in every run

Please help me.

Answer 1

Hi,

SQL*Plus uses &1, &2... &n to access the parameters.

Suppose you have the following script test.sql:

SET SERVEROUTPUT ON
SPOOL test.log
EXEC dbms_output.put_line('&1 &2');
SPOOL off

you could call this script like this for example:

$ sqlplus login/pw @test Hello World!

Edit:

In a UNIX script you would usually call a SQL script like this:

sqlplus /nolog << EOF
connect user/password@db
@test.sql Hello World!
exit
EOF

so that your login/password won't be visible with another session's ps

Answer 2

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'