Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Can't get rc of sqlpus script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Can't get rc of sqlpus script
# 1  
Old 01-16-2018
Can't get rc of sqlpus script
I have 2 shell scripts. One with data connection like that (script1.sh):
Code:
#!/usr/bin/ksh 
query="$1"   
whenever sqlerror exit 3   
connect $user/$pass@sid   
${query} 
EOF 
echo $? 
if [ 0 -ne "$?" ]; 
       then     
        exit 1 
fi

And other is a shell script bigger where I execute sql commands like these:
Code:
#!/usr/bin/ksh 
set -x 
$PATH/script1.sh " 
--set serveroutput on 
--set feedback off 
insert into table (column) values ('$1'); 
commit; " 
if [[ $? != 0 ]]   
       then 
             echo "Error" 
             exit 3   
else 
             echo "Ok" 
fi 
............ 
.............

.
The problem is that these second script won't detect error in sql commands and always continues with all code. I put traces and I check that rc is always 0. Could you help me to can detect errors if the sql failed? Thanks!
# 2  
Old 01-16-2018
I'm not sure what are you testing ?

Was the insert successfull ?
Execution of the sqlplus ?

sqlplus will return 0 (success) if a insert, for instance, fails due to a constraint.

Check if this example clear things out, you will need to put extra code in sql.
Code:
variable exitcode number;
begin
select 23 into :exitcode from dual;
end;
/
exit :exitcode

The $? will be populated with 23 in the shell program running the sqlplus program.

Regards
Peasant.
# 3  
Old 01-16-2018
Quote:
Originally Posted by Peasant
I'm not sure what are you testing ?

Was the insert successfull ?
Execution of the sqlplus ?

sqlplus will return 0 (success) if a insert, for instance, fails due to a constraint.

Check if this example clear things out, you will need to put extra code in sql.
Code:
variable exitcode number;
begin
select 23 into :exitcode from dual;
end;
/
exit :exitcode

The $? will be populated with 23 in the shell program running the sqlplus program.

Regards
Peasant.
The code of the sql sentence says me:

Code:
ERROR at line 1:
ORA-00001: unique constraint (TABLE.PROC_PK) violated

If I put a sql like
Code:
select * from not_table_existent;

Also gives me status 0
# 4  
Old 01-16-2018
The echo $? loses the sql's exit code and makes the script leave with 0.


Quote:
Originally Posted by mierdatuti
. . .
EOF
echo $?
if [ 0 -ne "$?" ];
then
exit 1
fi
. . .
# 5  
Old 01-16-2018
If you want the exit code displayed and then used in a decision, you might do something like this:-
Code:
...
${query}
EOF
RC=$?
echo $RC
if [ $RC -ne 0 ]
then
   exit 1
fi


Does that help?



Robin
Last edited by rbatte1; 01-16-2018 at 10:34 AM.. Reason: Schoolboy error of using ICODE tags instead of CODE tags. Corrected now.
Login or Register to Ask a Question

Previous Thread | Next Thread

5 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to block first bash script until second bash script script launches web server/site?

I'm new to utilities like socat and netcat and I'm not clear if they will do what I need. I have a "compileDeployStartWebServer.sh" script and a "StartBrowser.sh" script that are started by emacs/elisp at the same time in two different processes. I'm using Cygwin bash on Windows 10. My... (3 Replies)
Discussion started by: siegfried
3 Replies

2. Shell Programming and Scripting

Shell script works fine as a standalone script but not as part of a bigger script

Hello all, I am facing a weird issue while executing a code below - #!/bin/bash cd /wload/baot/home/baotasa0/sandboxes_finance/ext_ukba_bde/pset sh UKBA_publish.sh UKBA 28082015 3 if then echo "Param file conversion for all the areas are completed, please check in your home directory"... (2 Replies)
Discussion started by: ektubbe
2 Replies

3. UNIX for Dummies Questions & Answers

Calling a script from master script to get value from called script

I am trying to call a script(callingscript.sh) from a master script(masterscript.sh) to get string type value from calling script to master script. I have used scripts mentioned below. #masterscript.sh ./callingscript.sh echo $fileExist #callingscript.sh echo "The script is called"... (2 Replies)
Discussion started by: Raj Roy
2 Replies

4. Shell Programming and Scripting

Script will keep checking running status of another script and also restart called script at night

I am using blow script :-- #!/bin/bash FIND=$(ps -elf | grep "snmp_trap.sh" | grep -v grep) #check snmp_trap.sh is running or not if then # echo "process found" exit 0; else echo "process not found" exec /home/Ketan_r /snmp_trap.sh 2>&1 & disown -h ... (1 Reply)
Discussion started by: ketanraut
1 Replies

5. Shell Programming and Scripting

create a shell script that calls another script and and an awk script

Hi guys I have a shell script that executes sql statemets and sends the output to a file.the script takes in parameters executes sql and sends the result to an output file. #!/bin/sh echo " $2 $3 $4 $5 $6 $7 isql -w400 -U$2 -S$5 -P$3 << xxx use $4 go print"**Changes to the table... (0 Replies)
Discussion started by: magikminox
0 Replies
Login or Register to Ask a Question