Unix Shell - Getting the value of a SQL query into a shell variable

Author

Getting the value of a SQL query into a shell variable

Aditya Kumar

2007-12-19, 7:32 am

Hi,

This is my first post at this group.

I need to run an Oracle SQL query (Select Query) and store the value
returned in a Shell variable. Typically, the value returned would be a
single value (Single row, Single column). The code which is giving me
problem (I'll come to the problem) is as:

#!/bin/ksh

out=$(sqlplus -silent **username**/**password**<<-EOF
set head off
set echo off
set feedback off

select filter from project_filter p
where
p.project_id = 'somevalue'
;
EXIT SQL.SQLCODE
/
EOF
)
echo $out

Now, this shows up something like :

p.project_id = "somevalue" <<list of all the files in the present
directory here>> ERROR at line 3: ORA-00904: invalid column name

I am unable to understand what the problem here is. Also, if I change
the sql query to something like this: "select count(*) from tablename"
or something like "select filter from project_filter" ; it works
seamlessly. I have reason to believe that the problem here is
something related to the quotation marks used in the original query
that I want to use.

Can someone help me with this, please? Also, in case this forum is not
the right place to post a question like this, please let me know. I
hope to get some some response(s) soon.

Thanks,
Aditya K

Barry Margolin

2007-12-19, 7:27 pm

In article
<eb1d1f17-7432-47a6-84c4-db04636922cb@q3g2000hsg.googlegroups.com>,
Aditya Kumar <aditya2507@gmail.com> wrote:

> Hi,
>
> This is my first post at this group.
>
> I need to run an Oracle SQL query (Select Query) and store the value
> returned in a Shell variable. Typically, the value returned would be a
> single value (Single row, Single column). The code which is giving me
> problem (I'll come to the problem) is as:
>
> #!/bin/ksh
>
> out=$(sqlplus -silent **username**/**password**<<-EOF
> set head off
> set echo off
> set feedback off
>
> select filter from project_filter p
> where
> p.project_id = 'somevalue'
> ;
> EXIT SQL.SQLCODE
> /
> EOF
> )
> echo $out
>
> Now, this shows up something like :
>
> p.project_id = "somevalue" <<list of all the files in the present
> directory here>> ERROR at line 3: ORA-00904: invalid column name

What happens if you change the last line to:

echo "$out"

If you don't quote a variable, wildcard characters in its value will be
expanded.

>
> I am unable to understand what the problem here is. Also, if I change
> the sql query to something like this: "select count(*) from tablename"
> or something like "select filter from project_filter" ; it works
> seamlessly. I have reason to believe that the problem here is
> something related to the quotation marks used in the original query
> that I want to use.
>
> Can someone help me with this, please? Also, in case this forum is not
> the right place to post a question like this, please let me know. I
> hope to get some some response(s) soon.
>
> Thanks,
> Aditya K

--
Barry Margolin, barmar@alum.mit.edu
Arlington, MA
*** PLEASE post questions in newsgroups, not directly to me ***
*** PLEASE don't copy me on replies, I'll read them in the group ***

Aditya Kumar

2007-12-20, 7:35 am

On Dec 20, 5:29 am, Barry Margolin <bar...@alum.mit.edu> wrote:
> In article
> <eb1d1f17-7432-47a6-84c4-db0463692...@q3g2000hsg.googlegroups.com>,
> Aditya Kumar <aditya2...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
> What happens if you change the last line to:
>
> echo "$out"
>
> If you don't quote a variable, wildcard characters in its value will be
> expanded.

<snip>
Barry, thanks a lot for your response. It brings me a step closer to
success. This is the new output:

p.project_id = "somevalue"
*
ERROR at line 3:
ORA-00904: invalid column name

You will notice that the long list of files present in the current
directory is not appearing anymore. I think the problem was being
caused by the "*" which is thrown by Oracle. Alright, so at least we
have one positive.

The reason it shows up this error is not entirely an Oracle problem.
The query is being interpolated as column_name="value", while the
input is column_name='value'. I am almost sure that this is a special
character interpolation problem. Any pointers?

Thanks,
Aditya K

> Barry Margolin, bar...@alum.mit.edu
> Arlington, MA
> *** PLEASE post questions in newsgroups, not directly to me ***
> *** PLEASE don't copy me on replies, I'll read them in the group ***

Barry Margolin

2007-12-21, 1:48 am

In article
<3dc00a89-62e5-4191-98a7-cfdc12359e4f@p69g2000hsa.googlegroups.com>,
Aditya Kumar <aditya2507@gmail.com> wrote:

> On Dec 20, 5:29 am, Barry Margolin <bar...@alum.mit.edu> wrote:
>
> <snip>
> Barry, thanks a lot for your response. It brings me a step closer to
> success. This is the new output:
>
> p.project_id = "somevalue"
> *
> ERROR at line 3:
> ORA-00904: invalid column name
>
>
> You will notice that the long list of files present in the current
> directory is not appearing anymore. I think the problem was being
> caused by the "*" which is thrown by Oracle. Alright, so at least we
> have one positive.
>
> The reason it shows up this error is not entirely an Oracle problem.
> The query is being interpolated as column_name="value", while the
> input is column_name='value'. I am almost sure that this is a special
> character interpolation problem. Any pointers?

I think this may be a ksh bug. See this 4-year-old thread where someone
complained about the same thing.

http://groups.google.com/group/comp..._frm/thread/290
756fc59e6dbd5/03dc3ee35122f530?rnum=1&q=quoting+in+here+document+substitu
tion+group%3Acomp.unix.shell&_done=%2Fgroup%2Fcomp.unix.shell%2Fbrowse_fr
m%2Fthread%2F290756fc59e6dbd5%2Fbf977861
3b11ee92%3Flnk%3Dst%26q%3Dquoting
%2Bin%2Bhere%2Bdocument%2Bsubstitution%2
Bgroup%253Acomp.unix.shell%26#doc
_03dc3ee35122f530

--
Barry Margolin, barmar@alum.mit.edu
Arlington, MA
*** PLEASE post questions in newsgroups, not directly to me ***
*** PLEASE don't copy me on replies, I'll read them in the group ***