pass value from Oracle sql to Korn shell

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.

Thanks
Megha.

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

Hi

The tried the above and I still get the same output.
MMDD is
The value is not being displayed.

Thanks
Megha

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?

Try something like this:

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...).

Still no luck . Am not sure why the process is unable to display the value.
Please let me know what I am missing here.

Thanks
Megha

What does the query return when you execute it directly in sqlplus? Are you sure that userdate in the returned row is not null?

You are saying "select to_char(sysdate,'MMDD') x from dual;" returns blank?

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 ----------

Hi DGPickett

When I run my SQL query from korn shell , the output is blank .
Megha.

Fix that: The Oracle CURRENT_DATE Function

Hi DGPickett,

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 ?

Thanks
Megha

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.

tyler_durden

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