Loading CSV file data into a Table Variable (Index-by table) in Oracle

Question

My requirement is that I need to read the CSV file data and query it with one existing table in the Database to update some records. One approach I thought that to create a new table (temp) and load the CSV file into that table and query that with the existing table but I found that I don't have permission to create a new table or a directory (for external table approach).

Then I thought of doing this through a table variable but I'm not getting how to load the data into a table variable. I wrote the following query but it says

'invalid table name'

DECLARE
  TYPE t IS TABLE OF VARCHAR2(15);
  UPDATEPARTYID t;
BEGIN
  SELECT *
    BULK COLLECT INTO UPDATEPARTYID
    FROM 'C:\Test\PartyID.csv';
END;

I used to work on Sql Server, so not much comfortable with Oracle. I'm using Sql Developer and Oracle11g, there are millions of records in the .csv file. Any help would be appreciated.

Update:

Structure of the Input File:

OldID,NewID
015110044200015,099724838000015
069167641100015,099724838000015
016093943300015,099728485000015
033264160300015,099728485000015
035968914300015,099728485000015
087580324300015,099728485000015

There is a column named PartyID (Varchar2(15)) in the existing table where I need to update those IDs with the new party ID, which are matching with the OldID of the input file.

The structure of the new target table will be:

  From Party ID (Varchar2 15)
  To Party ID     (Varchar2 15)
  Created Date  Sysdate
  Updated Date  Sysdate 
  Status              Char (1) S: Success, F: Failure 
  No.Of Tries      Integer(3) Default value 0

If the number of tries are more than 3 then it will be marked as Failure.

I suggest using a csv file of 3 or 4 rows at the most to start with. — Dan Bracuk
– Dan Bracuk, Commented Oct 17, 2016 at 16:03
If you can get someone to create you a temp table for the data, your best option might be to use Oracle's SQL*Loader utility to bulk-load the data, after which you could update the target table. — Mick Mnemonic
– Mick Mnemonic, Commented Oct 17, 2016 at 16:14
If the record doesn't exist in the table, do you insert it or simply not perform any update? EIther way, the suggestion by @MickMnemonic is the way to go. If you can't do that, Oracle does have some nice bulk update capabilities with Oracle. It would help to know what your constraints are first, though. — Hambone
– Hambone, Commented Oct 18, 2016 at 2:10
@MickMnemonic Yes, Mick I've asked the admin to create a temp table, so that I could use SQL*Loader. Thanks. — user1547554
– user1547554, Commented Oct 18, 2016 at 8:21
@Hambone In that case I don't perform any update. Thanks for the comment. I'm trying to go as per Mick's suggestion. — user1547554
– user1547554, Commented Oct 18, 2016 at 8:24

Mick Mnemonic · Accepted Answer · 2016-10-21 07:34:47Z

1

For loading large amounts of data from a text file into Oracle, the SQL*Loader utility is a good choice. The software is included in the Oracle client installation (which you can download for example here).

Setup

Assuming you're importing the data into a target table (target_table) having the structure

CREATE TABLE target_table (
     from_party VARCHAR2(15) NOT NULL,
     to_party   VARCHAR2(15) NOT NULL,
     created    DATE,
     updated    DATE,
     status     CHAR(1),
     tries      NUMBER(1)
)

and using a two-column source data file (in path /path/to/party_import.csv) with the following structure

OldID,NewID
015110044200015,099724838000015
069167641100015,099724838000015
016093943300015,099728485000015
033264160300015,099728485000015
035968914300015,099728485000015
087580324300015,099728485000015

you can use a control file with the following contents:

OPTIONS (SKIP=1)
LOAD DATA
INFILE '/path/to/party_import.csv'
BADFILE 'import.bad'
INSERT
INTO TABLE target_table
fields terminated by ","  TRAILING NULLCOLS
(
  from_party,
  to_party,
  created sysdate
)

Running SQL*Loader

To run SQL*Loader, you can invoke the following command:

sqlldr username/pw@db_connection control=/path/to/control_file.ctl

This assumes that following setup has been done beforehand:

Oracle client (including SQL*Loader) has been installed
sqlldr.exe is in path, or the absolute path to the executable is used
You have configured the database connection (db_connection), either through Oracle's Net configuration assistant or by manually providing a tnsnames.ora file and setting the TNS_ADMIN environment variable (example here)

By default, the transaction is committed only after the whole file has been processed. If you want to commit e.g. every 1000 rows, you can use the ROWS option to do this:

sqlldr username/pw@db_connection control=/path/to/control_file.ctl ROWS=1000

answered Oct 21, 2016 at 7:34

Mick Mnemonic

7,9562 gold badges29 silver badges35 bronze badges

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

2 Comments

user1547554 Over a year ago

Thanks a lot Mick for the detailed answer. I wanted to ask one thing that is there a way to write the logic for Status and Tries column in the control file? How can I track these columns and insert in the table, if you've an idea, please let me know. :)

Mick Mnemonic Over a year ago

How should the values be populated? I didn't quite understand how those columns should be used.

Alexandre Nascimento · Accepted Answer · 2019-02-19 13:44:56Z

0

Another way using only C#:

Insert Bulk Data From CSV File To Database Table Using SQLBulkCopy Class

https://www.c-sharpcorner.com/article/insert-bulk-data-from-csv-file-to-database-table-using-sqlbu/

answered Feb 19, 2019 at 13:44

Alexandre Nascimento

1831 gold badge5 silver badges20 bronze badges

Collectives™ on Stack Overflow

Loading CSV file data into a Table Variable (Index-by table) in Oracle

2 Answers 2

Setup

Running SQL*Loader

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Setup

Running SQL*Loader

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related