| Sequence while loading through SQL*Loader [message #152257] |
Thu, 22 December 2005 00:00  |
himang
Messages: 282
Registered: March 2005
Location: Bangalore
|
Senior Member |
|
|
Control File.
Options ( Direct = True)
LOAD DATA
TRUNCATE
INTO TABLE FPD_STG_LogDetails
WHEN (01:05) = 'START'
(
Log_Index SEQUENCE(MAX,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)
INTO TABLE FPD_STG_LogDetails
WHEN (01:03) = 'END'
(
Log_Index SEQUENCE(MAX,1),
Log_Description POSITION(1) Char(4000),
LogFile_ID Constant '1'
)
Data File
START: read resource_maintenance_data (NULL) (03:00:19)
END: read resource_maintenance_data (NULL) (03:00:19): 0.000 sec
START: read operation_resources_data (NULL) (03:00:19)
END: read operation_resources_data (NULL) (03:00:19): 0.000 sec
START: read cycle_resources (NULL) (03:00:19)
END: read cycle_resources (NULL) (03:00:19): 0.000 sec
START: read cycle_calendar_data (NULL) (03:00:19)
END: read cycle_calendar_data (NULL) (03:00:19): 0.000 sec
START: read cycle_routings_data (NULL) (03:00:19)
END: read cycle_routings_data (NULL) (03:00:19): 0.000 sec
START: read bill_of_materials_data (NULL) (03:00:19)
END: read bill_of_materials_data (NULL) (03:00:24): 4.985 sec
START: read part_fair_share_distribution_data (NULL) (03:00:24)
END: read part_fair_share_distribution_data (NULL) (03:00:24): 0.000 sec
START: read routing_distribution_data (NULL) (03:00:24)
END: read routing_distribution_data (NULL) (03:00:24): 0.000 sec
START: read attribute_distribution (NULL) (03:00:24)
END: read attribute_distribution (NULL) (03:00:24): 0.000 sec
START: read demand_order_data (NULL) (03:00:24)
END: read demand_order_data (NULL) (03:00:25): 0.797 sec
START: read unassigned_inventory_data (NULL) (03:00:25)
END: read unassigned_inventory_data (NULL) (03:00:25): 0.156 sec
START: handle_negative_unassigned_inventory_quantity (NULL) (03:00:25)
END: handle_negative_unassigned_inventory_quantity (NULL) (03:00:25): 0.000 sec
START: read calendar_pattern_data (NULL) (03:00:25)
END: read calendar_pattern_data (NULL) (03:00:25): 0.000 sec
START: read resource_calendar_data (NULL) (03:00:25)
END: read resource_calendar_data (NULL) (03:00:25): 0.000 sec
START: read bfe_last_block_data (NULL) (03:00:25)
END: read bfe_last_block_data (NULL) (03:00:25): 0.000 sec
START: read block_formation_data (NULL) (03:00:25)
END: read block_formation_data (NULL) (03:00:25): 0.000 sec
START: read resource_lock_data (NULL) (03:00:25)
END: read resource_lock_data (NULL) (03:00:25): 0.000 sec
START: read priority_model_data (NULL) (03:00:25)
END: read priority_model_data (NULL) (03:00:25): 0.000 sec
START: read cao_parameters (NULL) (03:00:25)
END: read cao_parameters (NULL) (03:00:25): 0.000 sec
START: read dispatch_rule (NULL) (03:00:25)
END: read dispatch_rule (NULL) (03:00:25): 0.000 sec
START: Copying rampup calendars to subresources. (NULL) (03:00:25)
END: Copying rampup calendars to subresources. (NULL) (03:00:25): 0.000 sec
START: read supplier_part (NULL) (03:00:25)
END: read supplier_part (NULL) (03:00:25): 0.000 sec
START: read super_order_mapping_data (NULL) (03:00:25)
END: read super_order_mapping_data (NULL) (03:00:25): 0.000 sec
START: read unassigned_wip_data (NULL) (03:00:25)
END: read unassigned_wip_data (NULL) (03:00:25): 0.000 sec
START: read manufacturing_order_data (NULL) (03:00:25)
../fpDataIn/manufacturing_order_data, line 1: Non-unique mfg_order id 655507400_1 being given unique name 655507400_1-001.
../fpDataIn/manufacturing_order_data, line 2: Non-unique mfg_order id 659992400_1 being given unique name 659992400_1-001.
../fpDataIn/manufacturing_order_data, line 3: Non-unique mfg_order id 661137200_1 being given unique name 661137200_1-001.
END: read super_order_mapping_data (NULL) (03:00:25): 0.000 sec
START: read unassigned_wip_data (NULL) (03:00:25)
END: read unassigned_wip_data (NULL) (03:00:25): 0.000 sec
START: read manufacturing_order_data (NULL) (03:00:25)
This is a huge file with around 13 million records and we have to only load records starting with either START or END. The control files loads the data correctly but the Log_Index generated should be in a sequence of how the data is loaded whereas it is coming as all the START are sequenced and then for END the sequence is resetted to 1 and continued to last point. Any way we can have it in running sequence?
The data comes like this as given below, whereas the LOG_INDEX field should comes in a running sequence as it loaded from the file.
LOG_INDEX SUBSTR(FSL.LOG_DESCRIPTION,1,100)
---------- -----------------------------------------------------------------------------------------
1 START: Server startup (NULL) (02:36:36)
2 START: input_factory_model (NULL) (02:36:36)
3 START: read custom_extension_data (NULL) (02:36:36)
4 START: read client_profile_data (NULL) (02:36:36)
5 START: read user_authorization_data (NULL) (02:36:36)
6 START: read datafiles_security_data (NULL) (02:36:36)
7 START: read client_profile_commands_data (NULL) (02:36:36)
8 START: read start_time (NULL) (02:36:36)
9 START: read named_number_data (NULL) (02:36:36)
10 START: read unit_of_measure (NULL) (02:36:36)
11 START: read named_bucket_specifications_data (NULL) (02:36:36)
12 START: read attribute_alias (NULL) (02:36:36)
13 START: read part_buffer_style_data (NULL) (02:36:36)
14 START: read named_calendar (NULL) (02:36:36)
15 START: read part_number_data (NULL) (02:36:36)
16 START: read item_attribute_alias_data (NULL) (02:36:36)
17 START: read phantom_part_data (NULL) (02:36:36)
18 START: read demand_order_style_data (NULL) (02:36:36)
19 START: read maintenance_data (NULL) (02:36:36)
20 START: read sequence_dependent_setup_time (NULL) (02:36:36)
21 START: read processed_material_list (NULL) (02:36:36)
22 START: read setup_dispatch (NULL) (02:36:36)
23 START: read setup_sequence (NULL) (02:36:36)
24 START: Computing setup rings (NULL) (02:36:36)
25 START: read variable_capacity_bucket_size_data (NULL) (02:36:36)
26 START: read aggregate_resource_data (NULL) (02:36:36)
27 START: read vmi_bucket_size (NULL) (02:36:36)
28 START: read financial_reports_bucket_size (NULL) (02:36:36)
29 START: read resource_data (NULL) (02:36:36)
30 START: read resource_layout_data (NULL) (02:36:36)
31 START: read inventory_buffer_data (NULL) (02:36:36)
32 START: read transportation_time_data (NULL) (02:36:36)
33 START: read batch_size (NULL) (02:36:36)
34 START: read routing_data (NULL) (02:36:36)
1 END: read custom_extension_data (NULL) (02:36:36): 0.000 sec
2 END: read client_profile_data (NULL) (02:36:36): 0.000 sec
3 END: read user_authorization_data (NULL) (02:36:36): 0.000 sec
4 END: read datafiles_security_data (NULL) (02:36:36): 0.000 sec
5 END: read client_profile_commands_data (NULL) (02:36:36): 0.000 sec
6 END: read start_time (NULL) (02:36:36): 0.000 sec
7 END: read named_number_data (NULL) (02:36:36): 0.000 sec
8 END: read unit_of_measure (NULL) (02:36:36): 0.000 sec
9 END: read named_bucket_specifications_data (NULL) (02:36:36): 0.000 sec
10 END: read attribute_alias (NULL) (02:36:36): 0.000 sec
11 END: read part_buffer_style_data (NULL) (02:36:36): 0.000 sec
12 END: read named_calendar (NULL) (02:36:36): 0.000 sec
13 END: read part_number_data (NULL) (02:36:36): 0.297 sec
14 END: read item_attribute_alias_data (NULL) (02:36:36): 0.000 sec
15 END: read phantom_part_data (NULL) (02:36:36): 0.000 sec
16 END: read demand_order_style_data (NULL) (02:36:36): 0.000 sec
17 END: read maintenance_data (NULL) (02:36:36): 0.000 sec
18 END: read sequence_dependent_setup_time (NULL) (02:36:36): 0.000 sec
19 END: read processed_material_list (NULL) (02:36:36): 0.000 sec
20 END: read setup_dispatch (NULL) (02:36:36): 0.000 sec
21 END: read setup_sequence (NULL) (02:36:36): 0.000 sec
22 END: Computing setup rings (NULL) (02:36:36): 0.015 sec
23 END: read variable_capacity_bucket_size_data (NULL) (02:36:36): 0.000 sec
24 END: read aggregate_resource_data (NULL) (02:36:36): 0.000 sec
25 END: read vmi_bucket_size (NULL) (02:36:36): 0.000 sec
26 END: read financial_reports_bucket_size (NULL) (02:36:36): 0.000 sec
27 END: read resource_data (NULL) (02:36:36): 0.000 sec
28 END: read resource_layout_data (NULL) (02:36:36): 0.000 sec
29 END: read inventory_buffer_data (NULL) (02:36:36): 0.000 sec
30 END: read transportation_time_data (NULL) (02:36:36): 0.000 sec
31 END: read batch_size (NULL) (02:36:36): 0.000 sec
32 END: read routing_data (NULL) (02:36:36): 0.000 sec
33 END: read routing_group_data (NULL) (02:36:36): 0.000 sec
34 END: read super_routing_data (NULL) (02:36:36): 0.000 sec
|
|
|
|
| Re: Sequence while loading through SQL*Loader [message #152272 is a reply to message #152257] |
Thu, 22 December 2005 02:09  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Since both records are going into a single table, get rid of the WHEN clause and load every row through a single specification.
If there are rows in the file that you want to filter, you will have to get rid of them by piping the file though a filter before it is read by sqlldr.
eg. (in Unix)
/etc/mknod -p mypipe
sed -e '/^START/p' -e '/^END/p' -e 'd' filename.txt > mypipe 2>sed.err &
sqlldr uid/pwd ... DATA=mypipe
You could allocate the number out of an Oracle Sequence as well, but not with Direct-path load.
_____________
Ross Leishman
|
|
|
|
|
|
| Re: Sequence while loading through SQL*Loader [message #152404 is a reply to message #152277] |
Thu, 22 December 2005 22:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You could try putting a CHECK constraint on the table to enforce that the string begins with 'START:' or 'END:'. It is possible to set an option (EVALUATE CHECK_CONSTRAINTS) for Direct-path loads to run check constraints, so they should go through to the BAD file.
_____________
Ross Leishman
|
|
|
|
|
|
| Re: Sequence while loading through SQL*Loader [message #152566 is a reply to message #152407] |
Fri, 23 December 2005 23:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry, can't help you there.
If you were using Unix, you could pipe the log file through a filter to get rid of the error messages. But Windoze.... Nope.
You could completely re-engineer it using Externally Organized Tables:
1. Move the file to the database server, or to a disk mounted on the database server.
2. Create an Oracle directory pointing to the file location.
3. Create an EOT pointing to the file
4. CREATE TABLE new_table AS SELECT rownum AS counter, col FROM eot WHERE col LIKE 'START:%' OR col LIKE 'END:%'
5. Add the newly created table to your other table using Partition Exchange.
This idea is a bit bare-bones. You would have to flesh-out the detail. The reason I suggested CREATE .. AS SELECT .. is because it is not subject to rollback segments. If you INSERT /*+ APPEND */ then you'll probably blow your rollback segments.
I'd strongly consider running the file through a parser first to get rid of the unwanted lines. Providing you have the available disk space, it would run pretty quickly.
_____________
Ross Leishman
|
|
|
|
| Re: Sequence while loading through SQL*Loader [message #152638 is a reply to message #152257] |
Sun, 25 December 2005 22:49 |
himang
Messages: 282
Registered: March 2005
Location: Bangalore
|
Senior Member |
|
|
Thanks for the good information. We already had thought about EOT but can't go with it now. As you have advised we have got rid of error messages first before loading the file and hence solved our problem.
Thanks again for the solution.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
