Hi Tom
I have an input file that contains data that is tab delimited containing info like name, type, date, result, value as separate rows. The fields are not enclosed in quotes or anything else. I need to populate sequence for the data while loading into the table.
The requirement is that i need to assign a sequence to a set of records i.e for a given first and last name the sequence number should be the same i.e all the five rows will get the same sequence and the sequence should get increments for the next batch of records.
Sample data from input file
PanelBarcode: 12353080084
TestProgram: MPKMSK
Operator: CATX
Name: SERIAL
Type: SERIAL
Date: 06/13/2017
Result: PASS
Value: 1000000001
~#~
Name: MFG_PART_NUM
Type: MFG_PART_NUM
Date: 06/13/2017
Result: PASS
Value: 1RE-ABC7
~#~
Name: MFG_REV
Type: MFG_REV
Date: 06/13/2017
Result: FAIL
Value: V1
I tried out using case stmt for seq population
LOAD DATA
TRUNCATE
INTO TABLE MyTable
WHEN (1:3) <> '~#~'
FIELDS TERMINATED BY x'09'
TRAILING NULLCOLS
(COLNAME,
COLVALUE,
BATCH_SEQ "CASE WHEN :COLNAME='Name:' THEN MySeq.NEXTVAL ELSE MySeq.CURRVAL end"
)
Also tried few other ways.... created a function to check the count of records in the target table and assign the count to batch_seq for each record at run time....
Tried to increment the sequence for a batch...
LOAD DATA
TRUNCATE
INTO TABLE MyTable
WHEN (1:3)='~#~'
TRAILING NULLCOLS
(RECORD_INDICATOR FILLER CHAR,
COLNAME,
COLVAL,
BATCH_SEQ "MySeq.NEXTVAL"
)
INTO table MyTable
WHEN (1:3)!='~#~'
TRAILING NULLCOLS
(COLNAME,
COLVAL,
BATCH_SEQ "MySeq.CURRVAL"
)
But am unable to get the required output.....
The easier way could have been to populate the seq after loading data into the table but the requirement is to assign the sequence during data load itself.
Can you please let me know if there is a way to do this.
Thanks
Sounds like a perfect use case for external tables, eg
SQL> create TABLE MyTable
2 (COLNAME varchar2(50),
3 COLVALUE varchar2(50)
4 )
5 /
Table created.
SQL>
SQL> CREATE TABLE MyTable_ext
2 (
3 "COLNAME" VARCHAR2(50),
4 "COLVALUE" VARCHAR2(50)
5 )
6 ORGANIZATION external
7 (
8 TYPE oracle_loader
9 DEFAULT DIRECTORY TEMP
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
13 BADFILE 'file.bad'
14 LOGFILE 'file.log'
15 READSIZE 1048576
16 FIELDS TERMINATED BY 0x'09' LDRTRIM
17 MISSING FIELD VALUES ARE NULL
18 REJECT ROWS WITH ALL NULL FIELDS
19 (
20 "COLNAME" CHAR(255) TERMINATED BY 0x'09',
21 "COLVALUE" CHAR(255) TERMINATED BY 0x'09'
22 )
23 )
24 location ( 'file.dat' ))REJECT LIMIT UNLIMITED;
Table created.
SQL>
SQL> select * from MyTable_ext;
COLNAME COLVALUE
-------------------------------------------------- --------------------------------------------------
Name: SERIAL
Type: SERIAL
Date: 06/13/2017
Result: PASS
Value: 1000000001
~#~
Name: MFG_PART_NUM
Type: MFG_PART_NUM
Date: 06/13/2017
Result: PASS
Value: 1RE-ABC7
~#~
Name: MFG_REV
Type: MFG_REV
Date: 06/13/2017
Result: FAIL
Value: V1
~#~
Name: SERIAL
Type: SERIAL
Date: 06/13/2017
Result: PASS
Value: 1000000001
~#~
Name: MFG_PART_NUM
Type: MFG_PART_NUM
Date: 06/13/2017
Result: PASS
Value: 1RE-ABC7
~#~
Name: MFG_REV
Type: MFG_REV
Date: 06/13/2017
Result: FAIL
Value: V1
~#~
36 rows selected.
SQL>
SQL> select rownum r, e.* from MyTable_ext e;
R COLNAME COLVALUE
---------- -------------------------------------------------- -------------------------------------------
1 Name: SERIAL
2 Type: SERIAL
3 Date: 06/13/2017
4 Result: PASS
5 Value: 1000000001
6 ~#~
7 Name: MFG_PART_NUM
8 Type: MFG_PART_NUM
9 Date: 06/13/2017
10 Result: PASS
11 Value: 1RE-ABC7
12 ~#~
13 Name: MFG_REV
14 Type: MFG_REV
15 Date: 06/13/2017
16 Result: FAIL
17 Value: V1
18 ~#~
19 Name: SERIAL
20 Type: SERIAL
21 Date: 06/13/2017
22 Result: PASS
23 Value: 1000000001
24 ~#~
25 Name: MFG_PART_NUM
26 Type: MFG_PART_NUM
27 Date: 06/13/2017
28 Result: PASS
29 Value: 1RE-ABC7
30 ~#~
31 Name: MFG_REV
32 Type: MFG_REV
33 Date: 06/13/2017
34 Result: FAIL
35 Value: V1
36 ~#~
36 rows selected.
SQL>
SQL> select
2 last_value(case when colname = 'Name:' then r end ignore nulls) over ( order by r ) seq,
3 x.*
4 from
5 ( select rownum r, e.* from MyTable_ext e ) x;
SEQ R COLNAME COLVALUE
---------- ---------- -------------------------------------------------- --------------------------------
1 1 Name: SERIAL
1 2 Type: SERIAL
1 3 Date: 06/13/2017
1 4 Result: PASS
1 5 Value: 1000000001
1 6 ~#~
7 7 Name: MFG_PART_NUM
7 8 Type: MFG_PART_NUM
7 9 Date: 06/13/2017
7 10 Result: PASS
7 11 Value: 1RE-ABC7
7 12 ~#~
13 13 Name: MFG_REV
13 14 Type: MFG_REV
13 15 Date: 06/13/2017
13 16 Result: FAIL
13 17 Value: V1
13 18 ~#~
19 19 Name: SERIAL
19 20 Type: SERIAL
19 21 Date: 06/13/2017
19 22 Result: PASS
19 23 Value: 1000000001
19 24 ~#~
25 25 Name: MFG_PART_NUM
25 26 Type: MFG_PART_NUM
25 27 Date: 06/13/2017
25 28 Result: PASS
25 29 Value: 1RE-ABC7
25 30 ~#~
31 31 Name: MFG_REV
31 32 Type: MFG_REV
31 33 Date: 06/13/2017
31 34 Result: FAIL
31 35 Value: V1
31 36 ~#~