storing response from SQL in an unix (csh) environment variable

I'm trying to run a piece of SQL within a csh unix script (has to be csh, unfortunately) and have it set up as this:

set my_table_exists=`sqlplus -s uid/pwd << END\
set pagesize 0 feedback off verify off heading off echo off;\
select count(*) from all_objects where object_name = 'mytable'\
and OWNER = 'myowner';\
exit;\
END`

Open in new window


when I run the script it just returns:

Badly placed ()'s

I'm guessing it doesn't like the count(*) (?) but how can I get round this?

Any help much appreciated!
yelbowAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

woolmilkporcCommented:
Are you sure that you get "Badly placed ()'s" with "count(*)" appearing inside the heredoc?

Normally, inside a heredoc, csh should not care about "()". Instead, "count(*)" should disappear as a whole, because csh tries to interpret it as a wildcarded filename.

A way around this (again, inside a heredoc) is enclosing the whole thing in double quotes:

set my_table_exists="`sqlplus -s uid/pwd << END\
set pagesize 0 feedback off verify off heading off echo off;\
select count(*) from all_objects where object_name = 'mytable'\
and OWNER = 'myowner';\
exit;\
END`"

Outside a heredoc enclosing 'count(*)' in single quotes should be sufficient.
0
yelbowAuthor Commented:
Hi,

Yep - definitely getting it whilst within the heredoc.

Tried it with surrounding the whole thing in double quotes and the error is the same.

If I amend it to be:

set my_table_exists="`sqlplus -s uid/pwd << END\
set pagesize 0 feedback off verify off heading off echo off;\
select owner from all_objects where object_name = 'mytable'\
and OWNER = 'myowner';\
exit;\
END`"

i.e. replace the count(*) with a field name - the error is different I get:

select: Command not found.
END: Command not found.

I'm stumped
0
woolmilkporcCommented:
Please try without the backslashes at the line ends, they're not necessary.
Well, they shouldn't do any harm either, but who knows ...

It seems that only the first line (set pagesize ...) is read by sqlplus, the next line is interpreted by csh.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

woolmilkporcCommented:
I tried to reproduce your issue, but to no avail so far.
OK, my csh is rather old, tomorrow I'll try with a newer version.

My day is over now, sorry!  See you in a few hours!
0
yelbowAuthor Commented:
Thanks for your help, its much appreciated.

Interestingly, I ran the same script on a solaris server and all worked OK - its only when running on a redhat server that I'm having this trouble.

I may have to abandon doing it this way, but it really should work :)
0
woolmilkporcCommented:
OK,

the fact that you're on Redhat (CentOS/Fedora would be the same) explains a lot.
"csh" in these distributions is actually "tcsh", which behaves differently from csh in some aspects.

Anyway, under tcsh I was able to reproduce your issue - and couldn't find a solution!

Taking my limited "tcsh" knowledge into account I must assume that running a heredoc inside backticks is not possible under that shell.

I fear you will have to find another way - perhaps you could create a temporary SQL file like this

echo "SQL statements" > temporary.sql
echo "more SQL" >> temporary.sql
...

and then try

my_table_exists=`sqlplus -s uid/pwd @temporary.sql`
rm temporary.sql

Sorry, that's all I can do for the moment, but perhaps there's a real tcsh expert around here?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yelbowAuthor Commented:
It seems odd behaviour (to my untrained eye), but - yep - have changed it so that it creates temp sql and runs that instead.

Works a treat.

Many thanks for all your efforts :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Shell Scripting

From novice to tech pro — start learning today.