not able to pass a var to shell from anonymous PL/SQL block

February 17, 2012 at 06:20:50
Specs: Windows Vista
not able to pass a paramter from pl/sql to unix

See More: not able to pass a var to shell from anonymous PL/SQL block

Report •

#1
February 17, 2012 at 06:31:12
I have shell script pasted below,in that I have a anonymous PL/SQL block having variables v_count1 and v_user now I want to pass these variables to shell script but after pl/sql block is over when I do echo of variable it gives me message variable not set and though in the log file I see var v_user and v_count1 has been populated but I need use these values immedaitely after the pl/sql block in shell script can anyone suggest some simple sol=========================================================
#!/usr/bin/ksh


########################################
# Read the profile to ensure correct setting for this user
########################################
. $HOME/.profile


#find /tmp/log.txt -mtime +7 -exec rm -rf {} \;
#LOG=/tmp/log.txt

#To set the Date Variable
DATE=$(date -u +%a%d%b)
HOST=`hostname -s`
echo $DATE

# Find and Delete the logs which are older than 7 days.
find /batch/logs -name "*skinnylog*.txt" -mtime +7 -exec rm -rf {} \;
#find /batch/logs/*log*.txt -mtime +7 -exec rm -rf {} \;
LOG=/batch/logs/log_$DATE.txt

# CASE Structure for the HTML Tag toList

case "$HOST" in
"ddddddd") echo "Host is dev"
ENV='DSK'

toList="To:bhatiaprash@hotmail.com"
echo $ENV
echo $toList
;;
"uuuuuuu") echo "Host is UAT"
ENV='USK'
toList="To:bhatiaprash@hotmail.com"
echo $ENV
echo $toList
;;
"pppppppp") echo "Host is PROD"
ENV='PSK'
toList="To:bhatiaprash@hotmail.com"
echo $ENV
echo $toList
;;
esac

# OTHER HTML Tags

#toList="To:bhatiaprash@hotmail.com"
subject="Subject:Users logged in prior been cleared - Intimation for $HOST : "
tags1='Mime-Version: 1.0'
tags2='Content-type: text/html; charset="iso-8859-1"'
tags3='<html><head><body>'
headerTable='<TABLE width=500 border=1 CELLSPACING=3><TR align=center bgcolor=#DDDDDD><TD>Name of the User</TD><TD>Home Branch</TD></TR>'
headerTag1='<TR><TD align=center bgcolor=lightgreen>'
innerTag1='<TD align=center bgcolor=lightgreen>'
headerTag2='<TR><TD align=center bgcolor=red>'
innerTag2='<TD align=center bgcolor=red>'
headerTag3='<TR><TD align=center bgcolor=orange>'
innerTag3='<TD align=center bgcolor=orange>'
endTable='</TD></TR></TABLE>'


# HTML Logs
#find /batch/logs/*linesMonitoring*.html -mtime +7 -exec rm -rf {} \;

# Delete logs older than 7 days generated as a part of this script execution.

find /batch/logs -name "*UsersLoggedIn*.html" -mtime +7 -exec rm -rf {} \;

log=/batch/logs/UsersLoggedIn_1_$DATE.html


sqlplus / <<EOF >> $LOG
whenever sqlerror exit 1
whenever oserror exit 2
set serveroutput on

declare
v_count1 number :=0 ;
cursor c1 is
select user_id from current_users;
begin
dbms_output.put_line('Start *******');
for i in c1 loop
delete from current_users where user_id=i.user_id;
dbms_output.put_line('user is '||i.user_id);
v_user:=i.user_id;
v_count1:= v_count1+1;
end loop;
dbms_output.put_line('user is '||v_user);
dbms_output.put_line('total count is '||v_count1);
commit;
exception
when others then
dbms_output.put_line(' unable to delete'||sqlerrm);
END;
/

set serveroutput off

exit
EOF
echo $v_user
echo "prashant"

echo $toList"\n"$subject"\n"$tags1"\n"$tags2"\n"$tags3 > $log
echo "
DETAILS of the USER LOGGED IN

" >> $log
echo $headerTable >> $log
echo hostname is $HOST


echo $endTable >> $log

cat $log | sendmail -t -F"Users Logged IN"


Report •

#2
February 17, 2012 at 13:37:04
If the output of the sqlplus script is not real large, consider using using ksh command substitution to trap the output in a variable and then parse that variable to get the required data:

myoutput=$(sqlplus / <<EOF >> $LOG
whenever sqlerror exit 1
whenever oserror exit 2
set serveroutput on
.
.
.
set serveroutput off

exit
EOF
)

If it were me, I would only have this for an output:

dbms_output.put_line(v_user);

The 'user is' stuff probably isn't needed. Also, I would eminate the count because the count can be determined by looking at the output of variable: myoutput.

Another alternative is sending the output of your sqlplus to a file and reading that file into the script.



Report •
Related Solutions


Ask Question