SQL & PL/SQL

Hi,

I came across a funny issue while trying to modify an EXISTING program to a GENERIC program,
wsa trying to keep the changes minimal. In short ..


Intermediate( 2 parameters To 2 parametrs )
------------------------------------------------------------------

I am executing a .sql file from shell script with 2 parametrs.

sqlplus -s ${ID_PWD_STRING} @${COMMON_SQL}/end_reportid.sql $PARAM1 $PARAM2

inside the end_reportid.sql, the main line of execution is

execute dss_report_tracker.complete_report(&1,&2);

This works perfectly fine. But this is NOT the original one .

Original one is ( 1 Parameter to 1 Parameter )
-----------------------------------------------------------------

sqlplus -s ${ID_PWD_STRING} @${COMMON_SQL}/end_reportid.sql $PARAM1

&

execute dss_report_tracker.complete_report(&1)

Inside the dss_report_tracker.complete_report procedure parameter 2 is optional already hence ok.

hence this too works fine

Problem Here !!! (1 Parameter to 2 Parameters)
-----------------------------------------------------------------------

problem is while trying to execute

with one parameter , while it is expecting 2 parameters .

sqlplus -s ${ID_PWD_STRING} @${COMMON_SQL}/end_reportid.sql 12345

inside end_reportid.sql
------------------------------------
execute dss_report_tracker.complete_report(&1,&2);

as the second line is not getting the first parameter , it stops to key in the second one
(asks for values)?.

We want to run the program , from some places with one parameter, and from some other places
with 2 parameter. Inside the procedure complete_report second parameter is defaulted to 'VALID'.
So no issues there.

So how to modify the line
execute dss_report_tracker.complete_report(&1,&2);
so as to expect the parameter 2 as optional

I tried something like this ..
--------------------------------------

execute dss_report_tracker.complete_report(&1,nvl(&2,'VALID'));

where 'VALID' was the default parameter value inside the procedure

but here too while executing , It is asking for the &2 value

any thoughts on how to modify the above line so as to make the second parameter optional ??.

with thanks in advance

Ranjeesh