Visit Our UNIX and Linux User Community


Shell Script And SQLPLUS


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Shell Script And SQLPLUS
# 1  
Old 08-24-2007
Shell Script And SQLPLUS

i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help Smilie

I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is perfectly working. But now i have created some real obviously mistakes like "trunate" or "comit" - but I still get the 0 as returncode. Does anybody have an idea what i'm doing wrong???

Thanks for your help, Martin

###### dbUpdate.sh ######

sqlplus -s userrob@db1/passwrob< proc_clips.sql
RETVAL=$?
echo $RETVAL
echo "SQLPUS-CLIPS"

###### proc_clips.sql ######

set echo on
whenever sqlerror exit 1
whenever oserror exit 2
exec martin_s4m_fictions;
exec MAJ_RANK;
trunate table T_DRAUT_TEMP_CLIPSFICTIONS;
comit;
exit 0

###### OUTPUT ######

SP2-0734: unknown command beginning "trunate ta..." - rest of line ignored.
SP2-0042: unknown command "comit" - rest of line ignored.
0
SQLPUS-CLIPS
# 2  
Old 08-25-2007
What do your sqlplus scripts look like? Are you using the WHENEVER SQLERROR directive so that a code is sent to the O/S?

WHENEVER SQLERROR EXIT SQL.SQLCODE

Cheers,

Keith
# 3  
Old 08-25-2007
Thank for the idea, but I already use

"whenever sqlerror exit 1" and
"whenever oserror exit 2"

so this can't eb the problem. Looks like sqlplus ignors my commands...

I have posted a part of my shell script, the sql-script i execute and the produced output on the bottom of my first script.
# 4  
Old 08-25-2007
Is this a typo ( trunate ) ? Shouldn't this be truncate ?

Code:
trunate table T_DRAUT_TEMP_CLIPSFICTIONS;

# 5  
Old 08-25-2007
Quote:
Originally Posted by maco_home
i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help Smilie

I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is perfectly working. But now i have created some real obviously mistakes like "trunate" or "comit" - but I still get the 0 as returncode. Does anybody have an idea what i'm doing wrong???

Thanks for your help, Martin

###### dbUpdate.sh ######

sqlplus -s userrob@db1/passwrob< proc_clips.sql
RETVAL=$?
echo $RETVAL
echo "SQLPUS-CLIPS"

###### proc_clips.sql ######

set echo on
whenever sqlerror exit 1
whenever oserror exit 2
exec martin_s4m_fictions;
exec MAJ_RANK;
trunate table T_DRAUT_TEMP_CLIPSFICTIONS;
comit;
exit 0

###### OUTPUT ######

SP2-0734: unknown command beginning "trunate ta..." - rest of line ignored.
SP2-0042: unknown command "comit" - rest of line ignored.
0
SQLPUS-CLIPS
It's because those are SQL*Plus errors and not SQL ,PL/SQL blocks (SQLERROR) or OS (OSERROR) errors.

Last edited by radoulov; 08-25-2007 at 01:08 PM.. Reason: corrected(problem description not clear): commands->errors
# 6  
Old 08-25-2007
Quote:
Originally Posted by radoulov
It's because those are errors with SQL*Plus commands and not SQL commands,PL/SQL blocks (SQLERROR) or OS (OSERROR) errors.
For this reason, I suggest you exiting with a good return status different from 0, and test this value for successful completion of the sql script.
Every exit status is good, only dont' use 0 Smilie

Here at work we have a whole bunch of sql programs made in this way for avoiding this problem.
# 7  
Old 08-25-2007
Quote:
Originally Posted by robotronic
For this reason, I suggest you exiting with a good return status different from 0, and test this value for successful completion of the sql script.
Every exit status is good, only dont' use 0 Smilie

Here at work we have a whole bunch of sql programs made in this way for avoiding this problem.
He's using whenever sql/os error, so if
there is a SQL, PL/SQL or OS error, it's handled before the final exit:

Code:
$ sqlplus -s '/ as sysdba'<<<$(printf "whenever sqlerror exit 42\nselect 'hello' from x;\nexit 0")||echo "exit status $?"
select 'hello' from x
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


exit status 42

It's about the syntax errors that are not handled by the whenever directive.
 

Previous Thread | Next Thread
Test Your Knowledge in Computers #368
Difficulty: Medium
The Open Group released the Single UNIX Specification Version 2 in 1987.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sqlplus in shell script

Hi All, Please let me know what i am missing in the following code (part of my script) Schemas=(AWQM WFCONTROLLER PROVCO PRISM) for s in "${Schemas}" do sch="${s}_$tol" if || ;then echo "This is AD or TD region" sqlplus -s $sch/$tpwd@$ttns <<EOF... (7 Replies)
Discussion started by: pvmanikandan
7 Replies

2. Shell Programming and Scripting

sqlplus in shell script

Hi When I use sqlplus in shell script, I get sqlplus: command not found. ORACLE_HOME is not set. How to set ORACLE_HOME in unix? Thanks (3 Replies)
Discussion started by: vinoth_kumar
3 Replies

3. Shell Programming and Scripting

SQLplus and Shell script problem

sql_rows=`sqlplus -s / <<EOF set heading off set pagesize 1000 set tab off set linesize 120 wrap off column "Path" format a15 --column "No_Of_files" format a10 select tablespace_name, substr(file_name,1,instr(file_name,'/',1,2)) as "Path" , count(*) as "No_Of_files" from dba_data_files ... (7 Replies)
Discussion started by: desibabu
7 Replies

4. Shell Programming and Scripting

Want to learn/use SQLPLUS in shell script

Hi All, How i will use sqlplus in shell script? Can any one provide sample code which explain following: 1. Connect to oracle DB 2. Exceute select * from tablename 3. Release connection to the DB 4. Append output in file everytime when query executes. Thanks in advance (1 Reply)
Discussion started by: poweroflinux
1 Replies

5. Shell Programming and Scripting

SQLPLUS within shell script

Hi I want to connect to the Oracle database using a username/password and get back the query result(a numeric value) in a variable, which I can then compare using a conditional. Can anybody help me with this. Thanks Gaurav (4 Replies)
Discussion started by: gaurav_1711
4 Replies

6. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

7. Shell Programming and Scripting

Call sqlplus in the shell script

Hi, I am writing a script to test database connection. If the first try fails, it will wait for 1 minutes and then try again. The script is as following: ........ for i in $ORACLE_SID do $ORACLE_HOME/bin/sqlplus $username/$password@$i <<! >/dev/null select * from tab; exit if ; then... (3 Replies)
Discussion started by: beaniebear
3 Replies

8. Shell Programming and Scripting

help me in sending parameters from sqlplus script to unix shell script

Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files.. Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this? Please tell me... (2 Replies)
Discussion started by: Hara
2 Replies

9. Shell Programming and Scripting

running shell script from sqlplus

I have a script which connects to different database servers using sqlplus. Is there a way by which I can run a shell command on that host from sqlplus? I know about 'host' command but it runs script on the local machine where the original script is running. Is there a way to run command on the... (9 Replies)
Discussion started by: dkr123
9 Replies

10. Shell Programming and Scripting

passing parameters from a shell script to sqlplus

Hi , I want to pass parameters from a shell script to a sql script and use the parameter in the sql query ..and then I want to spool a particular select query on to my unix box... for 4 different locations by writing only one sql script Right now no file is generated on the unix box...it is a... (2 Replies)
Discussion started by: phani
2 Replies

Featured Tech Videos