troubleshooting Question

help on unix script

Avatar of Swaminathan_K
Swaminathan_KFlag for India asked on
Linux OS Dev
11 Comments1 Solution557 ViewsLast Modified:
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;

ASKER CERTIFIED SOLUTION
farzanj

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros