Passing output parameter(Oracle) to variable in ksh Script

UNIX for Beginners Q & A
1

Hello,

I have researched and tried many way to pass OUT parameter to be stored in variable in KSH Script.Still not success, please help.

Here is my Store Procedure.

create procedure testout3(v_in varchar2,v_out OUT integer)
as
begin
v_out := 1;
end;

Here is my testvout.ksh

#!/bin/ksh
vvarr=$(sqlplus -s username/password<<!EOF

declare 
v_out integer;

begin

execute testout3('test',:v_out);

commit;

END;

EOF)

echo $vvarr

Note: I have added "echo $vvarr" to check that the variable is passed or not.

After I run this command below, there is nothing happened, no error , no show data.

>testvout.ksh
2

I am no expert in things SQL, so i am not sure if this SQL-script does what i think it does or something different:

As i read it your stored procedure sets a certain variable to the value "1". In Korn shell, when you assign a variable with a process' outcome like in:

var=$( command )

then the variable is assigned with the output of the command, not its variables. So, if your stored procedure only changes the variables value but has no output, then the ksh-variable will be empty - as the output is empty.

I hope this helps.

bakunin