Optional parameter execution while executing .sql file ..

From: Ranjeesh K R. <ranjeeshk_at_infics.com>
Date: Tue, 10 Aug 2004 23:50:20 +0530
Message-ID: <A70AE049E81A6A40879D5DC0AC8C38C9DB1CD2@venus.infics.com>

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Tue Aug 10 2004 - 13:12:16 CDT