Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Return value to shell script, depending on status of pl/sql udpate

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Return value to shell script, depending on status of pl/sql udpate
# 1  
Old 07-05-2013
Return value to shell script, depending on status of pl/sql udpate
Hi All,

I need to return value to the main shell script, depending on whether the UPDATE command in the embedded pl/sql is successfu or not.

Code:
 
 
#!bin/ksh
 
updateStatus=`sqlplus --conn details-- << EOF
 
DECLARE
var_rows NUMBER;
 
BEGIN
update table_name 
set column_name = 'abc'
where condition;
 
var_rows := SQL%ROWCOUNT;
 
if var_rows = 1
then
        commit;
        ------Here i want to return value as 1 or true to unix variable updateStatus---------;
else
        rollback;
        ------Here i want to return value as 2 or false to unix variable updateStatus---------;

end if;

 
END;
/
exit;

EOF`
 
echo "update status : " $updateStatus

The rest of the script works fine, except for the return part
Please help Smilie

Thanks,
Ritu
# 2  
Old 07-05-2013
If you have nothing else in the output of `subshell` (that goes into the variable updateStatus)
then you can simply do
Code:
echo 1

and
Code:
echo 2

If you have output that goes to the variable updateStatus, and you want to additionally return a status value, you can try
Code:
exit 1

and
Code:
exit 2

that should terminate the `subshell` at this point, and is retrievable in the next command as $?
Code:
updateStatus=`...
...
...`
retval=$?
echo $retval

This User Gave Thanks to MadeInGermany For This Post:
rituparna_gupta
# 3  
Old 07-05-2013
Note that on UNIX and Linux systems, utilities conventionally return an exit code of 0 to indicate success; not 1.
# 4  
Old 07-07-2013
Hi MadeInGermany/Don,

Tried the suggested workaround, but getting this error on trying both echo 1 or exit 1 :
Code:
 
 
 ORA-06550: line 21, column 6: PLS-00103: Encountered the symbol "1" when expecting one of the following: := . ( @ % ; The symbol "; was inserted before "1" to continue.

Last edited by rituparna_gupta; 07-07-2013 at 07:20 PM..
# 5  
Old 07-07-2013
the values you are setting are being fed into your sql command ...

minus better alternatives, try ...

sending the the return value you want to an external file and have your script read the value in that file ...
# 6  
Old 07-07-2013
Hi Just Ice,

Even redirecting it to a file doesnt seem to wrk:
1. exit/echo fails with the same error
2. trying to print the output into the files using select statement doesnt work either, as it requires INTO clause for PL/SQL block

:-(
# 7  
Old 07-07-2013
Code:
#! /bin/ksh

PATH=/usr/bin:/bin:/usr/sbin:/sbin

file=/dir/return.file

$pl_sql_code_block_sends_return_value_into_$file 

if [ -s $file ]
then
    updateStatus=$(< $file)
    echo "Update Status: $updateStatus"
else
    echo "$file empty. No update return value."
fi

exit 0
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need output of script on screen and file with correct return status of the called script.

Hi, I am trying to capture logs of the script in the file as well as on the screen. I have used exec and tee command for this. While using exec command I am getting the correct output in the file but, script output is not getting displayed on the screen as it get executed. Below is my sample... (14 Replies)
Discussion started by: Prathmesh
14 Replies

2. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

3. UNIX for Dummies Questions & Answers

opposite return status in c shell

there is something wrong with my system. when I do this: diff file1 file1 && echo 1 the output is 1. but diff file1 file2 >/dev/null && echo 1 output nothing while diff file1 file2 >/dev/null || echo 1 shows 1. the same with "grep" return status. they are both GNU utilities.... (5 Replies)
Discussion started by: phil518
5 Replies

4. Shell Programming and Scripting

Assigning return value of an embedded SQL in a shell script variable

I've a script of the following form calling a simple sql that counts the no of rows as based on some conditions. I want the count returned by the sql to get assigned to the variable sql_ret_val1. However I'm finding that this var is always getting assigned a value of 0. I have verified by executing... (1 Reply)
Discussion started by: MxC
1 Replies

5. Shell Programming and Scripting

calling pl/sql procedure from shell and return values

How could I call an Oracle PL/SQL procedure from any shell (bash) and catch returning value from that procedure (out param) or get a returning value if it's a function. also, I got into trouble when I tried to send a number as a param #!/bin/bash -e username=$1 pwd=$2 baza=$3... (0 Replies)
Discussion started by: bongo
0 Replies

6. Shell Programming and Scripting

Shell script to catch PL/SQL return values

I tried searching the forum for similar posts but its closed now. Would appreciate any help on this. I am trying to capture return value from a select query into a variable. DB is Oracle I am able to spool it to a file but I donot intend to use it. Here is my script that does not work ;) I... (27 Replies)
Discussion started by: monie2717
27 Replies

7. Shell Programming and Scripting

return status after run the shell script

Hello, I wanted to delete all files which are placed 14 days back. Here is my below script. My script works very well and it deletes all files 14 days back. I wanted to display message incase if the delete script is not successful. The below script returns always successful. But the directory... (6 Replies)
Discussion started by: govindts
6 Replies

8. Shell Programming and Scripting

Need to return fail or pass from shell script on the basis of pl/sql code execution

Hi guys, I am quite new in shell scripting. I am tring to promote some oracle jobs into control-M. In control-M, I am calling a script which establishes a connection with database and execute some procedures. Now I want if that PL/sql Block got failed script should return failure to... (2 Replies)
Discussion started by: alok1301
2 Replies

9. Shell Programming and Scripting

Shell script to catch PL/SQL return values

Hello, I need some help from the experts on PL/SQL and Shell scripting. I need a shell script that runs a PL/SQL procedure and gets the values returned from the PL/SQL procedure into the shell variables. The PL/SQL procedure returns multiple values. I was able to assign a single return value... (1 Reply)
Discussion started by: Veera_Raghav
1 Replies

10. Shell Programming and Scripting

return variable from PL/SQL procedure to shell

Hi i'm calling a pl/sql procedure which is returning one variable. i'm trying to assing this value to variable in shell script the code i wrote is ** in shell script** var= 'sqlplus user/pass @ret.sql' echo $var ** and variable dum_var number exec rt_test(:DUM_VAR); exit; in... (4 Replies)
Discussion started by: ap_gore79
4 Replies
Login or Register to Ask a Question