Home » RDBMS Server » Server Utilities » SQLLOAD into 2 tables: Mater and Detail with same key
| SQLLOAD into 2 tables: Mater and Detail with same key [message #116265] |
Mon, 18 April 2005 23:36  |
karlcchu
Messages: 2
Registered: April 2005
|
Junior Member |
|
|
There are 2 tables: MASTER and DETAIL as shown:
MASTER
NAME NULL TYPE
file_id NOT NULL NUMBER(16)
col_a VARCHAR2(14)
....
where Primary Key = file_id
DETAIL
NAME NULL TYPE
file_id NOT NULL NUMBER(16)
col_x NOT NULL VARCHAR2(3)
col_y NOT NULL VARCHAR2(16)
col_z NOT NULL VARCHAR2(1)
....
where Primary key = (file_id + col_x + col_y + col_z)
Hopefully, a text file abc.txt is loaded to DETAIL and HEADER with a SAME and UNIQUE file_id (sequence number or a unique no. returned from a package):
abc.txt
001,FIRST RECORD,0
001,FIRST RECORD,1
001,SECOND RECORD,0
002,SECOND RECORD,0
DETAIL
file_id col_x col_y col_z
00000001 001 FIRST RECORD 0
00000001 001 FIRST RECORD 1
00000001 001 SECOND RECORD 0
00000001 002 SECOND RECORD 0
HEADER
file_id col_a
00000001 20050419000000 <---- /* only one record */
In next sqlload, the file_id should be 0000002 in DETAIL and HEADER. Please help
Many Thanks!
|
|
|
|
| Re: SQLLOAD into 2 tables: Mater and Detail with same key [message #116399 is a reply to message #116265] |
Tue, 19 April 2005 17:01  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your requirements of one row in the header table and multiple rows in the detail table, using the same sequence, make it difficult, if not impossible, to use traditional sequences or package variables, where the sequence currval and package variable of the previous session would be inaccessible to the next session. Please see the demonstration below for a possible solution.
-- contents of abc.txt:
001,FIRST RECORD,0
001,FIRST RECORD,1
001,SECOND RECORD,0
002,SECOND RECORD,0
-- contents of test.ctl:
LOAD DATA
INFILE 'abc.txt'
APPEND
INTO TABLE detail
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(col_x,
col_y,
col_z,
file_id "test_func")
-- header and detail tables:
scott@ORA92> CREATE TABLE header -- master
2 (file_id NUMBER(16) NOT NULL,
3 col_a VARCHAR2(14),
4 CONSTRAINT master_pk PRIMARY KEY (file_id))
5 /
Table created.
scott@ORA92> CREATE TABLE detail
2 (file_id NUMBER(16) NOT NULL,
3 col_x VARCHAR2(3) NOT NULL,
4 col_y VARCHAR2(16) NOT NULL,
5 col_z VARCHAR2(1) NOT NULL,
6 CONSTRAINT detail_pk PRIMARY KEY (file_id, col_x, col_y, col_z))
7 /
Table created.
-- table to hold current sequence:
scott@ORA92> CREATE TABLE seq_tab
2 (seq NUMBER)
3 /
Table created.
scott@ORA92> INSERT INTO seq_tab VALUES (0)
2 /
1 row created.
scott@ORA92> COMMIT
2 /
Commit complete.
-- function to retrieve current sequence:
scott@ORA92> CREATE OR REPLACE FUNCTION test_func
2 RETURN VARCHAR2
3 AS
4 v_seq NUMBER;
5 BEGIN
6 SELECT seq
7 INTO v_seq
8 FROM seq_tab;
9 RETURN v_seq;
10 END test_func;
11 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
-- 1st load using abc.txt data:
scott@ORA92> UPDATE seq_tab
2 SET seq = seq + 1
3 /
1 row updated.
scott@ORA92> INSERT INTO header
2 SELECT seq, TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS')
3 FROM seq_tab
4 /
1 row created.
scott@ORA92> COMMIT
2 /
Commit complete.
scott@ORA92> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test1.log
-- results after 1st load:
scott@ORA92> SELECT * FROM header
2 /
FILE_ID COL_A
---------- --------------
1 20050419134157
scott@ORA92> SELECT * FROM detail
2 /
FILE_ID COL COL_Y C
---------- --- ---------------- -
1 001 FIRST RECORD 0
1 001 FIRST RECORD 1
1 001 SECOND RECORD 0
1 002 SECOND RECORD 0
-- 2nd load using abc.txt data:
scott@ORA92> UPDATE seq_tab
2 SET seq = seq + 1
3 /
1 row updated.
scott@ORA92> INSERT INTO header
2 SELECT seq, TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS')
3 FROM seq_tab
4 /
1 row created.
scott@ORA92> COMMIT
2 /
Commit complete.
scott@ORA92> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test2.log
-- results after 2nd load:
scott@ORA92> SELECT * FROM header
2 /
FILE_ID COL_A
---------- --------------
1 20050419134157
2 20050419134158
scott@ORA92> SELECT * FROM detail
2 /
FILE_ID COL COL_Y C
---------- --- ---------------- -
1 001 FIRST RECORD 0
1 001 FIRST RECORD 1
1 001 SECOND RECORD 0
1 002 SECOND RECORD 0
2 001 FIRST RECORD 0
2 001 FIRST RECORD 1
2 001 SECOND RECORD 0
2 002 SECOND RECORD 0
8 rows selected.
|
|
|
|
| Re: SQLLOAD into 2 tables: Mater and Detail with same key [message #116435 is a reply to message #116265] |
Wed, 20 April 2005 02:22 |
karlcchu
Messages: 2
Registered: April 2005
|
Junior Member |
|
|
I worked out a solution using a trigger and a package as below:
CREATE OR REPLACE PACKAGE PKG_DETAIL AS
last_file_id NUMBER(16);
END PKG_DETAIL;
/
CREATE OR REPLACE TRIGGER TRG_DETAIL
BEFORE INSERT OR UPDATE ON DETAIL
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
IF PKG_DETAIL.last_file_id IS NULL THEN
PKG_DETAIL.last_file_id := DBA_UTIL.NEXT_TIME_STAMP;
INSERT INTO HEADER
(FILE_ID, UPL_DATE_TIME, DB_TIME_STAMP)
VALUES
(PKG_DETAIL.last_file_id, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'), DBA_UTIL.NEXT_TIME_STAMP);
END IF;
:NEW.FILE_ID := PKG_DETAIL.last_file_id;
END IF;
....
END;
/
Where DBA_UTIL.NEXT_TIME_STAMP is a unique number returned by another package. Then, when I sqlload to DETAIL table, the MASTER one will be inserted a record with same unique key (i.e. DBA_UTIL.NEXT_TIME_STAMP)
BTW, thanks for your great help!
Regards,
Charles
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 07:58:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
