CSharma has asked for the wisdom of the Perl Monks concerning the following question:

Hi Everyone! I'm working on a script to copy/update data from mysql table to oracle table. Requirements are: i.) if record exists in oracle, then update it (if data is old) ii.) if record doesn't exist, simple insert it into oracle I'm facing issue in insertion and update into oracle. My compatible oracle table to mysql is as below: 

 CREATE TABLE cloverleaf_ingestion(
    pk NUMBER(20),
    File_name VARCHAR2(200),
    Cl_file_size_in_bytes NUMBER(22),
    Cl_checksum VARCHAR2(32),
    CL_created_date_time DATE, 
    Status VARCHAR2(20),
    Mn_file_size_in_bytes NUMBER(22),
    Mn_checksum VARCHAR2(32),
    Failed_retries NUMBER(11),
    Processed_date_time DATE,
    ftp_acknowledgement VARCHAR2(20),
    ohf_processing_status VARCHAR2(20),
    ohf_processing_datetime DATE,
    ohf_total_rows NUMBER(22),
    ohf_failed_rows NUMBER(22),
    ohf_processed_rows NUMBER(22),
    hdfs_processing_status VARCHAR2(20),
    hdfs_processing_datetime DATE,
    hdfs_checksum VARCHAR2(60),
    hdfs_file_size_in_bytes NUMBER(22),
    CONSTRAINT cloverleaf_ingestion_pk PRIMARY KEY (pk)
);

[download]

What I've done is: i.) below hash to check if data exists in oracle. ii.) checks hash and then inserts or updates the record in oracle (for now done only insert). 

sub getOracleRowHahs{
    
    my $odbh = DBI->connect($oracledsn, $oracleuser, $oraclepass) || d
+ie "Can't connect to oracle db:$DBI::errstr\n";
    my $osth = $odbh->prepare("select * from emp");
    $osth->execute();
    
    my $tableref;
    while(my $row = $osth->fetchrow_hashref()){
        no warnings;
        $tableref->{$row->{'PK'}} = $row;
}
    $odbh->disconnect();
    return $tableref;
}

[download]
    my $sdbh = DBI->connect($sqldsn, $sqluser, $sqlpass) || die "Can't
+ connect to mysql db:$DBI::errstr\n";
    my $odbh = DBI->connect($oracledsn, $oracleuser, $oraclepass) || d
+ie "Can't connect to oracle db:$DBI::errstr\n";

    my $oracleTableHash = &getOracleRowHahs();

    no warnings;
    my $query = "select * from $table";
    my $ssth = $sdbh->prepare($query)    ||    die "Error in prepare t
+o mysql: $DBI::errstr\n";;
    $ssth->execute()    ||    die "Error in execute to mysql: $DBI::er
+rstr\n";;
    while(my @row = $ssth->fetchrow_array()){
#        say join("\t",@row);
        if(!(exists($oracleTableHash->{$row[0]}))){
            my $columns = join(",",@row);
            say $columns;
            my $insertquery = "insert into $table values(?,?,?,?,?,?,?
+,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            #say $insertquery;
            #exit();
            my $osth = $odbh->prepare($insertquery) || die "Error in p
+repare: $DBI::errstr";
            $osth->execute(@row);
        }
    }


    $sdbh->disconnect()    ||    die "Error in disconnect to mysql: $D
+BI::errstr\n";
    $odbh->disconnect()    ||    die "Error in disconnect to oracle: $
+DBI::errstr\n";

[download]

could someone please help me out?

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

    A few comments

    I would change "getOracleRowHahs" to 

    sub getOracleRowHash{
    
    my $odbh = shift; 
    my $osth = $odbh->prepare("select pk from emp");
    $osth->execute();
    
    my $tableref;
    while(my $row = $osth->fetchrow_hashref()){
        no warnings;
        $tableref->{$row->{'pk'}} = $row;
}
    return $tableref;
}

    [download]
    and the call to 
    my $oracleTableHash = getOracleRowHash($odbh);

    [download]
    No need to connect twice and the use of the & is no longer suggested. Just request the one var since that is all you want now. Changing it to a array fetch may speed it up significantly, but that is left as a exercise. Also note the case change to follow your table definition.

    In the while(my @row = $ssth->fetchrow_array()){ loop you re-prepare your insert every time. That is going to cost you. Also after a DBA changed the native variable order on me i always include variable names for all but the most trivial tasks. 

    my @vnames=qw/ 
 pk
    File_name 
    Cl_file_size_in_bytes
    Cl_checksum
    CL_created_date_time
    Status 
    Mn_file_size_in_bytes
    Mn_checksum 
    Failed_retries 
    Processed_date_time
    ftp_acknowledgement
    ohf_processing_status 
    ohf_processing_datetime
    ohf_total_rows
    ohf_failed_rows 
    ohf_processed_rows 
    hdfs_processing_status
    hdfs_processing_datetime 
    hdfs_checksum
    hdfs_file_size_in_bytes
/; 
    my $vlist=join(',',@vnames); 
    my $table='cloverleaf_ingestion'; 

    my @qs=map{'?'} @vnames; 
    my $q=join(',',@q); 

    my $query = "select $vlist from $table";
    my $ssth = $sdbh->prepare($query)    ||    die "Error in prepare t
+o mysql: $DBI::errstr\n";;
    my $insertquery = "insert into $table($vnames) values($q)";
    my $osth = $odbh->prepare($insertquery) || die "Error in prepare: 
+$DBI::errstr";
    $osth->begin_work; 

    $ssth->execute()    ||    die "Error in execute to mysql: $DBI::er
+rstr\n";;
    my $commit_ct      =500; 
    my $inserts=0; 

    while(my @row = $ssth->fetchrow_array()){
        if(!(exists($oracleTableHash->{$row[0]}))){
            $inserts++; 
            if ($inserts>$commit_ct){ 
                $inserts=1;
                $osth->commit or die 'something'; 
                $osth->begin_work; 
            }   
            $osth->execute(@row);
        }
    }
    $osth->commit or die 'something';

    [download]
    Note too the begin_work/commit calls that will speed up the insert a WHOLE BUNCH!!! The commit_ct test is to limit the size of the internal "holding" list that begin_work creates;

[reply]
[d/l]
[select]

      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

      [download]
[reply]
[d/l]

        You might want the timestamp datatype instead

[reply]
Re: copying mysql table data to oracle table
by thanos1983 (Parson) on Aug 24, 2017 at 09:42 UTC

    Hello CSharma,

    On MySQL there is a internal function INSERT ... ON DUPLICATE KEY UPDATE Syntax give it a try.

    Update: I do not have an oracle table to test if the same function works so I can not verify it but I hope it does.

    Let us know if this is working for you. Looking for your update, BR.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
[reply]
[d/l]

        Hello soonix,

        You are right, I should have Googled it.

        Thanks for the time and effort, BR.

        Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
Re: copying mysql table data to oracle table
by chacham (Prior) on Aug 24, 2017 at 11:54 UTC

    The "upsert" statement in Oracle is call MERGE

    Please do not use a variable for the table name. That is some dangerous dynamic sql there.

[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom