Bulk Copy from a csv file to OracleDB using shell script

Question

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

thatjeffsmith · Accepted Answer · 2017-11-10 15:00:21Z

1

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.

answered Nov 10, 2017 at 15:00

thatjeffsmith

22.7k6 gold badges44 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sanders the Softwarer · Accepted Answer · 2017-11-10 12:48:21Z

0

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

answered Nov 10, 2017 at 12:48

Sanders the Softwarer

2,5061 gold badge15 silver badges29 bronze badges

Collectives™ on Stack Overflow

Bulk Copy from a csv file to OracleDB using shell script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related