Passing the value of Out parm in SP to UNIX Shell Script


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing the value of Out parm in SP to UNIX Shell Script
# 1  
Network Passing the value of Out parm in SP to UNIX Shell Script

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????
# 2  
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]"
============================
# 3  
Assuming the stored procedure is already created in the db, create a "sql_script.sql" like this:
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(v_cnt);
END;
/

...and this is the shell code:
Code:
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

The resulting "$return_cc" value is actually the new v_cnt value. It's useless to print out other things with DBMS_OUTPUT: you already know the values of sub_area and wf_name!
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #167
Difficulty: Easy
The Transmission Control Protocol (TCP) is one of the least used protocols of the Internet protocol suite.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX and Linux Applications

Passing variables from UNIX to Ansible to UNIX shell

I m passing a variable stringg from Unix shell which has value 'Good Day' to ansible and from ansible to a second shell script where it print only Good instead of 'Good Day' passing the variable stringg from unix shell script1.sh echo $stringg ansible-playbook install.yml -i... (1 Reply)
Discussion started by: mohtashims
1 Replies

2. Shell Programming and Scripting

UNIX: passing stuff to a shell function

I have users that print files to selected printers. Instead of creating one function for each printer I would like to have just one and passing the files to print as well as the wanted printer. The following code does not work, of course. I'm expecting that $1 is the list of files to be printed... (6 Replies)
Discussion started by: emare
6 Replies

3. UNIX for Dummies Questions & Answers

Passing shell script parameter value to awk command in side the script

I have a shell script (.sh) and I want to pass a parameter value to the awk command but I am getting exception, please assist. diff=$1$2.diff id=$2 new=new_$diff echo "My id is $1" echo "I want to sync for user account $id" ##awk command I am using is as below cat $diff |... (1 Reply)
Discussion started by: Sarita Behera
1 Replies

4. Shell Programming and Scripting

To run a local shell script in a remote machine by passing arguments to the local shell script

I need to run a local shell script on a remote machine. I am able to achieve that by executing the command > ssh -qtt user@host < test.sh However, when I try to pass arguments to test.sh it fails. Any pointers would be appreciated. (7 Replies)
Discussion started by: Sree10
7 Replies

5. Shell Programming and Scripting

Passing a value to stored procedure from unix shell script

Hi Dudes :) I want a unix shell script to pass value to SQL stored procedure. Below is the procedure declare res varchar2(10); begin odm_load_check('PRE_SANITY',res); dbms_output.put_line(res); end; select * from error_log; truncate table error_log; select * from test; (1 Reply)
Discussion started by: shirdi
1 Replies

6. UNIX for Dummies Questions & Answers

Help Passing An Oracle parameter to a unix shell.

I have an Oracle concurrent program that I'm passing a parameter to a unix shell script. An example value of the Oracle parameter is PO_TOP. The Oracle parameter represents the unix env var PO_TOP, meaning, on the unix side there is env var called PO_TOP (ex value: /oradev/apps/po/11.0.3/). My... (7 Replies)
Discussion started by: Mark_Wright
7 Replies

7. Shell Programming and Scripting

SSH - Passing Unix login passwords through shell scripts

Hi All , I need to call a script runscript_B.sh on server A, the runscript_B.sh script locating in server B. The runscript_B.sh in calls another script runscript_A on server A itself. it seend, i need to be connect from Server A to Server B using ssh. I have tryed like this in... (3 Replies)
Discussion started by: koti_rama
3 Replies

8. Shell Programming and Scripting

pass runtime parm to at -f shell script

Hi Folks... I am using a ksh script to submit the at command to run a shell script for immediate execution. The shell script requries 1 parameter. Command in the script is at -m -f $EXE_DIR/process_server.sh $START_TIME $DB_NAME where START_TIME=now and DB_NAME= tnsname of Oracle... (1 Reply)
Discussion started by: island360
1 Replies

9. Shell Programming and Scripting

cron job problem, passing a parm

Hi, I have an existing cron job like the following. 15 5 * * * /appl/scripts/MyScript.sh 2>/dev/null >/dev/null and this works just fine. Now I had to modify the script and now it requires an input parm as 2. I had modified the cron entry so that the script can run now with the... (12 Replies)
Discussion started by: bheemsen
12 Replies

10. Shell Programming and Scripting

Script to submit a job with date parm in maestro

I'm a newbie in scripting attempting to create a script where i can submit a job (in maestro/tivoli) with parameters,in maestro CLI i can do that no problem,i'm thinking about creating a script that will accept input while the script is executing, more like below structure: exec the script -... (1 Reply)
Discussion started by: Kirojin
1 Replies

Featured Tech Videos