I have a simple procedure which accepts two input parms and returns and output count:

CREATE OR REPLACE Procedure GET1 ( IN_FOLDER IN VARCHAR2,
IN_SUBJECT_AREA IN VARCHAR2,
OUT_CNT IN OUT VARCHAR2
)
AS
BEGIN
select count(*)
into OUT_CNT
from REP_WFLOW_RUN
where subject_area = IN_FOLDER
and workflow_name = IN_SUBJECT_AREA
and end_time is null
order by end_time desc;

END GET1 ;
/

If I execute it in SQL*PLUS, with following code:

SET SERVEROUTPUT ON
DECLARE
v_folder_name varchar2(20) ;
v_wf_name varchar2(20) ;
v_cnt varchar2(20) ;
BEGIN

v_folder_name := '~user1' ;
v_wf_name := 'w_Workflow2';

GET1(v_folder_name, v_wf_name,v_cnt) ;
DBMS_OUTPUT.PUT_LINE('Folder:' || v_folder_name || ' Wf:' || v_wf_name ||' cnts:' || v_cnt);
END;
/


It returns corrrect value

Folder:~user1 Wf:w_Workflow2 cnts:1

Now how can I get this return value from SP into a UNIX shell Script variable??


I tried following shell script:

wf_name="w_Workflow2"
sub_area="~user1"

echo "wf name :" $wf_name
echo "sub_area:" $sub_area
echo "REP User:" $REP_READ_ONLY_USR
echo "REP PASS:" $REP_READ_ONLY_PASS

v_cnt=6

return_cc=`sqlplus << EOINPUT
##sqlplus << EOINPUT
$REP_READ_ONLY_USR/$REP_READ_ONLY_PASS@dadev
set echo off
set feedback off
set serveroutput on
set verify off
whenever sqlerror exit 1
@/etlapps/SrcFiles/SPD/exec_sp.sql $sub_area $wf_name $v_cnt
exit
##EOINPUT
EOINPUT`

echo "out cnt:" $v_cnt
echo "return cc:" $return_cc

I don't get the value in shell script.

I also tried to use a BIND Variable in my anonymous SQL that runs the SP as follows:

SET SERVEROUTPUT ON
DECLARE
v_folder_name varchar2(20) ;
v_wf_name varchar2(20) ;
var v_cnt varchar2(20) := $v_cnt ;
BEGIN

v_folder_name := '~user1' ;
v_wf_name := 'w_Workflow2';

GET1(v_folder_name, v_wf_name,:v_cnt) ;
DBMS_OUTPUT.PUT_LINE('Folder:' || v_folder_name || ' Wf:' || v_wf_name ||' cnts:' || v_cnt);
END;
/

I get error that Bind variable $v_cnt not declared.

What am I doing wrong????

Could you try following and let me know how it goes

====================
#!/bin/sh

wf_name="w_Workflow2"
sub_area="~user1"

echo "wf name :" $wf_name
echo "sub_area:" $sub_area
echo "REP User:" $REP_READ_ONLY_USR
echo "REP PASS:" $REP_READ_ONLY_PASS

return_cc=`sqlplus -s $REP_READ_ONLY_USR/$REP_READ_ONLY_PASS@dadev << EOINPUT
set echo off
set feedback off
set serveroutput on
set verify off
DECLARE
v_folder_name varchar2(20) ;
v_wf_name varchar2(20) ;
rec_cnt number;
BEGIN
v_folder_name := $sub_area ;
v_wf_name := $wf_name;
GET1(v_folder_name, v_wf_name,rec_cnt) ;
DBMS_OUTPUT.PUT_LINE(rec_cnt);
END;
/
EXIT;
EOINPUT`

echo "Record count is : [$return_cc]"
============================

Assuming the stored procedure is already created in the db, create a "sql_script.sql" like this:

SET SERVEROUTPUT ON
DECLARE
   v_folder_name varchar2(20) ;
   v_wf_name varchar2(20) ;
   v_cnt varchar2(20) ;

BEGIN
   v_folder_name := '~user1' ;
   v_wf_name := 'w_Workflow2';
   GET1(v_folder_name, v_wf_name,v_cnt) ;
   DBMS_OUTPUT.PUT_LINE(v_cnt);
END;
/

wf_name="w_Workflow2"
sub_area="~user1"

echo "wf name :" $wf_name
echo "sub_area:" $sub_area
echo "REP User:" $REP_READ_ONLY_USR
echo "REP PASS:" $REP_READ_ONLY_PASS

v_cnt=6

return_cc=`sqlplus -s /nolog <<EOINPUT
connect $REP_READ_ONLY_USR/$REP_READ_ONLY_PASS@dadev
set echo off
set feedback off
set serveroutput on
set verify off
whenever sqlerror exit 1
@sql_script.sql $sub_area $wf_name $v_cnt
exit
EOINPUT`

echo "return cc:" $return_cc