Shell Programming and Scripting

There are 4 parameters that I have to pass from korn shell to sql script.

1) I have to check if $1 , $2 , $3 and $4 are null values or not .
How can I do that ?

2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script from the korn shell script ?

Code:

if [ -z "$1" ] 
then
        echo "param 1 is null"
fi

If you want null as in empty strings, just pass empty strings in. if you want null as in database null, pass in NULL without quotes instead.

Is this how ? am confused

Code:

if [ -z "$1" ]     
then
        echo "param 1 is null"
        $1 = NULL   OR (is it   $1 ="")  
fi

eg:
i want to pass something like this after checking for null values

Code:

sqlplus -s /nolog @$sqlfile $1 $2 $3 $4

based on this the sql script shlould check for null .
i am using nvl function in sql to handle this.

please help.

Moderator's Comments:

Video tutorial on how to use code tags in The UNIX and Linux Forums.

Not knowing how you were using these variables, I couldn't give any more detail. Now that you've shown how I can help you a little better.

Unfortunately I'm not familiar with how sqlplus assigns things on a commandline. It may need some special syntax to input a real database NULL rather than a string containing the letters "NULL". If you can do so, it may be easier to check inside your sql script for zero-length strings.

You can't assign to $1.

What you can do is

Code:

"${1-NULL}"

which will cause it to evaluate to the string NULL whenever $1's blank, and its usual content otherwise.

Hi Corona668

Do u mean I can pass the parameters this way ?

Code:

PARAM1 = "${1-NULL}" 
 
sqlplus -s /nolog @$sqlfile $PARAM1

Yes.

Or just do it right there.

Code:

sqlplus -s /nolog @$sqlfile "${1-NULL}"

I'm not sure if this will result in a database NULL or the string "NULL". You might have better luck just leaving it as "$1" and seeing if the resulting "" is interpreted as a database NULL.

Code:

sqlplus -s /nolog @$sqlfile "${1-NULL}" $2 $3 $4

I have not passed any value for $1 ... so i know $1 is empty.
I passed in values for $2 $3 $4.
whenever $1 $2 $3 $4 are passed from shell to sqlplus they are referred to as &1 &2 &3 &4 .

now we know that I passed no value for $1 ,
but in sql script what is happening is the first non null value that is from shell i.e, $2 in our case now ... is being treated as &1 .

so its not working

---------- Post updated at 04:56 PM ---------- Previous update was at 04:38 PM ----------

Do u know how can I display the values passed from korn shell in the sql script?
for eg in the korn shell i am passing a value like this.

Code:

sqlplus -s /nolog @sqlpfile $1
 
 
in the sql script i am doing this.
declare
var1 varchar2(50);
begin
var1='&1';
dbms_output.put_line(var1);
end;

i am getting an error
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.

not sure whats going on