Need Shell Script to upload data from Text file to Oracle database

Shell Programming and Scripting
1

Hi
Does any one have any idea on uploading the data using Unix Shell script from text file to Oracle database.
Requirement:-

  1. Need to connect to Oracle database from Unix Shell script.
  2. Need to pick Text file from some location on Unix Box.
  3. Need to upload the data from text file to some table in Oracle Database.

Any similar scripts matching above requirement is also highly appreciated.
Or any one have any idea on shell scripts available on net.
Advance Thanks
JC

2

Hi,

You can use sqlldr command directly from unix to do this.
Syntax is like this :

sqlldr control=ctlFile log=logFile bad=badFile

3

Below one is a good link explaining creating the control, data , load, bad log files..

Read this

4

Thanks a lot jacoden !!!
Regards
JC

5

Example, here is a utility that will take the output from Linux vmstat utility and insert the output into an Oracle table:

PATH=$ORACLE_HOME/bin:$PATH
export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME

# sample every five minutes (300 seconds) . . . .
SAMPLE_TIME=300

while true
do
vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$

# run vmstat and direct the output into the Oracle table . . .
cat /tmp/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9,
14, $15, $16) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU
IDLE_CPU
do

  $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@testsys1<<EOF
  insert into perfstat.stats\\$vmstat
  values \(
  sysdate, 
  $SAMPLE_TIME,
  '$SERVER_NAME',
  $RUNQUE,
  $PAGE_IN,
  $PAGE_OUT,
  $USER_CPU,
  $SYSTEM_CPU,
  $IDLE_CPU,
  0 
  \);
  EXIT
  EOF

done
done

rm /tmp/msg$$

(taken from this site)

6

"INSERT" will not be effective if there are thousands of records to be loaded...

7

use sqlloader, external table or pro*c