Re: sqlplus substitution variables

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