Solved

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

Posted on 2014-11-04
7
284 Views
Last Modified: 2014-11-10
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!
0
Comment
Question by:yelbow
  • 4
  • 3
7 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40423046
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
 

Author Comment

by:yelbow
ID: 40423070
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
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40423076
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40423083
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
 

Author Comment

by:yelbow
ID: 40423322
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
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 40423860
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
 

Author Closing Comment

by:yelbow
ID: 40433820
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now