Visit Our UNIX and Linux User Community


shellscript to read data from txt file and import to oracle db


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting shellscript to read data from txt file and import to oracle db
# 1  
Old 12-21-2011
shellscript to read data from txt file and import to oracle db

Hi all,
Help needed urgently.
I am currently writing a shellscript to read data/record from a flat file (.txt) file, and import/upload the data to oracle database. The script is working fine, but it takes too long time (for 18000 records, it takes around 90 mins).

I guess it takes so long time to insert to db is because the login to db each time after reading one line of record from txt file (sqlplus -s username/password <<EOF)

login to db each time before insert statement because i want switch the environment to oracle db, after insert, i will exit, so that it will point me back to the backend server to get the dir.txt file.

Is this the correct way?
Is there any way/command to use so that no need to switch between environment? or meaning to say, no need to login to db each time when i want to insert a row of data.

Hope somebody can help as i am newbie to shellscript. thx a lot in advance!!!

Below is the codes:
Check if $FILE exists or not
Code:
if test ! -f "dir.txt"
then
echo "Error - $FILE not found or mcelog is not configured for 64
bit Linux systems."
exit 1
else
echo "dir.txt file exist"
fi
 
#start for dir.txt
FILENAME='dir.txt'
count=0
cat $FILENAME | while read LINE
do
 
dname=`echo "$LINE" | awk '{ print substr( $0, 0, 44 ) }'|sed "s/\'/\'\'/g"`
dicnum1=`echo "$LINE" | awk '{ print substr( $0, 45, 26 ) }'` 
dposition=`echo "$LINE" | awk '{ print substr( $0, 71, 26 ) }'|sed "s/\'/\'\'/g"`
dcompnm=`echo "$LINE" | awk '{ print substr( $0, 97, 8 ) }'|sed "s/\'/\'\'/g"`
dcpicno=`echo "$LINE" | awk '{ print substr( $0, 105, 14 ) }'`
dcpname=`echo "$LINE" | awk '{ print substr( $0, 119, 46 ) }'|sed "s/\'/\'\'/g"` 
dcprel=`echo "$LINE" | awk '{ print substr( $0, 165, 20 ) }'`
NOW=$(date +"%d/%m/%Y %H:%M:%S")
 
sqlplus -s username/password <<EOF
set scan off;
Insert into T_DIR_TEST (INSERT_DATE,S_NAME,S_NEW_IC,S_POSITION,S_COMPANY,CP_NAME,CP_IC_NO,CP_REL) VALUES (to_date('$NOW','dd/mm/yyyy HH24:MI:SS'),'$dname','$dicnum1','$dposition','$dcompnm','$dcpname','$dcpicno','$dcprel');
Exit
EOF
done


Last edited by Franklin52; 12-21-2011 at 04:15 AM.. Reason: Please use code tags for code and data samples, thank you
# 2  
Old 12-21-2011
Placing sqlplus within while loop wont be a good idea ...

Try with the below modified one ..
Code:
[ -f "dir.txt" ] && echo "File present" || exit 1
while read line
do
      << your variable declaration code >>
      echo "<< your full sql insert code >> " >> /your/path/to/insert_query.sql
done < dir.txt
 
##Here connect the DB to insert 
 
sqlplus -s username/password <<EOF
set scan off;
@/your/path/to/insert_query.sql
exit;
EOF


Previous Thread | Next Thread
Test Your Knowledge in Computers #5
Difficulty: Easy
The UNIX trademark is a "certification mark".
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI I have a dsv file that looks like: <<BOF>> record_number|id_number|first name|last name|msisdn|network|points|card number|gender 312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M 313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M... (4 Replies)
Discussion started by: tera
4 Replies

2. Shell Programming and Scripting

How to get the shell script to read the .txt file as an input/data?

i have written my shell script in notepad however i am struggling to pass the data file to be read to the script the data file is of .txt format. My target is to run the shell script from the terminal and pass 3 arguments e.g. polg@DESKTOP-BVPDC5C:~/CS1420/coursework$ bash valsplit.sh input.txt... (11 Replies)
Discussion started by: Gurdza32
11 Replies

3. Homework & Coursework Questions

Oracle dump file (del format) import into db2

1. The problem statement, all variables and given/known data: are the oracle dump files compatible to direct import into db2? I already tried many times but it always truncated results. anyone can help/ advice or suggest? 2. Relevant commands, code, scripts, algorithms: exp... (3 Replies)
Discussion started by: Sonny_103024
3 Replies

4. UNIX and Linux Applications

How to import and dump file to remote Oracle server?

Hi All, I have a linux centos instance which has a dump file. I need to import the dump file to the oracle server which is located at some remote location. I have installed the oracle client on my machine and I am able to connect to the remote oracle server. Now how to import the dump to the... (3 Replies)
Discussion started by: Palak Sharma
3 Replies

5. Shell Programming and Scripting

How to import a value from txt file

Hello, I want to give a value from a txt file to my variable at my ksh script. I`ve searched on the net and I found the command variable < file.txt but it cannot see the file. The .txt file contains two values in the first and the second line. Is there any way to give the first-line value to... (1 Reply)
Discussion started by: chris_euop
1 Replies

6. Shell Programming and Scripting

Want to read data from a file name.txt and search it in another file and then matching...

Hi Frnds... I have an input file name.txt and another file named as source.. name.txt is having only one column and source is having around 25 columns...i need to read from name.txt line by line and search it in source file and then save the result in results file.. I have a rough idea about the... (15 Replies)
Discussion started by: ektubbe
15 Replies

7. Programming

import .txt and split word into array C

Hi, if I want to import .txt file that contain information and the number separate by space how can I split and put into array In C Example of .txt file 3 Aqaba 49789 10000 5200 25.78 6987 148976 12941 15.78 99885 35262 2501 22.98 Thank (3 Replies)
Discussion started by: guidely
3 Replies

8. Shell Programming and Scripting

shellscript.query Oracle table..populate in a text file

Hi Guys, I'm new to this forum as well as to UNIX shell scripting. I'm looking for a shellscript to query an Oracle database table and populate the result set of the query in a text file. Could you someone help me out with a sample code? Thanks, Bhagat (7 Replies)
Discussion started by: bhagat.singh-j
7 Replies

9. Shell Programming and Scripting

txt file to oracle database

hiya, i have a query: i want to read a file which contains: 2005/02/21 16:56:54.301: 111 PS (200, 10) sent <log instrument="FXA.ROSS"... (9 Replies)
Discussion started by: jorhul
9 Replies

10. Shell Programming and Scripting

Import data from compressed file

HI I need to import data from a file which is in comressed format but system doesn't have enough space to uncompress file Is there any way so that i can do import from compressed file. (4 Replies)
Discussion started by: ap_gore79
4 Replies

Featured Tech Videos