Oracle Error Handling In Unix
Contents |
script [message #97631] Wed, 25 September 2002 06:00 Tara Messages: 4Registered: July 1999 Junior Member I have a shell script which calls a packaged procedure. In my shell script, how do I trap an error which has happened in the pl/sql program, so that I can sqlplus error handling in shell script take some actions in the shell script. At the moment, I have an exception handler in sqlplus error handling in unix my pl/sql program which rolls back the changes. Thanks in advance, T. Report message to a moderator Re: how to trap whenever sqlerror pl/sql error in shell script [message #97632 is a reply to message #97631] Wed, 25 September 2002 21:13 K.K. Raj kumar Messages: 33Registered: July 2002 Member Hello, You can use set serveroutput on size 50000 before calling the procedure or package sqlplus error codes and inside the procedure or package you can use dbms_output.put_line to trap the variables. Before calling the package you can spool the output so that the messages will come out. Report message to a moderator Re: how to trap pl/sql error in shell script [message #97634 is a reply to message #97631] Sun, 29 September 2002 07:54 Deborah Messages: 18Registered: June 2002 Junior Member It did not work out for me. But I use the following to catch the
Capture Sql Error In Shell Script
details of the error by redirecting into some file. sqlplus -s usename/password@connect << EOF >> Logfile.txt execute pl-sql_procedure_name exit EOF The above is in shell script and I get the message (success/ failure) in the Logfile.txt Debby Report message to a moderator Re: how to trap pl/sql error in shell script [message #97638 is a reply to message #97631] Mon, 30 September 2002 16:38 andrew again Messages: 2577Registered: March 2000 Senior Member See the solution at the following URL: http://www.oracle.com/oramag/oracle/01-may/index.html?o31asktom.html Report message to a moderator Re: how to trap pl/sql error in shell script [message #97640 is a reply to message #97638] Wed, 02 October 2002 14:18 andrew again Messages: 2577Registered: March 2000 Senior Member Note that some things don't generate errors - like connecting to Oracle. If user/pass is wrong (or maybe if Oracle is unavailable) then you don't get a pl/sql error. A good way of handling that is to spool to a dummy file as the last command in the script. When you return to the OS and your dummy file is there (you obviouly clean it up after your run or before the next run) then you know that you got to the bottom of the file. Report message to a moderator Re: how to trap pl/sql error in shell script [message #97794 is a reply to message #97638] Tue, 28 January 2003 02:23 C.Muthukumar Messages: 1Registered: January 2003 Junio
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us
Sqlplus Exception Handling
Learn more about Stack Overflow the company Business Learn more about hiring developers sqlplus exit code or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack whenever sqlerror exit 1 shell script Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Managing error handling while running sqlplus from http://www.orafaq.com/forum/t/37022/ shell scripts up vote 9 down vote favorite 4 #!/bin/sh echo "Please enter evaluate database username" read eval_user echo "Please enter evaluate database password" read eval_pass echo "Please enter the database name" read db_name LOGFILE=shell_log.txt $ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF>> ${LOGFILE} connect $eval_user/$eval_pass@$db_name WHENEVER OSERROR EXIT 9; WHENEVER SQLERROR EXIT SQL.SQLCODE; DBMS_OUTPUT.put_line('Connected to db'); EOF if [ $? != 0 ] then echo "The upgrade script failed. Please http://stackoverflow.com/questions/14734131/managing-error-handling-while-running-sqlplus-from-shell-scripts refer to the log results.txt for more information" echo "Error code $?" exit 0; fi I am entering garbage values trying to force this script to fail. But, annoyingly, it keeps moving ahead without any mention of any error code. What else needs to be done here? oracle shell sqlplus aix share|improve this question asked Feb 6 '13 at 16:35 roymustang86 1,941113573 Which OS user account are you running under? What database does the script login to? –APC Feb 11 '13 at 14:01 I tried your script, and the log file is capturing the error code. Here is the grab from the log file: ERROR: ORA-01017: invalid username/password; logon denied SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored. –Incognito Feb 12 '13 at 9:55 May I know, what're you getting in log file when you execute the script? –Incognito Feb 12 '13 at 11:18 add a comment| 5 Answers 5 active oldest votes up vote 7 down vote accepted +50 What Max says is correct. Try this modified script #!/bin/sh echo "Please enter evaluate database username" read eval_user echo "Please enter evaluate database password" read eval_pass echo "Please enter the database
Last updated: January 03, 2005 - 11:19 pm UTC Category: Database � Version: Oracle 8i Whilst you are here, check out some content from the AskTom team: On Ignoring, Locking, and Parsing Latest Followup You Asked Hi Tom, In order to pass an https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:444389210579 Oracle error to Unix, I use the "whenerror..." statement. This works fine for some kind of errors (like division by zero, invalid SP name, ...), BUT NOT FOR ALL. In particular, when the error ORA-01652 (segments too small) http://datawarehouse.ittoolbox.com/groups/technical-functional/informatica-l/sqlplus-ora-error-in-shell-unix-2745143 is raised in Oracle, it is not passed to Unix. Is there a way to raise this error at the Unix level ? Thanks, Thierry. and we said... It is working however the status returned to Unix is limited error handling to a single 1 number (in the range of 0..255). If you run a shell script like this: #!/bin/csh -v sqlplus -s / <
Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge Management Linux Networking Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage UNIX Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries VoIP_News Inside-ERP MacProTX Inside-CRM I_am_the_dragon maxwellarnold Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker Ramnath.Awate JoeTorre Craig Borysowich Locutus Dennis Stevenson DukeGanote Richard iudithm mircea_luca Clinton Jones bracke Nikki Klein AbhaiTripathi Iqbalyk Adrian_Grigoriu bluesguyAZ59 blrvenkat COMPANIES EdgeWave Sophos Pivotal CRM Wave Direct View All Topics View All Members View All Companies Toolbox for IT Topics Data Warehouse Groups Ask a New Question Informatica The Informatica group is your premier resource for objective technical discussion and peer-to-peer support on Informatica data integration software. Home | Invite Peers | More Data Warehouse Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. Sqlplus ORA error in shell Unix wilsonc asked May 7, 2009 | Replies (2) Hi all, Is it possible to get the Oracle Error Code on Unix? For example i have the following script (sql_get_clients.sql) --------------sql_get_clients.sql----------------------- WHENEVER SQLERROR EXIT 1; SPOOL spool_clients.txt; SELECT * FROM CLIENTS SPOOL OFF; DISCONECT; EXIT; Join this group Popular White Paper On This Topic Big Data and the CMO: An Introduction to the Challenge and the Opportunity 2Replies Best Answer 0 Mark this reply as the best answer?(Choose carefully, this can't be changed) Yes | No Saving... Brian replied May 7, 2009 It can pipe out your command to a log file and it should be able to show you the ORA- error. Brian Top Best Answer 0 Mark this reply as the best an