in reply to Re: copying mysql table data to oracle table
in thread copying mysql table data to oracle table

Hi, that's OK but code breaks for dates. see the error below. any idea how to deal with dates here?

DBD::Oracle::st execute failed: ORA-01861: literal does not match form +at string (DBD ERROR: error possibly near <*> indicator at char 404 i +n 'insert into cloverleaf_ingestion(pk,File_name,Cl_file_size_in_byte +s,Cl_checksum,CL_created_date_time,Status,Mn_file_size_in_bytes,Mn_ch +ecksum,Failed_retries,Processed_date_time,ftp_acknowledgement,ohf_pro +cessing_status,ohf_processing_datetime,ohf_total_rows,ohf_failed_rows +,ohf_processed_rows,hdfs_processing_status,hdfs_processing_datetime,h +dfs_checksum,hdfs_file_size_in_bytes) values(:p1,:p2,:p3,:p4,:<*>p5,: +p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20 +)') [for Statement "insert into cloverleaf_ingestion(pk,File_name,Cl_ +file_size_in_bytes,Cl_checksum,CL_created_date_time,Status,Mn_file_si +ze_in_bytes,Mn_checksum,Failed_retries,Processed_date_time,ftp_acknow +ledgement,ohf_processing_status,ohf_processing_datetime,ohf_total_row +s,ohf_failed_rows,ohf_processed_rows,hdfs_processing_status,hdfs_proc +essing_datetime,hdfs_checksum,hdfs_file_size_in_bytes) values(?,?,?,? +,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" with ParamValues: :p1=3068, :p10=' +2017-08-09 00:39:22', :p11='Success Copy', :p12='PENDING', :p13=undef +, :p14=undef, :p15=undef, :p16=undef, :p17='PENDING', :p18=undef, :p1 +9=undef, :p2='/mnt/prod/ehr_person/ehr_person_07262017_155159.txt.gz' +, :p20=undef, :p3=23835527, :p4='6282802511cb67c8e6a4e55b3d477fef', : +p5='2017-08-08 06:34:36', :p6='PROCESSED', :p7=23835527, :p8='6282802 +511cb67c8e6a4e55b3d477fef', :p9=1] at sentryMySqlToOracleDump.pl line + 67

Replies are listed 'Best First'.
Re^3: copying mysql table data to oracle table
by huck (Prior) on Aug 24, 2017 at 13:11 UTC

    You might want the timestamp datatype instead

      Thanks for all help! I just got this fixed by setting as below:

      my $date_conv = $odbh->prepare("ALTER SESSION SET NLS_DATE_FORMAT = 'Y +YYY-MM-DD HH24:MI:SS'"); $date_conv->execute;