CODEWITHSUNDEEP
mysqloracle-databaseshellcsv
LearningCpp
LearningCpp

Reputation: 972

Bulk Copy from a csv file to OracleDB using shell script

I am trying to write a shell script which picks up data from a csv file and bulk copies (bcp) it to oracle db. I could write a shell script but not sure how sql bcp works.

Please advice me on how to proceed

Here is a snippet of the script

#!/bin/bash
echo "Now processing step: LOAD_"
DB_CONNECT="USER/PASS@TESTDB"
SPOOLFILE=/home/log.txt
echo "Now processing step:" $DB_CONNECT
sqlplus -s ${DB_CONNECT} <<EOF
spool ${SPOOLFILE}
file="/etc/hosts"
if [ -f "$file" ]
  then
echo "$file found."
TRUNCATE TABLE ;
else
echo "$file not found."
fi

Assuming my file is csv file with below data

1,2,3,4
5,6,7,8
10,11,12,13

Thanks

Upvotes: 0

Views: 4690

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22412

BCP is a SQL Server thing.

You're in Oracle land now.

However, if you want to load up a table from CSV using a script, we do have a few tools that can help you.

SQL*Loader is great for doing LOTS of data - millions of rows, no problem. And it's flexible, you can define/map the data from the input stream to your tables. It's part of the Oracle Client - so you'll need an $ORACLE_HOME on your machine to use it.

If you have a CSV where the columns match up in the same order as the table, and it's just a few thousand rows, you can also just use the LOAD command in SQLcl. It's part of SQL Developer (in the /bin directory), and it's available as a separate download. You'll need a JRE to run it.

Upvotes: 1

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

I'm not sure what you exactly means when saying 'bulk copies', but probably you should use Oracle SQL*Loader. It's a standart tool for bulk load into Oracle DB and it can work with CSV files too.

Documentation: https://docs.oracle.com/database/122/SUTIL/oracle-sql-loader.htm#SUTIL3311

Upvotes: 0

Related Questions