Connect once db disconnect after all execution


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Connect once db disconnect after all execution
# 1  
Old 07-15-2013
Connect once db disconnect after all execution

Hi All,

Please see the below code.

it is working fine when in 'test_file' have only one emplid.

test_file contains only emplid and date, like below ...
0000221|1/12/2003
0000223|1/1/1996

Problem :-
when test_file contains more then one records(emplids) it is not giving any errors but it is disconnecting from database and
always connecting to database for each and every emplid..

But i want once connect to data base and after perform all execution it would disconnect from data base.
Please help me on this problem.
HTML Code:
#!/bin/sh
set -x
echo "test for multiple values"
UIDPSWD=`cat /projects/feeds/twest/uidpswd`
echo "oracle connection test"
full=/projects/feeds/twest/test_file
#values=`cut -d'|' -f1 $full|sed -e "s/.*/'&'/" -e 's/$/;/g' -e '$s/,$//'`
#below command will work in bourne shell
#values=(`awk -F"|" '{print $1}' $full`)
#below command will work in korn shell
set -A values $(awk -F"|" '{print $1}' $full)
for i in ${values[*]}
do
sqlplus -s $UIDPSWD <<EOF >>/projects/feeds/twest/result.txt
set serveroutput on
set verify off
set heading off
set feedback off
set ECHO OFF
--spool /projects/feeds/twest/ouput_file.txt
declare
cursor c1
is select emplid,ACTION,ACTION_DT,ACTION_REASON
from ps_job
where emplid='$values';
c2 ps_job%rowtype;
begin
DBMS_OUTPUT.PUT_LINE( 'emplid'|| chr(9) ||'ACTION'|| chr(9) ||'ACTION_DT'|| chr(9) ||'ACTION_REASON');
for i in c1 loop
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(i.emplid|| chr(9) ||i.action||chr(9)||i.action_dt||chr(9)||i.ACTION_REASON);
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('test Update query: ' || SQLERRM);
end;
/
exit;
EOF
done
Thanks & Regards,
Krupa
# 2  
Old 07-15-2013
You can define a variable with list of empids separated by comma:
Code:
while IFS="|" read empid skip
do
        [ -z "$qid" ] && qid="$empid" || qid="${qid},${empid}"

done < test_file

And use that in your cursor:
Code:
cursor c1
is select emplid,ACTION,ACTION_DT,ACTION_REASON
from ps_job
where emplid in ('$qid');

# 3  
Old 07-17-2013
Connect once db disconnect after all execution

can you please explain about this code ,actually i didn't get

Thanks,
Krupa
# 4  
Old 07-17-2013
Quote:
Originally Posted by krupasindhu18
can you please explain about this code ,actually i didn't get

Thanks,
Krupa
Code:
# Set IFS (Internal Field Separator) to pipe |
# Read first field into variable: empid rest of fields into: skip
while IFS="|" read empid skip
do
        # If variable qid is empty, assign empid value to qid
        # If variable qid is not empty, append empid value to qid separated by comma
        [ -z "$qid" ] && qid="$empid" || qid="${qid},${empid}"

done < test_file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Trigger the execution of a script on SFTP Disconnect

Hi Guys, I suspect what I'm trying to do isn't possible, but I'm hoping someone can either confirm this or point me in the right direction. We have a third-party application which transfers a collection of files to our SFTP server ( Ubuntu 12.04 with OpenSSH ) . Once the app disconnects, we... (13 Replies)
Discussion started by: jamesdrinkwater
13 Replies

2. UNIX for Dummies Questions & Answers

How to disconnect telnet prompt with port no in script.

Hi Gurus, I am trying to write a script for checking the status of linux servers by connecting via telnet with port no but to terminate i have to manually type "quit" .how can i terminate the telnet session in script itself.For E.g ========================================= telnet ipaddress... (3 Replies)
Discussion started by: kapil514
3 Replies

3. Shell Programming and Scripting

Disconnect scripts

I think I read that by entering "disconnect <script_name>" somewhere in pppd, that when ppp connection is lost that the named script will be executed. I also see that something similar can be (if I read correctly) in ip-down. Which is correct or are both correct. I want to set off the system... (5 Replies)
Discussion started by: slak0
5 Replies

4. Shell Programming and Scripting

In real time - what user connects or disconnect

Write a tool that will be displayed in real time, what user connects or disconnects from a system that uses this tool :)) Maybe anyone can help me? (2 Replies)
Discussion started by: titasas
2 Replies

5. UNIX for Advanced & Expert Users

Extract Oracle DB Connect and SQL execution log

Hi, I am trying to write a generic script as a part of a framework which will establish Oracle DB connection once and loops in to check for some files which gives the SQL statements to execute. The script is running but I am stuck with capturing errors ( ORA and SP) and outputs. Example: ... (4 Replies)
Discussion started by: mirage0809
4 Replies

6. Shell Programming and Scripting

vpn connect/disconnect shell script

Hi I am not so good in scripting..trying ot learn it...need guidance of the experts in shell scripting.. Let me explain the scenario first.. a server MX1 is connected to another server MX2 through vpn..every 5 minute a script runs to test vpn connectivity between the 2 servers.when the vpn... (12 Replies)
Discussion started by: renuka
12 Replies

7. Shell Programming and Scripting

Is it possible..when ftp session disconnect and it can automatic run again?

Hi, Is is possible when ftp script disconnect by remote server and it can restart to tranfer (such as restart in 10 mins, etc)? Please help!!!! (1 Reply)
Discussion started by: happyv
1 Replies

8. Shell Programming and Scripting

after server disconnect ftp tranfer......

Hi, I have a problem for ftp file to server. If the server due to some reason (such as unstable) and disconnect my ftp script. Can the ftp keep automatic retry to connect? If yes, the files can be keep sending to host without duplicate? HOST=hostname USER=happyv PASSWD=happyv exec 4>&1... (2 Replies)
Discussion started by: happyv
2 Replies

9. UNIX for Advanced & Expert Users

Force user to disconnect if no activity

Does anybody know how to force a user to automatically logoff a UNIX session if there is no keyboard activity for a period of time? We use COBOL and there is a BEFORE TIME option on the ACCEPT command, however, we do not want to change the many programs we have to detect this. What we really... (3 Replies)
Discussion started by: MarkN
3 Replies

10. UNIX for Dummies Questions & Answers

Connect over ssh, start something, disconnect

i'm connecting with ssh from a windows pc to a linux system. i want to start e.g. a download and close my session afterwards. how do i do this without killing the download? thx in advance. (2 Replies)
Discussion started by: sTorm
2 Replies
Login or Register to Ask a Question