Hello all

I have posted this question before but no one seemed to know what the
problem was or how to solve it, so I'm giving it another try ..

I'm having some problems with an sql statement in a here document.
The following code works fine:
#!/bin/ksh
..
..
result=$(sqlplus -s myuser/mypass [at] myinstance << EOF
select tname from tab
/
EOF)

echo $result | tr " " "\n"


but when I add a "where field like 'XXX' " statement with single
quotes, such as:
#!/bin/ksh
..
..
result=$(sqlplus -s myuser/mypass [at] myinstance << EOF
select tname from tab where tname like 'VOUCHER'
/
EOF)

echo $result | tr " " "\n"

I get the the content of the current directory and an ORACLE error
message, for example:
..
..
sqlnet.log
testing
users
var
ERROR
at
line
1:
ORA-00904:
"VOUCHER":
invalid
identifier

I think the source of the problem is the single quotes surrounding the
tale name
I can't tell why this is happening or how to solve it

PLEASE HELP!!!
This issue is really plaguing me :-(

Thanks

hyperboogie wrote:
> Hello all
>
> I have posted this question before but no one seemed to know what the
> problem was or how to solve it, so I'm giving it another try ..
>
> I'm having some problems with an sql statement in a here document.
> The following code works fine:
> #!/bin/ksh
> .
> .
> result=$(sqlplus -s myuser/mypass [at] myinstance << EOF
> select tname from tab
> /
> EOF)
>
> echo $result | tr " " "\n"
>
>
> but when I add a "where field like 'XXX' " statement with single
> quotes, such as:
> #!/bin/ksh
> .
> .
> result=$(sqlplus -s myuser/mypass [at] myinstance << EOF
> select tname from tab where tname like 'VOUCHER'
> /
> EOF)
>
>
>
> I get the the content of the current directory and an ORACLE error
> message, for example:
> .
> .
> sqlnet.log
> testing
> users
> var
> ERROR
> at
> line
> 1:
> ORA-00904:
> "VOUCHER":
> invalid
> identifier
>
> I think the source of the problem is the single quotes surrounding the
> tale name
> I can't tell why this is happening or how to solve it
>
> PLEASE HELP!!!
> This issue is really plaguing me :-(
>
> Thanks

Not sure, but try quoting EOF. This has the effect of
suppressing shell processing of the here doc body.
While quotes don't seem to get stripped for me using
bash, maybe in ksh it does.

result=$(sqlplus -s myuser/mypass [at] myinstance << \EOF
select tname from tab where tname like 'VOUCHER'
/
EOF)

Also, you should quote $result in the echo statement,
instead of using tr:
echo "$result"
Or even better:
printf '%s\n' "$result"

-Wayne

Have you tried '%VOUCHER%'? Just an idea.

hyperboogie wrote:
[...]
> but when I add a "where field like 'XXX' " statement with single
> quotes, such as:
> #!/bin/ksh
> .
> .
> result=$(sqlplus -s myuser/mypass [at] myinstance << EOF
> select tname from tab where tname like 'VOUCHER'
> /
> EOF)
>
> echo $result | tr " " "\n"
>
> I get the the content of the current directory and an ORACLE error
> message, for example:
> .
> .
> sqlnet.log
> testing
> users
> var
> ERROR
> at
> line
> 1:
> ORA-00904:
> "VOUCHER":
> invalid
> identifier
[...]

Try with:

t="'VOUCHER'"
result="$(sqlplus -s myuser/mypass [at] myinstance << EOF
set pages 0 feed off
select tname from tab where tname like $t
/
EOF)"


Dimitre