Hello all

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 statement with quotes, such as:
#!/bin/ksh
..
..
result=$(sqlplus -s myuser/mypass [at] myinstance << EOF
select tname from tab where tname like 'MYTABLE'
/
EOF)

echo $result | tr " " "\n"

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


How can I escape the quotes so that they will be used in the context
of the sql statement?

Thanks

On 2007-11-12, hyperboogie <hyperboogie [at] gmail.com> wrote:
> echo $result | tr " " "\n"
>
> I get the the content of my home directory and an ORACLE error
> message:
> .
> .
> sqlnet.log
> testing
> users
> var
> ERROR
> at
> line
> 1:
> ORA-00904:
> "VOUCHER":
> invalid
> identifier
>
>
> How can I escape the quotes so that they will be used in the context
> of the sql statement?
>
result=$(sqlplus -s myuser/mypass [at] myinstance << \EOF
echo "$result"
and you might want to check $? or test whether "$result" contains
'ERROR' before changing the spaces to newlines.

On Nov 12, 4:23 pm, Bill Marcum <marcumb... [at] bellsouth.net> wrote:

>
> result=$(sqlplus -s myuser/mypass [at] myinstance << \EOF
> echo "$result"
> and you might want to check $? or test whether "$result" contains
> 'ERROR' before changing the spaces to newlines.

Sorry, this did not work for me ...
I'm not sure I understood though ...
should I just backslash the first EOF??? cause that's what I did to no
avail .... :-(
am I missing something from your explanation???

On 2007-11-13, hyperboogie <hyperboogie [at] gmail.com> wrote:
> On Nov 12, 4:23 pm, Bill Marcum <marcumb... [at] bellsouth.net> wrote:
>
>>
>> result=$(sqlplus -s myuser/mypass [at] myinstance << \EOF
>> echo "$result"
>> and you might want to check $? or test whether "$result" contains
>> 'ERROR' before changing the spaces to newlines.
>
> Sorry, this did not work for me ...
> I'm not sure I understood though ...
> should I just backslash the first EOF??? cause that's what I did to no
> avail .... :-(
> am I missing something from your explanation???
>
A backslash on the first EOF would prevent expansion of shell variables
or wildcards in the here document. Apparently that was not your
problem.

On Nov 13, 11:26 am, Bill Marcum <marcumb... [at] bellsouth.net> wrote:

> A backslash on the first EOF would prevent expansion of shell variables
> or wildcards in the here document. Apparently that was not your
> problem.

No ... the problem (I think) is with the single quotes in the sql
statement:
select tname from tab where tname like 'MYTABLE'

Thanks anyway ... The information about the backslash is useful non-
the-less :-)

On 13 Nov., 10:26, Bill Marcum <marcumb... [at] bellsouth.net> wrote:
> On 2007-11-13, hyperboogie <hyperboo... [at] gmail.com> wrote:> On Nov 12, 4:23 pm, Bill Marcum <marcumb... [at] bellsouth.net> wrote:
>
> >> result=$(sqlplus -s myuser/mypass [at] myinstance << \EOF
> >> echo "$result"
> >> and you might want to check $? or test whether "$result" contains
> >> 'ERROR' before changing the spaces to newlines.
>
> > Sorry, this did not work for me ...
> > I'm not sure I understood though ...
> > should I just backslash the first EOF??? cause that's what I did to no
> > avail .... :-(
> > am I missing something from your explanation???
>
> A backslash on the first EOF would prevent expansion of shell variables
> or wildcards in the here document. Apparently that was not your
> problem.

Either way, wildcards are not expanded in here documents.

Janis