• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5599
  • Last Modified:

output parameter into unix variable

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
ahoor
Asked:
ahoor
1 Solution
 
amitpagarwalCommented:
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
 
amitpagarwalCommented:
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
 
ahoorAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ahoffmannCommented:
# 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
 
amitpagarwalCommented:
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
 
ahoorAuthor Commented:
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
 
amitpagarwalCommented:
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
 
waynezhuCommented:
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
 
ahoorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now