Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Problem in connecting to db in a loop
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
Old 04-08-2016
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>

 

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... (3 Replies)
Discussion started by: S.Vishwanath
3 Replies

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:... (2 Replies)
Discussion started by: dragunu
2 Replies

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... (1 Reply)
Discussion started by: goodmis
1 Replies

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>... (6 Replies)
Discussion started by: Stevhp
6 Replies

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... (3 Replies)
Discussion started by: Devesh5683
3 Replies

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"... (2 Replies)
Discussion started by: Vi-Curious
2 Replies

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... (3 Replies)
Discussion started by: mraghunandanan
3 Replies

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... (2 Replies)
Discussion started by: SIFT3R
2 Replies

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... (2 Replies)
Discussion started by: Vishawdeep
2 Replies

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... (6 Replies)
Discussion started by: ajayakunuri
6 Replies

LEARN ABOUT OSX

platform::shell

platform::shell(n)					       Tcl Bundled Packages						platform::shell(n)

__________________________________________________________________________________________________________________________________________________

NAME

       platform::shell - System identification support code and utilities

SYNOPSIS

       package require platform::shell ?1.1.4?

       platform::shell::generic shell
       platform::shell::identify shell
       platform::shell::platform shell
_________________________________________________________________

DESCRIPTION

       The platform::shell package provides several utility commands useful for the identification of the architecture of a specific Tcl shell.

       This  package  allows the identification of the architecture of a specific Tcl shell different from the shell running the package. The only
       requirement is that the other shell (identified by its path), is actually executable on the current machine.

       While for most platform this means that the architecture of the interrogated shell is identical to the architecture of  the  running  shell
       this  is  not generally true. A counter example are all platforms which have 32 and 64 bit variants and where a 64bit system is able to run
       32bit code. For these running and interrogated shell may have different 32/64 bit settings and thus different identifiers.

       For applications like a code repository it is important to identify the architecture of the shell which will  actually  run  the  installed
       packages, versus the architecture of the shell running the repository software.

COMMANDS

       platform::shell::identify shell
	      This command does the same identification as platform::identify, for the specified Tcl shell, in contrast to the running shell.

       platform::shell::generic shell
	      This command does the same identification as platform::generic, for the specified Tcl shell, in contrast to the running shell.

       platform::shell::platform shell
	      This command returns the contents of tcl_platform(platform) for the specified Tcl shell.

KEYWORDS

       operating system, cpu architecture, platform, architecture

platform::shell 						       1.1.4							platform::shell(n)
All times are GMT -4. The time now is 11:01 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy