Hi All ,
I am trying to pass a value from sqlplus to korn shell .
There is a table tab1 in Oracle that has a column userdate.
I need to pass the userdate to the korn shell . This is what I am doing .
VALUE=`sqlplus -silent username/password << END
set pagesize 0 feedback off verify off heading off echo off termout off
set serveroutput on
variable retval number;
begin
SELECT to_CHAR(userdate,'MMDD') into :retval FROM tab1 where rownum=1;
end;
/
exit :retval;
END`
echo "MMDD IS $VALUE"
But the output is only
MMDD is
I am not getting a value . For example if the userdate in the table tab1 is 9/20/2012 I must be getting the output of my korn shell script as
0920 . All I am getting is, MMDD is
Please help.
Usually I get my data from stdout. Often I filter it out like this, so I get exactly what I want and all messages go to a log for debug:
export MMDD=$( echo "select
'dAtA' || to_char(userdate,'MMDD') || 'eNdATa' x
from dual ;
" | sqlplus ... 2>&1 | sed '
w logfile
s/.*dAtA\(.*\)eNdAtA.*/\1/
t
d
' )
UNIX exit() returns a character 0-255 status, not enough for MMDD, and it is for error checking with:
some_command some_args
RET=$?
if [ $RET != 0 ]
then
echo error return: $RET >&2
fi
Must be an SQL issue, and I do not have an Oracle this gig. Try CURRENT_DATE or SYSDATE inplkace of userdate. Google says userdate is not defined. Maybe knock off the -silent, as my sed moves all that to the log?
VALUE=`sqlplus -silent username/password << END
set pagesize 0 feedback off verify off heading off echo off termout off
SELECT to_CHAR(userdate,'MMDD') FROM tab1 where rownum=1;
END
`
echo "MMDD IS $VALUE"
No need for PL/SQL here and stdout fits your needs better than the returnvalue of SQL*Plus (leading zero is not stripped...).
Hi Cero,
Thank you for the reply.
Yes. I am very sure on that part.When I run the query in sqlplus , I get the userdate in the format that I am using in the query. ie, MMDD . I do get a value .
I still cant find out whats causing the problem.
Thanks
Megha
---------- Post updated at 09:41 AM ---------- Previous update was at 09:40 AM ----------
I am facing this issue when I try to display the value of the sql query from the korn shell. even if its userdate or sysdate .
this is my code
VALUE=`sqlplus -s /nolog @$PASSWORD_FILE << END
set pagesize 0 feedback off verify off heading off echo off
SELECT to_char(userdate,'MMDD') userdate FROM tab1 where rownum=1;
exit;
END`
echo "----------------"
echo $VALUE
print "above line has the value"
The output is as follows
----------------
Connected. 0920
above line has the value
Instead the output must be only 0920.
Is there a way I can suppress 'Connected.'
Please help.
Thanks
Megha
---------- Post updated at 03:46 PM ---------- Previous update was at 01:09 PM ----------
So I tried this .
VALUE=`sqlplus -s /nolog @$PASSWORD_FILE | sed "s/[^0-9]//g" << END set pagesize 0 feedback off verify off heading off echo off SELECT to_char(userdate,'MMDD') userdate FROM tab1 where rownum=1; exit; END` echo "----------------" echo $VALUE print "above line has the value"
Can anybody tell me why is sed not able to get rid of that string connected and just display those numbers 0920 ?
VALUE=`sqlplus -s /nolog @$PASSWORD_FILE <<END
set pagesize 0 feedback off verify off heading off echo off
SELECT to_char(userdate,'MMDD') userdate FROM tab1 where rownum=1;
exit;
END`
set -- $VALUE
echo "----------------"
echo $2
print "above line has the value"
$
$
$ cat f70.sh
VALUE=`sqlplus -s /nolog <<END
connect test/test@ora11g
set pagesize 0 feedback off verify off heading off echo off time off timing off
SELECT to_char(sysdate,'MMDD') FROM dual;
exit;
END`
echo $VALUE
$
$
$ ./f70.sh
0925
$
$
$
You may replace the query in red by your own, provided it returns exactly 1 row.
Another note: you may not require "set time off timing off" sqlplus commands.
I had to set them off because my login.sql sets them on everytime I log in to sqlplus. Experiment with the sqlplus commands to determine the bare minimum for your case.
Usually MAX() or the like is a good way to ensure there is only one row out, but rownum=1 might be cheaper and OK if there is only one value. On one hand, MAX() folds all the rows together inside the engine, but rownum=1 allows a cursor to be created and filled, then discards each following row one by one at the presentation side. There are sample functions cheaper than max(), which evaluates every row, like FIRST_VALUE(), but it might insist on a sort: FIRST_VALUE but doc says OVER() can be empty: SQL Functions
or FIRST (also needs a sort): FIRST
Hint for first rows 1 helps: Comments
On a no-problem day, the sqlplus options pile is a fine way to return just the value, but on those other days, often including day 1 as you debug, for errors it is a bit of a train wreck. The sed and dAtA...eNdAtA approach works portably on all SQL tools, and saves the informative stuff to a log file for production support and debug.
I had a similar problem and this worked well for me:
#! /usr/bin/ksh
VALUE=`sqlplus -silent user/password@instance <<END
set pagesize 0 feedback off verify off heading off echo off
select to_CHAR(userdate,'MMDD') FROM tab1 where rownum=1;
exit;
END`
if [ -z "$VALUE" ]; then
echo "No rows returned from database"
exit 0
else
echo $VALUE
fi