Re: Unix to Oracle / Back

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

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