BULK INSERT ORACLE SQL / sql loader utiltiy

I am trying create a bulk insert of a csv file into Oracle Sql, I found this code on line, does Oracle allow for Bulk Inserts or is there another way to import data from a csv file?  is there an actual download file for SQL LOADER UTILTIY?

Thanks,

Karen
USE PROD
GO
BULK INSERT ORG_CHART
FROM '\\data\Metrics_Data_Repository\Org Charts Docs\Mgr_OrgChart_Data_kfs.csv'
WITH
  (
  FIELDTERMINATOR = ','
  , ROWTERMINATOR ='\n'
  )
GO

SELECT *
FROM ORG_CHART;

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Not sure where you found that code but it isn't Oracle or was that an example of what you are trying to achieve?

You shouldn't compare Oracle to other databases in specific terms like "bulk insert".

What problem are you trying to solve?  You can set commit points and/or perform inserts above the High Water Mark (HWM) which can speed up loads.  That may or may not be the best thing for your system.

SQL*Loader processes the lines one at a time but it does this very efficiently.  Loading data from flat files is what it does so it is optimized for that task.

Depending on the post-load processing you may need to do, there is a possible alternative:  External Tables.  They use the SQL*Loader engine but behave as a real, read-only, database table you can query directly.  There are many examples of them on the web.


>>is there an actual download file for SQL LOADER UTILTIY?

The server where the database is running has it be default.

From the client, several.  The easiest is the Instant Client and Utilities.

It will be two downloads:
Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
Instant Client Package - Tools: Includes Data Pump, SQL*Loader and Workload Replay Client

I would add a third, sqlplus, because it is the easiest way to query the database.  IMHO of course.
Instant Client Package - SQL*Plus: Additional libraries and executable for running SQL*Plus with Instant Client

You can get them from here:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
flow01Commented:
Probably sqlloader is already there because you can define an 'external table' of type oracle_loader

And you can use that the external table option:

-- make de operating system directory available to oracle
create directory data_dir as '\\data\Metrics_Data_Repository\Org Charts Docs';
-- i'm not sure of the \\  so maybe you need  '\data\Metrics_Data_Repository\Org Charts Docs'

-- define the external table :something like
create table ORG_CHART_ext
( column1 VARCHAR2(30),
 column2 VARCHAR2(30),
 column3 DATE
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
(
records delimited by newline
fields terminated by ','
missing field values are null
( column1 ,
  column2 ,
  column3 'dd-mon-yy'
)
)
location ('Mgr_OrgChart_Data_kfs.csv')
)
/

select * from ORG_CHART_ext;
insert into  ORG_CHART select * from ORG_CHART_ext;
slightwv (䄆 Netminder) Commented:
Back to the original question:  I did some Google'ing.  It appears that is SQL Server syntax.  I don't see where the "BULK INSERT" part does anything special when compared to SQL*Loader.

You still need to answer questions like commit points, HWM, etc. but they appear similar.

>>Probably sqlloader is already there because you can define an 'external table'

This assumes they are running this on the database server and not a remote client.

>>-- i'm not sure of the \\  so maybe you need

That is Uniform Naming Convention (UNC):
https://en.wikipedia.org/wiki/Path_(computing)#Uniform_Naming_Convention

I've never been able to get Oracle tools to recognize UNC but I haven't tried for several years.  Maybe the latest versions will accept it but I really doubt it.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Karen SchaeferBI ANALYSTAuthor Commented:
When you say to create table ORG_CHART_ext
( column1 VARCHAR2(30),
 column2 VARCHAR2(30),
 column3 DATE
)

Do you mean create a new table in ORacle, if so I do not have admin writes to the server>
Karen SchaeferBI ANALYSTAuthor Commented:
Ok can we take this step by step.

Need to create an import process for a csv file(stored on a network drive) without creating a new table each time.  I want to take the data from the excel file and insert into an existing table (ORG_CHART).  

I am using Oracle 12 and SQL Developer 4.0 and Excel 2013.  Note I am new to this process.  I am not familiar with oracle_loader.

What is the best way to automate this task?  Any help is greatly appreciated.

Disregard the \\ in the file name.  I removed the server name in my sample above.
Mark GeerlingsDatabase AdministratorCommented:
"Need to create an import process for a csv file(stored on a network drive) without creating a new table each time.  I want to take the data from the excel file and insert into an existing table (ORG_CHART)."

This is exactly the business problem that an "external table" in Oracle can help you solve.  Be aware that the Excel file that will be used as the basis for your "external table" must be in a directory that is on (or at least accessible by) the Oracle database server.

If you want to do this with an Excel file that is on your (or another) client PC, but not on the database server, then you need to use a different approach.

If your Oracle database server can see and read the *.csv file that is stored on a network drive, then you simply create an "external table" that points to this *.csv file.  Yes, "external tables" in Oracle actually use parts of the Oracle SQL Loader utility to get the job done.  And the syntax for "external tables" is very similar to the syntax for SQL Loader.

After your "external table" is created once, you simply need to update the contents of the Excel file it points to, then you can select from this external table and see the new contents, to copy them to whatever table you need them to be copied into.
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for your input, however, this approach will not work for me.  I was informed I do not have access the server drive for security issues.  Looks like I will have to use the ODBC connectivity approach.
Mark GeerlingsDatabase AdministratorCommented:
From a client PC, to load data from an Excel or *.csv file into Oracle you have two options:
1. SQL Loader
2. An ODBC connection from Excel to Oracle

For both of these you will need to have the Oracle client software installed on the PC.  If you want to use SQL Loader, this should be the same (or a higher) Oracle version as your Oracle database server.  

And for both of these, you need to configure a connection to your database server.  This connection information usually gets stored in the (plain text) file: tnsnames.ora that is part of the Oracle client installation.  The Oracle client includes a utility (Net Manager or Net Configuration Utility) that can be used to set up and test this connection information.  After that is set up and working, you can then create an ODBC connection that uses this Oracle Client connection.

For SQL Loader, you have to write a control file (just once) that SQL Loader can use each time to interpret your *.csv file, and know which table and columns to load the data into.  You simply have to create or rename your *.csv file each time to have the new contents you need.  The name and directory location of this *.csv file must match what you have specified in your control file for SQL Loader.  Then, you start SQL Loader from the C: prompt in a command window and give it the name of your control file.

For an ODBC connection from Excel (which I've never used) you have to do some configurations and/or create a macro in Excel (but I'm not an expert on those).
slightwv (䄆 Netminder) Commented:
If the file is in CSV format, forget about Excel.  Why would you want to take a CSV file, load it into Excel just to use ODBC to insert it into an Oracle table.

If this is a one-time thing:
In SQL Developer, right-click on the table you wish to import the data into and select Import.  Then locate the CSV file and follow the choices.

If you will continue to do this over time and want more of an automated way, use SQL*Loader.

If you cannot figure out the many examples out there post a sample test case with the destination table definition and same CSV data and we can set up a tested control file for you.
Karen SchaeferBI ANALYSTAuthor Commented:
I am trying to import the csv into oracle however, it wants me to create a new table, and I do not have the privileges.  How do I import the file  into an existing table without creating a new table?
Karen SchaeferBI ANALYSTAuthor Commented:
"For SQL Loader, you have to write a control file (just once) that SQL Loader can use each time to interpret your *.csv file, and know which table and columns to load the data into.  You simply have to create or rename your *.csv file each time to have the new contents you need.  The name and directory location of this *.csv file must match what you have specified in your control file for SQL Loader.  Then, you start SQL Loader from the C: prompt in a command window and give it the name of your control file"

Do you have a sample of what a TNSNames file should look like, also sample of the SQL Loader file ( I never used this method)
slightwv (䄆 Netminder) Commented:
>>How do I import the file  into an existing table without creating a new table?

Using sql developer, on the table you wish to import the data into:  Right click and select import.  It should import the data into that table.

Here is an example with images.  It uses an XLS spreadsheet.  Just select your CSV file instead.
https://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

>>Do you have a sample of what a TNSNames file should look like, also sample of the SQL Loader file ( I never used this method)

There are examples all over the web.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.