1

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.

user1547554
  • 441
  • 2
  • 8
  • 16
  • I suggest using a csv file of 3 or 4 rows at the most to start with. – Dan Bracuk Oct 17 '16 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 Oct 17 '16 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 Oct 18 '16 at 02:10
  • @MickMnemonic Yes, Mick I've asked the admin to create a temp table, so that I could use SQL*Loader. Thanks. – user1547554 Oct 18 '16 at 08: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 Oct 18 '16 at 08:24
  • @DanBracuk Yes, Dan I'm considering only 4-5 rows in the starting. – user1547554 Oct 18 '16 at 08:25
  • If you give some more details about the structure of the file and the update you need to perform, I can try to help out writing the Loader control file etc. – Mick Mnemonic Oct 18 '16 at 08:42
  • @MickMnemonic Hey, Mick I've updated my question with the structure, please look in to that. Thanks for your help. – user1547554 Oct 18 '16 at 09:55
  • @MickMnemonic Could you please check the updates and help me out. Thanks. – user1547554 Oct 20 '16 at 14:48
  • Yeah, sorry, I've been insanely busy. I'll write an answer within a couple of hours. – Mick Mnemonic Oct 20 '16 at 19:30

2 Answers2

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
Mick Mnemonic
  • 7,808
  • 2
  • 26
  • 30
  • 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. :) – user1547554 Oct 21 '16 at 09:00
  • How should the values be populated? I didn't quite understand how those columns should be used. – Mick Mnemonic Oct 21 '16 at 09:51
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/

Alexandre Nascimento
  • 183
  • 1
  • 5
  • 20