Issue
I want to get output value from procedure and display it or store in a variable in shell script .
I have tried below code and got output but we can only print numbers using this method and also can print only one output from procedure .
#!/bin/bash
sqlplus -s HOME/testhome#@SIR22457 << EOF
set serveroutput on;
variable out_shell number;
begin sam('$1',:out_shell);
end;
/
exit :out_shell
EOF
echo "Value out_shell: $?"
But i want string as well as how to handle for multiple out parameter.
Here is the sample procedure with one out parameter . i also want the same with multiple out parameters
create or replace procedure sam (var1 in varchar2, var2 out number)
as
begin
dbms_output.put_line('var1 is: '||var1);
var2 := var1;
end;
/
Solution
Would be this one:
create or replace procedure giveMeFive(var1 in varchar2)
as
begin
dbms_output.put_line('Hi '||var1);
end;
/
var="five"
ret=$( sqlplus -s HOME/testhome#@SIR22457 <<END_SQL
SET FEEDBACK OFF ECHO OFF SERVEROUTPUT ON SIZE 100000
BEGIN giveMeFive('$var'); END;
END_SQL
)
echo "$ret"
Hi five
I don't think you can retrieve multiple OUT values. You must put the result into a text string - which you could parse, of course.
For example like this:
create or replace procedure giveMeFive(var1 in varchar2)
as
begin
dbms_output.put_line('var1='||var1||' ; var2="foo"');
end;
/
ret=$( sqlplus -s HOME/testhome#@SIR22457 <<END_SQL
SET FEEDBACK OFF ECHO OFF SERVEROUTPUT ON SIZE 100000
BEGIN giveMeFive('$var'); END;
END_SQL
)
ret
But it would be very error prone.
Answered By - Wernfried Domscheit Answer Checked By - Katrina (WPSolving Volunteer)