我想使用 SQLLDR 加载以下 2 个表。 2个表的表结构如下：
CREATE TABLE Customer
(ID varchar2(50), --PK
org_cd varchar2(50), --PK
NAME VARCHAR2 (255),
Address1 VARCHAR2(1000),
DOB TIMESTAMP(3),
cust_ref_col number ---used for all the future references to this record since this is a number. This is unique key.
);
CREATE TABLE Customer_contact
(ID varchar2(50), --PK
org_cd varchar2(50), --PK
Contact_id Number, --PK --Running serial # for a given Customer
contact_name varchar2(50),
cust_ref_col number ---foreign key from Customer table
);
这是数据文件，客户.dat(最后一列值 1 是虚拟的，因为我想生成 Oracle 序列(合作伙伴序列(编号
PTNR_78814824,ACCT,Tom,123 Church Road, 12-dec-99,1,Ralph,1
PTNR_78814825,FIN,Tom,124 Main Road, 12-dec-99,2,Jody,1
PTNR_78814826,ENGG,Tom,125 Station Road, 12-dec-99,3,Mardy,1
我的控制文件看起来像这样
LOAD DATA
INFILE test.dat
INTO TABLE Customer
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ID ,
org_cd ,
name ,
Address1 ,
DOB ,
cust_ref_col "partnersequence.nextval"
)
INTO TABLE Customer_contact
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ID ,
org_cd ,
Fill1 Filler,
Fill2 Filler,
Fill3 Filler,
Fill4 Filler,
cust_ref_col "partnersequence.nextval"
)
这里的问题是表中Customer_contact cust_ref_col正在获取新序列#。我想使用为 生成的相同序列 客户表。你能帮忙吗？
编辑：我误解了OP的问题。 他正在寻找一种解决方案，其中两个表每行都有相同的序列号(我相信在评论中得到了回答(。 此答案显示了如何获得每个整个加载的唯一序列。 虽然不是答案，但我认为这些信息会有所帮助，所以我会离开它。
正如您已经发现的那样，在控制文件中调用 sequence.nextval 会导致它随着加载的每一行而递增。 诀窍是调用一个在实例化时设置值的包，然后调用返回加载的每一行的值的包函数。由于我已经为我的负载设置了它，我将与您分享我所做的解释。 我们的负载向表中添加了一个load_date和一个load_sequence_id，所以这就是你将在这些示例中看到的内容。假设读者已经理解了包的结构，因为如果不混淆主要问题，就无法在这里解释太多了。
您需要创建一个序列(您已经(和一个包。该包包含 2 个变量来保存load_date和load_seq_id，2 个"getter"函数来返回它们，以及代码在实例化时设置它们。 然后，您的控制文件将调用"getter"函数以从包中返回load_date和load_seq_id，每行都相同。
因此，在开始加载时，当要加载的第一行调用函数以获取序列时，将实例化包并设置日期和序列，然后返回。 只要会话处于活动状态，日期/序列就不会从此更改，对 getter 函数的后续调用将继续返回相同的值。
包装规格和正文：
CREATE OR REPLACE PACKAGE SCHEMA.LOAD_SEQ AS
/******************************************************************************
NAME: LOAD_SEQ
PURPOSE: Sets unique load_date and Load_seq_id per session when
the package is instantiated. Package functions are
intended to be called from control files so all rows in a
file load will have the same load_date and
load_seq_id.
When the functions are called, the package is instantiated and
the code at the bottom is run once for the session, setting the
load_date and load_seq_id. The functions simply return the values
which will remain the same for that session.
load_date date "MM/DD/YYYY" "to_char(trunc(schema.load_seq.get_load_date), 'mm/dd/yyyy')",
load_seq_id decimal external "schema.load_seq.get_load_seq_id"
(each row then has the same load_seq_id).
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2/20/2017 Gary_W 1. Created this package.
******************************************************************************/
NEXT_LOAD_SEQ_ID NUMBER;
NEXT_LOAD_DATE DATE;
FUNCTION GET_LOAD_SEQ_ID
RETURN NUMBER;
FUNCTION GET_LOAD_DATE
RETURN DATE;
END THC_LOAD_SEQ;
/
CREATE OR REPLACE PACKAGE BODY SCHEMA.LOAD_SEQ AS
/******************************************************************************
NAME: GET_LOAD_SEQ_ID
PURPOSE: Return the package variable LOAD_SEQ.NEXT_LOAD_SEQ_ID
which is set when the package is instantiated. It does not
change during the session.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2/20/2017 Gary_W 1. Created this package.
******************************************************************************/
FUNCTION GET_LOAD_SEQ_ID
RETURN NUMBER IS
BEGIN
RETURN LOAD_SEQ.NEXT_LOAD_SEQ_ID;
END GET_LOAD_SEQ_ID;
/******************************************************************************
NAME: GET_LOAD_DATE
PURPOSE: Return the package variable LOAD_SEQ.NEXT_LOAD_DATE
which is set when the package is instantiated. It does not
change during the session.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2/20/2017 Gary_W 1. Created this package.
******************************************************************************/
FUNCTION GET_LOAD_DATE
RETURN DATE IS
BEGIN
RETURN LOAD_SEQ.NEXT_LOAD_DATE;
END GET_LOAD_DATE;
BEGIN
-- Code outside of the procedures/functions defined in the spec runs
-- once on instantiation of the package, when the package is first called by the session.
-- It sets the package variables which then do not change during the life of the session.
SELECT SYSDATE, partnersequence.NEXTVAL
INTO LOAD_SEQ.NEXT_LOAD_DATE, LOAD_SEQ.NEXT_LOAD_SEQ_ID
FROM DUAL;
END LOAD_SEQ;
/
在控制文件中：
LOAD_DATE date "MM/DD/YYYY" "to_char(trunc(schema.load_seq.get_load_date), 'mm/dd/yyyy')"
cust_ref_col "decimal external "schema.load_seq.get_load_seq_id""