Problem in connecting to db in a loop Post: 302970576

Sponsored Content

Top Forums Shell Programming and Scripting Problem in connecting to db in a loop Post 302970576 by anijan on Friday 8th of April 2016 08:29:52 AM

04-08-2016

Registered User

Problem in connecting to db in a loop

Im trying to run a query in multiple db thro sqlplus in a loop written n shell script. whenevr there is a problem in connecting to db, the shell script abruptly exits. My requirement is , when db connection fails, the script should print the msg and contnue to connect to the next db mentioned in the file county_list Below is my code. Pls assist in getting it fixed.

Code:

cat county_list | while read details
do
county_code=`echo $details | cut -d '|' -f1`
county_name=`echo $details | cut -d '|' -f2`
usr_nm=`echo $details | cut -d '|' -f3`
passwd=`echo $details | cut -d '|' -f4`
domain=`echo $details | cut -d '|' -f5`

echo "Connecting to ${domain}"

$ORACLE_HOME/bin/sqlplus ${usr_nm}/${passwd}@${domain} @sqlquery.sql >> ${conn_dir}/${county_name}_conn.txt

if [ "$?" = "1" ]
then
echo "Connection problem"
continue
fi

echo "LOOP over for ${county_name}"
done

sqlquery.sql :

Code:

SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET MARKUP HTML OFF SPOOL OFF
SET PAGESIZE 50000
SET LINESIZE 5000
SET HEADING ON
SET TRIMSPOOL ON
SET TRIMOUT ON
COLUMN clogname NEW_VALUE logname NOPRINT
SET COLSEP ,
SET SERVEROUTPUT ON
SPOOL /tmp/uat_temp.txt

DECLARE
   v1_sql_err_code   CHAR (10) := NULL;
   v1_success        BOOLEAN := TRUE;
 v1_count NUMBER(7,2);
BEGIN
   DBMS_OUTPUT.enable (NULL);
   v1_success := TRUE;

   SELECT COUNT(*) INTO v1_count
     FROM se_usr a
    
DBMS_OUTPUT.put_line (v1_count);
EXCEPTION
   WHEN OTHERS
   THEN
      v1_success := FALSE;
      v1_sql_err_code := SUBSTR (SQLERRM, 1, 10);
      DBMS_OUTPUT.put_line (
         'EXCEPTION CAUGHT. SQL Error Code: ' || v1_sql_err_code);
END;
/
SPOOL OFF

OUTPUT ( for incorrect login detail in the first db connection. )

Code:

/home/dev/uat_report>./UAT_usr_login_report.sh
Connecting to ALA01r
Connection problem
/home/dev/uat_report>

anijan

View Public Profile for anijan

Find all posts by anijan

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Problem Connecting from Windows base OS to linux

I have a network consisting of Linux Server, Win-Nt & 95 OS. I am able to connect from Linux to Win-NT using either ftp / telnet in time. But it takes me around & more than 50 seconds to connect from Wint -Nt/95 to Linux using either ftp/telnet. My network is small & it hardly consists 20-25...

2. UNIX for Dummies Questions & Answers

Connecting with X-win problem

Hello all, I am trying to connect to my Solaris 8 system with X-win. However, when i try to connect, a black screen comes up, then closes up immediatley. I have checked /var/dt/Xerrors, and I am getting the folllowing: Warning: Missing charsets in String to FontSet conversion Warning:...

3. UNIX for Dummies Questions & Answers

Having A Problem Connecting to Oracle Using Cron

Can someone tell me how to prevent this? stty: standard input: Invalid argument ERROR: ORA-12154: TNS:could not resolve the connect identifier specified SP2-0306: Invalid option. Usage: CONN where <logon> ::= <username> | / ERROR: ORA-12162: TNS:net service name is incorrectly...

4. Programming

Problem Connecting to Socket

Can anyone help? I'm trying to write a program which will write to a socket. I can get the server to run, but always get an error when I try to connect. It gives me an error at the "connect" command. It's probably a simple error, but I can't seem to find it. #include <sys/socket.h>...

5. Shell Programming and Scripting

Error connecting oracle from inside while loop

Hi, I m trying to connect oracle database from inside while loop. I m trying to put the output of sql query in a flat file. Anyone please help me out. cat $FILE_NAME | \ while read da_name do $ORACLE_HOME/bin/sqlplus -s user_name/password@instance << EOF >> $OUTPUT_FILE select...

6. Post Here to Contact Site Administrators and Moderators

Problem connecting to www.unix.com

I've been having a problem connecting to the forum. I know it is just something with my computer but I have no idea what it is. It started last week. Every time I connect I get this: <?xml version="1.0" encoding="ISO-8859-1" ?> - <rss version="2.0"...

7. Shell Programming and Scripting

Problem in Connecting to Oracle Database using KornShell

Hello, I am very new to Scripting. I am having a Kornshell Script below for connecting to Oracle database. But getting an error while executing it. #!/bin/ksh ssh -X root@192.168.2.127 <perimuka> sleep 5 su - oracle sqlplus <mraghunandanan>/<peri123> <<eof Can anyone tell what is wrong...

8. UNIX for Dummies Questions & Answers

Problem connecting with Reflection X 14.1

Other people at work are able to connect but I am not. I am not sure if there is a setting that I am missing. Connecting from Win7 to Solaris 10. Connection attempt log: gnome-session Connecting 144.243.90.235 via TELNET Thu Mar 29 15:15:32 2012 login: *** Password: ******* Last login: Thu...

9. UNIX for Dummies Questions & Answers

Problem connecting FreeBSD VM(Hyper-v) to internet

I created a VM for FreeBSD on hyper-v and i am having trouble connecting it to the internet. My virtual machine config are as below: 100 GB HD 1GB RAM Generation 1 Two Network adapters 1. Legacy Network Adapter 2. Network Adapter both are connected to network switch "Internet" which...

10. Shell Programming and Scripting

Sqlplus not connecting the 2nd time in for loop

Hi, I am trying to get the rows(First step is to get the poolid's and then second step run a loop to get the output based on each pool id and third connection is to get the member id and pool id based on a different condition) where based of certain conditions and storing it in a file. I wrote the...

LEARN ABOUT LINUX

set_constraints

SET 
CONSTRAINTS(7)						   SQL Commands 						SET CONSTRAINTS(7)

NAME

       SET CONSTRAINTS - set constraint checking modes for the current transaction

SYNOPSIS

       SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

DESCRIPTION

       SET  CONSTRAINTS  sets  the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of
       each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

       Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY  IMMEDIATE,  or  NOT
       DEFERRABLE.  The  third	class  is  always  IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two classes start every
       transaction in the indicated mode, but their behavior can be changed within a transaction by SET CONSTRAINTS.

       SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints (which must  all  be	deferrable).  The  current
       schema  search  path  is  used  to find the first matching name if no schema name is specified. SET CONSTRAINTS ALL changes the mode of all
       deferrable constraints.

       When SET CONSTRAINTS changes the mode of a constraint from DEFERRED to IMMEDIATE, the new mode takes effect retroactively: any  outstanding
       data  modifications  that  would  have  been checked at the end of the transaction are instead checked during the execution of the SET CON-
       STRAINTS command.  If any such constraint is violated, the SET CONSTRAINTS fails (and does not change the constraint mode). Thus, SET  CON-
       STRAINTS can be used to force checking of constraints to occur at a specific point in a transaction.

       Currently,  only  foreign key constraints are affected by this setting. Check and unique constraints are always effectively not deferrable.
       Triggers that are declared as ``constraint triggers'' are also affected.

NOTES

       This command only alters the behavior of constraints within the current transaction. Thus, if you execute this command outside of a  trans-
       action block (BEGIN/COMMIT pair), it will not appear to have any effect.

COMPATIBILITY

       This command complies with the behavior defined in the SQL standard, except for the limitation that, in PostgreSQL, it only applies to for-
       eign-key constraints.

SQL - Language Statements					    2010-05-14							SET CONSTRAINTS(7)

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Problem Connecting from Windows base OS to linux

Discussion started by: S.Vishwanath

2. UNIX for Dummies Questions & Answers

Connecting with X-win problem

Discussion started by: dragunu

3. UNIX for Dummies Questions & Answers

Having A Problem Connecting to Oracle Using Cron

Discussion started by: goodmis

4. Programming

Problem Connecting to Socket

Discussion started by: Stevhp

5. Shell Programming and Scripting

Error connecting oracle from inside while loop

Discussion started by: Devesh5683

6. Post Here to Contact Site Administrators and Moderators

Problem connecting to www.unix.com

Discussion started by: Vi-Curious

7. Shell Programming and Scripting

Problem in Connecting to Oracle Database using KornShell

Discussion started by: mraghunandanan

8. UNIX for Dummies Questions & Answers

Problem connecting with Reflection X 14.1

Discussion started by: SIFT3R

9. UNIX for Dummies Questions & Answers

Problem connecting FreeBSD VM(Hyper-v) to internet

Discussion started by: Vishawdeep

10. Shell Programming and Scripting

Sqlplus not connecting the 2nd time in for loop

Discussion started by: ajayakunuri

LEARN ABOUT LINUX

set_constraints