create a spool file based on values passed from korn shell to sql script

Applications Programming
1

this is my issue.
4 parameters are passed from korn shell to sql script.

 
parameter_1=  varchar2 datatype or no value entered my user.
parameter_2=  number datatype or no value entered my user.
parameter_3= number datatype or no value entered my user.
parameter_4= number datatype or no value entered my user.

But I have to use these 4 parameters in the select statement. And this is my problem. How to initialize these parameters to get all rows when the user does not enter any value?

2

And what values do you want to pass to your database in case the user doesn't pass a value?

3

may be anything . Can I pass an empty string " " or any special character ?

4

You can pass whatever you want. The result of your SQL statement depends on those values, so they are rather important.

5

Okay. If i pass an empty string as in " " , can you please tell how can I write the select statement that will work no matter what value(s), the user enters.
The user can pass 4 parameters or choose not to pass any value for the 4 paramaters or just pass 1 st and 2nd parameters etc .

6

You could use something like this:

_where=
for p; do
  [ -n "$_where" ] &&
    _where="$_where and $p" ||
      _where="where $p"    
done
      
select 
  col1, col2 ..
from
  table_name
"$_where";

And invoke the script like this:

./script_name 'col1 = val1' 'col2 = val2' ...

Consider the following example and output:

% cat s
_where=
for p; do
  [ -n "$_where" ] &&
    _where="$_where and $p" ||
      _where="where $p"    
done
      
cat << !
select 
  col1, col2 ..
from
  table_name
$_where;
!

% ksh s "col1 = 'val1'" 'col2 = val2'
select 
  col1, col2 ..
from
  table_name
where col1 = 'val1' and col2 = val2;

You could invoke it without arguments too and it will generate valid SQL:

% ksh s                              
select 
  col1, col2 ..
from
  table_name
;