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)

Saturday, January 27, 2024

[SOLVED] How to get out parameter value from oracle procedure in shell script

Issue

Solution