|
|
pass null value to sql script from korn shell script
11-09-2011
$ $ $ # 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 $ $ $
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;The symbol ":= was inserted before "=" to continue.|
|
|
|