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?

In reply to copying mysql table data to oracle table by CSharma

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.