sql statement inside a here document
sql statement inside a here document
am 12.11.2007 10:44:11 von hyperboogie
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@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@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
Re: sql statement inside a here document
am 12.11.2007 15:23:04 von Bill Marcum
On 2007-11-12, hyperboogie 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@myinstance << \EOF
echo "$result"
and you might want to check $? or test whether "$result" contains
'ERROR' before changing the spaces to newlines.
Re: sql statement inside a here document
am 13.11.2007 10:03:24 von hyperboogie
On Nov 12, 4:23 pm, Bill Marcum wrote:
>
> result=$(sqlplus -s myuser/mypass@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???
Re: sql statement inside a here document
am 13.11.2007 10:26:34 von Bill Marcum
On 2007-11-13, hyperboogie wrote:
> On Nov 12, 4:23 pm, Bill Marcum wrote:
>
>>
>> result=$(sqlplus -s myuser/mypass@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.
Re: sql statement inside a here document
am 13.11.2007 11:13:21 von hyperboogie
On Nov 13, 11:26 am, Bill Marcum 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 :-)
Re: sql statement inside a here document
am 13.11.2007 11:15:14 von Janis Papanagnou
On 13 Nov., 10:26, Bill Marcum wrote:
> On 2007-11-13, hyperboogie wrote:> On Nov 12, 4:23 pm, Bill Marcum wrote:
>
> >> result=$(sqlplus -s myuser/mypass@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