Calling a stored procedure in unix

[Muktha]

Hi all,

I need to a call a stored procedure in my unix script.I am able to call but the parameters are not able to pass as i want.
I passed as below.
exec Procedure_name('$FLAG',parameter2)
But it did not work.
I got the o/p as
Procedure_name("$FLAG",parameter2)

I need to get parameter as below
Procedure_name('FALSE',parameter2)

Means parameter has to be passed in ''.

Please give me a solution.

Regards
Muktha

[Steven Schweda]

> [...] in my unix script [...]

Is that a _shell_ script, or something else?
(What?)

> exec Procedure_name('$FLAG',parameter2)
> But it did not work.

In a shell script, apostrophes are normally
not what to use around $variable. Have you
tried quotation marks?

exec Procedure_name("$FLAG",parameter2)

> Means parameter has to be passed in ''.

What makes you think that apostrophes are
needed here?

[TTr]

> stored procedure

I am thinking it is a database stored procedure. This should be in a sql script which in turn can be in a shell script.

[Muktha]

Hi ,
Thanks for the response.

Its shell script.
When i use quotation marks i got the output as
execute ("FALSE",Parameter2)

That is not the expected output.

I need to pass as below:-
execute ('FALSE',Parameter2)
Means in single inverted comma.

Could you please try in that way?

Regards
Muktha

[TTr]

Yes, you have a shell script that you want to call the stored procedure. But where is the stored procedure?

[Muktha]

The procedure is placed in another path.
The script trigers the procedure.But the parameter is wrong.

[Steven Schweda]

> That is not the expected output.

Output from what? You seem to be hiding too
many facts. Can you supply a simple example
which shows the problem?

[TTr]

> The procedure is placed in another path.

What is this procedure? another script? a binary?

> The script trigers the procedure.But the parameter is wrong.

The parameters should be in the same syntax as the procedure expects them. Do you know what the "procedure" expects and in what syntax?

Steven is right, you are not telling us all the facts and you are not using standard UNIX terminology either.

[Muktha]

Hi ,

Please dont misunderstand.
I will give you a sample pgm.

EXEC_RESULT=$(sqlplus -s / <<-EOF
set feedback off
set serveroutput on
execute $STORED_PROCEDURE_NAME("$INITIAL_LOAD",$PARAM1,$PARAM2,$XBR_NUM);
exit;
EOF)

Above is my unix script.
But when it is not calling the procedure as i expected
I need as below.
execute $STORED_PROCEDURE_NAME('FALSE',456,678,6);

[Muktha]

Hi ,

I had given a part of my unix script in the previous msg.

That command can trigger the procedure
as below.

EXEC_RESULT=$Procedure_name("FALSE",0007587741,0013926360,0);

But i need the parameter FALSE in single inverted comma.

EXEC_RESULT=Procedure_name('FALSE',0007587741,0013926360,0);

Because my procedure is expecting the parameter in that way.

[TTr]

> Above is my unix script.

Is that all of it?

Did you initialize any variables?

STORED_PROCEDURE_NAME=name_of_stored_proc
INITAIL_LOAD=FALSE
PARAM1=456
PARAM2=678
XBR_NUM=6

[Steven Schweda]

> EXEC_RESULT=$Procedure_name("FALSE", [...]

You're inventing those quotation marks,
right? The shell script isn't putting them
in, is it? "set -x" in your script to see
what it really does.

> $STORED_PROCEDURE_NAME("$INITIAL_LOAD", [...]

What about this?:

$STORED_PROCEDURE_NAME("'$INITIAL_LOAD'", [...]

[Steven Schweda]

Note that your shell script does not do
anything like "call a stored procedure".

Your shell script is, effectively, creating a
temporary file (the "here document") which is
used as input to a program, "sqlplus". What
your shell script needs to do is to put the
desired text into that file/document. The
shell will substitute variable values when
you use the "$variable" notation, as in
"$STORED_PROCEDURE_NAME", but all the
punctuation (apostrophes, commas,
parentheses, and so on) is simply more text
which your script must put out.

You can test your script more easily if you
use "cat" instead of "$()". For example:

td176> cat ./sst.sh
#!/bin/sh
var1=abc
var2=def
var3=ghi
var4=jkl
cat <<-EOF
var1 = $var1
var2 = "$var2"
var3 = '$var3'
var4 = "'$var4'"
EOF
td176> ./sst.sh
var1 = abc
var2 = "def"
var3 = 'ghi'
var4 = "'jkl'"
td176>

Which shows, among other things, that I was
thinking about command-line arguments, and
not a "here document" when I said, "You're
inventing those quotation marks, right?"

The key here is that you're simply creating
text, with $variable substitution, and, in
this context, apostrophes are only more text
(just like quotation marks).

[Muktha]

Hi,

Thanks to all for the response.
I got the solution.

Regards
Muktha

[Muktha]

Thanks to all for the response.

[Steven Schweda]

> I got the solution.

Which solution did you get?

Unlikely as it may seem, someone else might
read this thread someday, hoping to find a
similar solution. If you learned something,
it might help if you pass it along.

[Muktha]

Hi Stewen,

Thanks for the responses given by you.

My query was how to pass a parameter to a stored procedure in single inverted comma.

Solution :
IN_LOAD="'"$INITIAL_LOAD"'"
execute $STORED_PROCEDURE_NAME($IN_LOAD,$PARAM1,$PARAM2,$XBR_NUM);

So i passed the parameters as i expected.
execute refresh_iad_db.refresh_meter_reading('FALSE',0013926429,0019960339,0);

Regards
Muktha

[Steven Schweda]

> My query was how to pass a parameter to a
> stored procedure in single inverted comma.

Yes, and it should have been, "How can I get
apostrophes into a shell string variable
value?", but yes,
var5="'"mno"'"
will cause:
var5 = $var5
in a "here document" to become:
var5 = 'mno'
showing, once again, that there is often more
than one solution to a problem.