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 ?
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 
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
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.
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
"${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 ?
PARAM1 = "${1-NULL}"
sqlplus -s /nolog @$sqlfile $PARAM1
Yes.
Or just do it right there.
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.
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.
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
$
$
$ # Here's my Oracle SQL script
$
$ cat -n myscript.sql
1 set verify off feed off time off timing off
2 declare
3 v1 varchar2(10);
4 v2 varchar2(10);
5 v3 varchar2(10);
6 v4 varchar2(10);
7 begin
8 v1 := '&1';
9 v2 := '&2';
10 v3 := '&3';
11 v4 := '&4';
12 --
13 if v1 is null then
14 dbms_output.put_line ('SQL variable v1 is NULL');
15 else
16 dbms_output.put_line ('SQL variable v1 is NOT NULL; its value is '||v1);
17 end if;
18 --
19 if v2 is null then
20 dbms_output.put_line ('SQL variable v2 is NULL');
21 else
22 dbms_output.put_line ('SQL variable v2 is NOT NULL; its value is '||v2);
23 end if;
24 --
25 if v3 is null then
26 dbms_output.put_line ('SQL variable v3 is NULL');
27 else
28 dbms_output.put_line ('SQL variable v3 is NOT NULL; its value is '||v3);
29 end if;
30 --
31 if v4 is null then
32 dbms_output.put_line ('SQL variable v4 is NULL');
33 else
34 dbms_output.put_line ('SQL variable v4 is NOT NULL; its value is '||v4);
35 end if;
36 end;
37 /
38 exit
39
$
$
$ # And here's my Unix shell script with login credentials replaced
$
$ cat -n tstscript.sh
1 #!/usr/bin/bash
2 PARAM1="$1"
3 PARAM2="$2"
4 PARAM3="$3"
5 PARAM4="$4"
6 ##
7 echo "Within the Unix script now..."
8 echo "Script parameter PARAM1 = \"$PARAM1\""
9 echo "Script parameter PARAM2 = \"$PARAM2\""
10 echo "Script parameter PARAM3 = \"$PARAM3\""
11 echo "Script parameter PARAM4 = \"$PARAM4\""
12 ##
13 echo "=============================================="
14 echo "About to call the Oracle script now..."
15 sqlplus -s user/passwd@db @myscript.sql "$PARAM1" "$PARAM2" "$PARAM3" "$PARAM4"
16
$
$
$ # I'll execute the Unix shell script now, passing four parameters
$ # that are in turn passed to the Oracle SQL script
$
$ ./tstscript.sh "AAA" "BBB" "CCC" "DDD"
Within the Unix script now...
Script parameter PARAM1 = "AAA"
Script parameter PARAM2 = "BBB"
Script parameter PARAM3 = "CCC"
Script parameter PARAM4 = "DDD"
==============================================
About to call the Oracle script now...
SQL variable v1 is NOT NULL; its value is AAA
SQL variable v2 is NOT NULL; its value is BBB
SQL variable v3 is NOT NULL; its value is CCC
SQL variable v4 is NOT NULL; its value is DDD
$
$
$ # Try out NULLs now
$
$ ./tstscript.sh "AAA" "" "CCC" "DDD"
Within the Unix script now...
Script parameter PARAM1 = "AAA"
Script parameter PARAM2 = ""
Script parameter PARAM3 = "CCC"
Script parameter PARAM4 = "DDD"
==============================================
About to call the Oracle script now...
SQL variable v1 is NOT NULL; its value is AAA
SQL variable v2 is NULL
SQL variable v3 is NOT NULL; its value is CCC
SQL variable v4 is NOT NULL; its value is DDD
$
$
$ # And more
$
$ ./tstscript.sh "" "" "CCC" ""
Within the Unix script now...
Script parameter PARAM1 = ""
Script parameter PARAM2 = ""
Script parameter PARAM3 = "CCC"
Script parameter PARAM4 = ""
==============================================
About to call the Oracle script now...
SQL variable v1 is NULL
SQL variable v2 is NULL
SQL variable v3 is NOT NULL; its value is CCC
SQL variable v4 is NULL
$
$
$ # And some more
$
$ ./tstscript.sh "" "BBB" "CCC" ""
Within the Unix script now...
Script parameter PARAM1 = ""
Script parameter PARAM2 = "BBB"
Script parameter PARAM3 = "CCC"
Script parameter PARAM4 = ""
==============================================
About to call the Oracle script now...
SQL variable v1 is NULL
SQL variable v2 is NOT NULL; its value is BBB
SQL variable v3 is NOT NULL; its value is CCC
SQL variable v4 is NULL
$
$
$ # And some more
$
$ ./tstscript.sh "" "" "" "DDD"
Within the Unix script now...
Script parameter PARAM1 = ""
Script parameter PARAM2 = ""
Script parameter PARAM3 = ""
Script parameter PARAM4 = "DDD"
==============================================
About to call the Oracle script now...
SQL variable v1 is NULL
SQL variable v2 is NULL
SQL variable v3 is NULL
SQL variable v4 is NOT NULL; its value is DDD
$
$
$ # And finally this...
$
$ ./tstscript.sh "" "" "" ""
Within the Unix script now...
Script parameter PARAM1 = ""
Script parameter PARAM2 = ""
Script parameter PARAM3 = ""
Script parameter PARAM4 = ""
==============================================
About to call the Oracle script now...
SQL variable v1 is NULL
SQL variable v2 is NULL
SQL variable v3 is NULL
SQL variable v4 is NULL
$
$
$
tyler_durden
---------- Post updated at 11:49 PM ---------- Previous update was at 06:20 PM ----------
It's actually quite easy to see what's going on if you pay close attention to that error message.
The Oracle PL/SQL engine doesn't quite understand the "equals" character ("=") at line 4 (you've used it at one place only). Hence it says this -
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
which means that it expected something but found something else at line 4.
The assignment operator that you intended at line 4 is actually ":=", and not "=". Hence the PL/SQL engine says this -
The symbol ":= was inserted before "=" to continue.
Of course, it's just a guess on its part, and it doesn't really fix the issue.
tyler_durden
Hi durden_tyler,
Thank you so much for the detailed scripts. I am doing the same thing now as you mentioned.
Here is the requirement.
I am passing 4 parameters from the concurrent manager in oracle apps .
param1 is a number data type
param2 is a varchar2 data type ( e.g: this is a test)
param3 and param4 are again number data types.
In the concurrent manager i am calling the korn shell script.
I am accepting the values passed from concurrent manager in korn shell and passing them to the sql script in exactly the same way as you said.
I am getting this error.
error 45 intializing SQL * Plus.
Internal error .
what could be the issue ?
No idea what the issue could be. Your post is not descriptive enough.
Cut and paste the portion of your application/tool where you see this error. Use code tags while posting.
tyler_durden
Attached is the screenshot of the error message
Have you checked the log file mentioned: "rpae850_extract.log"?
Maybe there you will find further information about the error reported!