[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

output parameter into unix variable

Posted on 2002-05-06
9
Medium Priority
?
5,369 Views
Last Modified: 2012-08-14
Hi all,

Been a while since I've been here. Howeverm I've got a new question.
I'm trying to call a stored procedure from a unix shell script. So far, no problem. But in the stored procedure there is an outputparameter which I would like to put in a unix variable. This is giving me problems... I've tries a few things but so far, nothing. The sp works fine exept my unix-variable stays empty.

Any experience in this?


Code:

echo $1
isql $DB_CONNECTSTRING  > log.txt <<aho
declare @iets varchar(10)
exec aho_test @p_in = $1 , @p_out = @iets output
go
aho
echo $iets
cat log.txt

executed as: test 6
result:
6 <---- this is 'echo $1'
  <---- this is where the 'echo' of $iets should be.
(return status = 0)

Return parameters:

 @p_out
 ----------
 groter

(1 row affected)

when I try it like this:

echo $1
isql $DB_CONNECTSTRING  > log.txt <<aho
exec aho_test @p_in = $1 , @p_out = $iets output
go
aho
echo $iets
cat log.txt

then excute: test 6
this is the result:
6 <---- this is 'echo $1'
  <---- this is where the 'echo' of $iets should be.

(return status = 0)

Any one??
0
Comment
Question by:ahoor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6991771
all i can think of is :

echo $1
isql $DB_CONNECTSTRING  > log.txt <<aho
exec aho_test @p_in = $1 , @p_out = @iets output
select @p_out
go
aho
cat log.txt

0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6991785
oh i found somethign ...

var=`isql -U... << Sqlbatch
set nocount on
exec procname @var1 output
 :
 :
go
select @var1
go
Sqlbatch
`
Those are back quotes, BTW (the ones underneath the tilde)

0
 
LVL 3

Author Comment

by:ahoor
ID: 6995844
Sorry, no succes so far... what shell should this be executed in? ksh, sh?
Still the variable is not being filled...

This should be possible...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 51

Expert Comment

by:ahoffmann
ID: 6996739
# if your shell is sh syntax:
iets="`cat log.txt`"
echo $iets

# if your shell is csh syntax
set iets = "`cat log.txt`"
echo $iets
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6996787
ahoor.. please take a close look at this example which seems to work for me.

var=`isql -U.. <<Sqlbatch
set nocount on
declare @b char(2)
exec xb @b output
go
select @b
Sqlbatch
`
echo $var

I am running this script in tcsh shell and it works fine for me.

If this does not work, could you please post the shell script that you are trying to execute.

Thanks.
0
 
LVL 3

Author Comment

by:ahoor
ID: 6996882
Alright, this works is I add one statement:

echo $1
var=`isql $DB_CONNECTSTRING <<aho
set nocount on
declare @iets varchar(10)
exec aho_test @p_in = $1 , @p_out = @iets output
go
aho
`
echo $var
echo $var | read a b c d e f g h iets
echo $iets

The output becomes:

6
(return status = 0) Return parameters: @p_out ---------- groter
groter

where 6 = $1 = input in shell
(return status etc = $var = output of sql statement
grotesr = $iets is 9th column of output which is the value of the output parameter.

I will leave this question open til friday for further remarks, maybe there's an easier way which you or someone will think off seeing this new input.
Thanks so far...
0
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 480 total points
ID: 6996896
a slight change to get exactly what you want.

var=`isql -U.. <<Sqlbatch
set nocount on
declare @b char(2)
exec xb @b output
go
select @b
Sqlbatch
`
echo $var | awk '{print $2}'
0
 
LVL 7

Expert Comment

by:waynezhu
ID: 6997450
While ago, I wrote a similar script for Oracle  which did same thing to assign the SQL output to a shell variable. It appears you already have the answer. What I am going to do is to modify your script to make it little bit compact:

echo $1
iets=`isql $DB_CONNECTSTRING <<aho | awk '{print $9}'
set nocount on
declare @iets varchar(10)
exec aho_test @p_in = $1 , @p_out = @iets output
go
aho
`
echo $iets

In this way, the script will work for sh, ksh, bash, zsh.

I did not use the read command, because it does not work
for sh, bash, and even Linux's ksh (if RedHad 7.1 and up
you can use zsh instead)


For reference, I also pasted my script below:

#
TFBN=`
sqlplus -s $user/$passwd <<!  | head -1
set pages 0
select
decode(substr(p.value,1,1),'?','\$ORACLE_HOME'||substr(p.value,2),p.value)||'/ora_'||i.instance_name||'_'||pr.spid
from v\\$parameter p, v\\$instance i, v\\$process pr
where p.name='user_dump_dest'
and pr.addr=(select paddr from v\\$session where audsid = userenv('SESSIONID'));
!
`
echo $TFBN
#


Good luck.

0
 
LVL 3

Author Comment

by:ahoor
ID: 7005586
Right, I will accept Amitpagarwal's solution because this is the only one that also works for multiple output parameters which I may need in the near future.

Thanks all who participated.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Working from home is a dream for many people who aren’t happy about getting up early, going to the office, and spending long hours at work. There are lots of benefits of remote work for employees.
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

650 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