I'm trying to accomplish this:

estimates=`sqlplus -s $user/${password} << " EOF"
set heading off feedback off verify off timing off
SELECT count(*)
FROM daily_estimates
WHERE TO_CHAR(date_appended,'YYYYMMDD') = '$yyyymmdd';
exit
EOF`


I get nothing. If I hard code an actual date in there, it works
fine. Why is Oracle not resolving the variable? I've seen this on
many pages and it works fine there.......

Oracle 10g R2. Using ksh......

Many thanks!

On Jul 16, 2:03*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
Something bizarro with the eof quoting. Try removing the double
quotes and moving the ending EOF to the beginning of the line.
There's some other trick to do what you want but I can't quite
remember what it is, something to do with the backquotes making things
different, I think. I don't do it enough to remember all the nesting
rules.

jg
--
@home.com is bogus.
http://www.sun.com/aboutsun/pr/2009-...20090716.1.xml

On Jul 16, 2:03*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
Oddly, this also works:

yyymmdd=`date`
user=xxx
password=yyy
echo $user
echo $password
echo $yyymmdd
estimates=`sqlplus -s $user/${password} <<EOF
set heading off feedback off verify off timing off
SELECT count(*),'blorf','$yyymmdd'
FROM dual;
exit
EOF
`
echo $estimates

The issue is that (per Unix Power Tools) white space and newlines both
become argument separators when in backquotes. That's why you can put
a command that outputs things on separate lines in backquotes and they
are treated as though they are parameters on the same line. That's
not the trick I thought I was about to remember. That's not how I
always thought putting quotes around a hereis delimiter worked - when
I first started this second post, I had thought a backslash was
necessary on the line before the EOF to force the newline not to be
like whitespace. Learn something new every day.

jg
--
@home.com is bogus.
http://blog.mozilla.com/security/200...in-firefox-35/

On Jul 16, 5:35*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Hmmm...I'm playing with this:

estimates=`sqlplus -s $user/${password} <<EOF
set heading off feedback off verify off timing off
SELECT count(*)
FROM daily_estimates
WHERE TO_CHAR(date_appended,'YYYYMMDD') = '$yyyymmdd';
exit
EOF`

I get this: here document `EOF' unclosed

On Jul 16, 5:35*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Forget it.....I got it. Thanks!!!!

The Magnet <art (AT) unsu (DOT) com> writes:

I suspect its a quoting issue. Oracle doesn't know anything about $
variables - these are interpreted by the shell, not by Oracle.

At a guess, I'd suspect your here document is incorrectly quoted - what
is actually being passed to oracle is the string $yyyymmdd rather than
the value of that variable. Its been a long time since I used ksh, so
I'm not up to speed with the quoting and nested quoting expansion etc.

Also, I'd be tempted to change your SQL. instead of a
to_char(date.... construct on the LHS, I'd be tempted to do
date_column = to_date('date_str', 'fmt_str'); This will enable Oracle to
use any indexes that may be on the date column.

HTH

Tim


--
tcross (at) rapttech dot com dot au