Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Need to Capture Sql Failures
Top Forums Shell Programming and Scripting Need to Capture Sql Failures Post 302576547 by felipe.vinturin on Friday 25th of November 2011 07:48:38 AM
Old 11-25-2011
I generally work with both solutions:
-----> Use: "WHENEVER SQLERROR EXIT 1" (as commented by Carlo)
-----> Redirect SQLPLus' output to a file and validate the error.
-----> Also use: "sqlplus -S -L" to try to login just once.

Code:
sqlOutputFile="./sqlplusExec.log"
# sqlplus -S -L PASS/USERID@DB << EOF > "${sqlOutputFile}"
     WHENEVER SQLERROR EXIT 1
     SELECT * from notable;
EOF
sqlplusRetCode=${?}

countErrors=`egrep -c 'ORA-[0-9]{4}|SP2-|TNS-|Usage' "${sqlOutputFile}"`

if [ ${sqlplusRetCode} -ne 0 -o ${countErrors} -ne 0 ]
then
     echo "Found errors while executing SQLPlus[${sqlplusRetCode}][${countErrors}]: "
     egrep 'ORA-[0-9]{4}|SP2-|TNS-|Usage' "${sqlOutputFile}"
     exit 1
fi

I hope it helps!
Last edited by felipe.vinturin; 11-25-2011 at 08:55 AM..
These 2 Users Gave Thanks to felipe.vinturin For This Post:
CarloM mac4rfree
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Running file sql from shell without capture the password

Dear All I have file a.sql, let's say the content is: _________________________________ select * from dual; exit; _________________________________ and I have shell script a.sh, the content is: _________________________________ ORACLE_SID=testing; export ORACLE_SID... (0 Replies)
Discussion started by: Aditya Purwanto
0 Replies

2. HP-UX

Communication Failures

HI ALL, I have been trying to install a particular software using remote linux server. some thing like this: rsh <host ID> /usr/sbin/swinstall -x autoreboot=true -s /tmp/<software> <Product name>. The problem is whenever I try to install the product through a shell script the installation... (1 Reply)
Discussion started by: barun agarwal
1 Replies

3. Shell Programming and Scripting

How to capture value in shell variable from oracle sql?

Hi Friends, Do someone know how to capture value in a shell variable from oracle sql? Requirement : In a table we want to count the number of records and want to pass this value to a shell variable where it can be manipulated later. In ksh shell we open oracle connection from sqlplus. For... (1 Reply)
Discussion started by: sourabhsharma
1 Replies

4. UNIX for Dummies Questions & Answers

how to capture no. of rows updated in update sql in unix db2

hi, i am a new user in unix..and we have unix db2. i want to capture the no. of rows updated by a update db2 sql statement and redirect into a log file. I've seen db2 -m...but not sure how the syntax should be. The update sql that I'm going to run is from a file... Can you please share... (1 Reply)
Discussion started by: j_rymbei
1 Replies

5. Shell Programming and Scripting

Capture linking failures

Hi all, I have a script file that has numerous linking commands (ln -s) and currently there is no checking to see if the linking is successful or not and I need to implement something that checks if any of the linking failed and report a failure. The method I can think of is a small function... (3 Replies)
Discussion started by: zmfcat1
3 Replies

6. UNIX for Dummies Questions & Answers

Capture Error In SQL Plus

Hi I am trying to fetch the data from Oracle Table More my_query.sql | sqlplus -s 'scott/tiger@OrcaleSID ' | sed 's///g;s///g' > sample_file Now if the table passed in the my_query.sql is wrong or there is some other error insied SQL how i can caputure that i tried More... (1 Reply)
Discussion started by: max_hammer
1 Replies

7. Shell Programming and Scripting

Need to capture error of sybase sql in unix

Hi Gurus, I am very new in Unix, I have 1 script, in which I am truncating the table , then BCP the data in Sybase table, and then loading the data from sybase table to sybase table. every thing is working fine, but the problem is with Error. I made some hanges in my insert statement so... (1 Reply)
Discussion started by: aksar
1 Replies

8. Shell Programming and Scripting

Capture rows for a column in file from delete sql -Oracle

Hi, This may not be the right forum but i am hoping someone knows an answer to this. I have to capture rows for a column that was deleted. How can i do that without having to write a select query? delete from myschema.mytable where currentdatetimestamp > columnDate this should delete 5... (4 Replies)
Discussion started by: jakSun8
4 Replies

9. Solaris

11.0 to 11.2 update failures

Attempting to update an 11.0 server with many non-global zones installed. pkg publisher is pkg.oracle.com/solaris/support. FMRI = pkg://solaris/entire@0.5.11,5.11-0.175.1.15.0.4.0:20131230T203500Z When we run pkg update --accept the server contacts oracle, checks packages, finds about 700... (4 Replies)
Discussion started by: CptCarrot
4 Replies

10. Shell Programming and Scripting

UNIX Sqlplus - Capture the sql statement about to run and execution status

Greetings Experts, I am on AIX using ksh. Created a unix script which generates the CREATE OR REPLACE VIEW ... and GRANT .. statements, which are placed in a single .txt file. Now I need to execute the contents in the file (there are around 300 view creation and grant statements) in Oracle and... (4 Replies)
Discussion started by: chill3chee
4 Replies

LEARN ABOUT SUNOS

capture-schema

capture-schema(1m)					    Application Server Utility						capture-schema(1m)

NAME

       capture-schema -  stores the database metadata (schema) in a file for use in mapping and execution

SYNOPSIS

       capture-schema-dburl url-username name- password password-driver a_jdbc_driver [-schemaname name] [-table tablename]* [-out filename]

       Stores  the  database metadata (schema) in a file. You can also use the Sun Java System Studio IDE to capture the database schema. Run cap-
       ture-schema as the same database user that owns the tables, so that the -username and -schemaname options  are  given  the  same  username.
       Additionally, you can grant the database user running the capture-schema command the ANALYZE ANY TABLE privilege.

OPTIONS

       -dburl		       JDBC URL expected by the driver for accessing a database.

       -username	       user name for authenticating access to a database.

       -password	       password for accessing the selected database.

       -driver		       JDBC driver classname in your CLASSPATH.

       -schemaname	       name of the user schema being captured. If not specified, the default will capture metadata for all tables from all
			       the schemas accessible to this user. Specifying this parameter is highly recommended. If more than  one	schema	is
			       accessible to this user, more than one table with the same name may be captured which will cause problems.

       -table		       name of the table; multiple table names can be specified.

       -out		       output target; defaults to stdout. This parameter corresponds to the schema sub-element of the sun-cmp-mapping ele-
			       ment in the sun-cmp-mapping_1_1.dtd file.

EXAMPLES

       Example 1: Using capture-schema

       capture-schema -dburl jdbc:oracle:thin:@sadbuttrue:1521:ora817
       -schemaname cantiflas -username CANTIFLAS -password enigma -driver
       oracle.jdbc.driver.OracleDriver

       asadmin(1M)

Sun Java System Application Server				    March 2004							capture-schema(1m)
All times are GMT -4. The time now is 09:02 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy