CSV文件导入ORACLE, 用pl/sql developer 的Text Importer, 在哪里设置data类型输入

yulihua49

这就是我常说的，oracle的日期类型字段应该能预定义缺省格式。
否则太不方便了，各字段的格式不同你又怎么办呢？

1. 
   

复制代码

1. 
   
2. 
   
3. 
   
4. INSERT INTO CMS_PROD.CUT_PI_EXIT (streaming_session_id,data_version,txn_class,txn_revision,txn_ssn_a,
   
5. txn_ssn_b,ols_txn_type,settlement_date,application_validation_flags,host_name,multipart_txn_count,issuer_abort_reason,
   
6. acquirer_id,destination_participant_id,account_type,portioned_txn_flag,format_version,txn_date_time,
   
7. source_participant_id,device_id,sam_id,udsn,service_participant_id,
   
8. device_location,device_ssn,business_date,transaction_status,cd_set_version,reconciliation_date,ud_type,ud_subtype,device_home_depot,
   
9. mass_installation_id,iss_exception_proc_abrtd,iss_exception,iss_txn_reflection,cch_flags_txn_portion,
   
10. cch_flags_txn_summarised,cch_flags_txn_forwarded,cch_flags_txn_apportioned,cch_txn_good_for_summaries,cch_no_further_proc,
    
11. cch_exception,cch_txn_not_to_issuer,cch_txn_approved,exception_list,card_issuer_id,card_serial_number,
    
12. card_type,card_life_cycle_count,card_action_sequence_number,num_rides,remaining_rides,product_issuer_id,product_serial_number,
    
13. product_type,product_action_sequence_number,ptsn,invoice_printed,application_provider_id,application_serial_number,
    
14. application_personalise_cat,app_action_sequence_number,application_type,application_passenger_type,key_version,route_line_id,
    
15. route_direction,passenger_type,journey_type,current_location,number_of_passengers,number_of_zones,previous_operator,
    
16. trip_origin_location,trip_previous_location,lav_sam_id,lav_participant_id,lav_date,lav_txn_value,lav_remaining_rides,lav_ptsn,
    
17. lav_method_of_payment,data_is_valid,number_of_entries,portion_to_participant_id,portion_value,v_start_date_time,v_end_date_time,
    
18. v_duration,v_distance_origin,v_distance_destination,v_distance_distance,v_journey_period_journeys,v_journey_transfers,
    
19. v_route_routes,v_location_locations,restrictions_day,restrictions_time,v_period_duration,v_origin,v_destination,lav_pass_expiry_date_time,
    
20. pass_end_date_time,transaction_value,currency_indicator_fin_details,sales_tax,discount,tax_rate,tax_code,number_of_payments,
    
21. payment_method,payment_value,partial_transaction_value,partial_sales_tax,purse_remaining_value,lav_remaining_value,lav_amount_paid,
    
22. invoice_not_printed,entry_time,end_of_journey,delay_date,delay_station_location,delay_operation_mode,value_per_ride,first_use_activation,
    
23. total_journey_amount,card_captured,file_name_and_record,acc_time,changer) VALUES ( :1, :2, :3, :4, :5, :6, :7,TO_DATE(:8,'YYYYMMDD'),
    
24. :9, :10, :11, :12, :13, :14, :15, :16, :17,TO_DATE(:18,'YYYYMMDDHH24:MI:SS'), :19, :20, :21, :22, :23, :24, :25,TO_DATE(:26,'YYYYMMDD'),
    
25. :27, :28,TO_DATE(:29,'YYYYMMDD'), :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46,
    
26. :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69,
    
27. :70, :71, :72, :73, :74, :75, :76, :77, :78,TO_DATE(:79,'YYYYMMDD'), :80, :81, :82, :83, :84, :85, :86, :87,
    
28. TO_DATE(:88,'YYYYMMDDHH24:MI:SS'),TO_DATE(:89,'YYYYMMDDHH24:MI:SS'), :90, :91, :92, :93, :94, :95, :96, :97,
    
29. :98, :99, :100, :101, :102,TO_DATE(:103,'YYYYMMDDHH24:MI:SS'),
    
30. TO_DATE(:104,'YYYYMMDDHH24:MI:SS'), :105, :106, :107, :108, :109, :110, :111, :112, :113, :114, :115, :116, :117,
    
31. :118, :119,TO_DATE(:120,'YYYYMMDDHH24:MI:SS'), :121,TO_DATE(:122,'YYYYMMDD'), :123, :124, :125, :126, :127, :128, :129,
    
32. TO_DATE(:130,'YYYY-MM-DD HH24:MI:SS'), :131)
    

复制代码

yulihua49

yulihua49 发表于 2012-7-20 13:11
这就是我常说的，oracle的日期类型字段应该能预定义缺省格式。
否则太不方便了，各字段的格式不同你又怎么 ...

这语句日期格式不同且怪异。
别人给的，没办法，自己改。

不过csv格式可以自己配控制文件说明格式的。