将CSV文件数据加载到Oracle中的表变量(索引表)中 [英] Loading CSV file data into a Table Variable (Index-by table) in Oracle

问题描述

我的要求是，我需要读取CSV文件数据并使用数据库中的一个现有表进行查询以更新某些记录.我认为一种方法是创建一个新表(临时表)并将CSV文件加载到该表中，并使用现有表进行查询，但是我发现我无权创建新表或目录(用于外部表)方法).

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;

我以前在Sql Server上工作，所以对Oracle不太满意.我正在使用Sql Developer和Oracle11g，.csv文件中有数百万条记录.任何帮助，将不胜感激.

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.

更新:

输入文件的结构:

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

现有表中有一个名为PartyID(Varchar2(15))的列，我需要用新的Party ID更新那些ID，这些ID与输入文件的OldID相匹配.

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.

新目标表的结构为:

  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

如果尝试次数大于3，则将其标记为失败".

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

推荐答案

要将文本文件中的大量数据加载到Oracle中，请此处).

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).

假设您要将数据导入具有结构的目标表(target_table)

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)
)

，并使用具有以下结构的两列源数据文件(在路径/path/to/party_import.csv中)

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
)

运行SQL * Loader

要运行SQL * Loader，可以调用以下命令:

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客户端(包括SQL * Loader)
• sqlldr.exe在路径中，或使用了可执行文件的绝对路径
• 您已经通过Oracle的Net配置助手或通过手动提供tnsnames.ora文件并设置TNS_ADMIN环境变量来配置数据库连接(db_connection)(示例

• 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)

默认情况下，仅在处理完整个文件后才提交事务.如果您想提交例如每1000行，您可以使用ROWS选项执行此操作:

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

这篇关于将CSV文件数据加载到Oracle中的表变量(索引表)中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！