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????