Hi,
>
> Writting some informix migration scripts here and just got a bit stucked.
>
> When we invoke sql scripts in sqlplus with arguments such as
>
> @test.sql par1 par2 par3
>
> par1, 2 and 3 can be treated as &1, &2 and &3
>
>
> I have a situation where the number of arguments can vary so I can have from
> par1 only up to par3 inclusive.
>
> Basically does anyone know if there is something similar as $# in ksh but for
> SQLPLUS? Or anyway to treat do something like NVL('&2', ';') (this does not
> seem to work as it prompts and asks for a value)
>
a solution independently whether there is a connect to a database or not is to
write all define variables
into a spool file using the define command without any parameter and then
remove all define's which
are not parameter value defines from this file
you may then fill all possbile parameters with a default value.
call now the previous created file to restore the define variables (it is
callable like any sql file)
this will restore all real parameters while the parameters missed in the call
are hold its default value
now you may access it without any prompting.
I wrote a mechanism to do some handling with parameters some years ago.
addtionally to the
store-restore mechanism it includes mechanisms to for shift and unshift,
collapse all parameters
into the first one in a manner of ksh "$*" or optionally of ksh "$@", and also
a getopt handling.
after storing the actual defines it first creates a ksh script and excutes it.
the ksh script then creates
a sql script which does the necessary define and undefine commands to get the
expected changes
and deletes itself. the generated sql script is then called to do this and
deleted after call.
regards
kf
--
http://www.freelists.org/webpage/oracle-l