Hi ,
I need help on how to check the value of the input parameters passed from the unix script to the procedure.
The requirement is that , we run the unix script call account_details which accepts two dates as inputs as command line arguments.
eg :./ account_details.sh 01-APR-2011 03-APR-2011
Here we may pass the dates or no dates as inputs to the script
./account_details.sh
In the script we call a stored procedure which acceptstwo dates as parameters. I pass the dates through the unix script to the procedure. But the problem is when I pass the date , Iam able edit that value in the procedure , but when I do not pass the value , Iam unable to check whether it is null or space etc.
Can anyone help me in this regard?
Unix Script:
#!/bin/sh
MON=`/bin/date +%b`
user=sam
pass=sam1
Today=`date +%h-%y`
db=emp
#if [ $# -ne 2 ]; then
#echo "Error : No of Parameters mismatch : scriptname fromdate todate eg: 01-APR-2011"
#exit 1
#fi
fromdate=$2
todate=$3
var=`sqlplus -s $user/$pass@$db <<EOF
set head off
set pagesize 0
set linesize 3000
set feedback off
set echo off
set feed off
set verify off
set trimspool on
#set termout off
set colsep ','
set sqlprompt ''
set echo off
set sqlnumber off
SET SERVEROUTPUT ON;
declare
v_status number(12);
v_errmsg varchar2(4000 byte);
begin
pull_charges (to_date('$fromdate','DD-MON-YYYY'),to_date('$todate','DD-MON-YYYY'),v_status, v_errmsg);
if v_status=1 then
DBMS_OUTPUT.PUT_LINE('Status=1');
Else
DBMS_OUTPUT.PUT_LINE ('Status=0'||v_errmsg);
END IF;
END;
/
exit
EOF`
# $var is the returned value as a Unix variable
status=`echo "$var" | cut -c8`
if [ "$status" -eq 0 ]; then
echo "Success"
exit 0
else
echo $var
exit 1
fi
Procedure Code:
create oe replace procedure pull_charges (v_fromdate date , v_todate date,v_status out number , v_errmsg out varchar2)
is
begin
---- here I need to add teh code to test whether is null or contains any other value like white spac etc other then dates. If I add the below code i get an errror while compiling
If v_fromdate is null or v_from_date=" ") then
----business logic
end if;
exception
when others then
v_status:=0;
v_errmsg:=SQLCODE || sqlerrm;
end;