AnySQL.net English

    Create the demo table and materialized view log in Oracle:

CREATE TABLE T_OBJECTS AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM < 1;
ALTER TABLE T_OBJECTS MODIFY OBJECT_ID NOT NULL;
ALTER TABLE T_OBJECTS ADD PRIMARY KEY (OBJECT_ID);
CREATE MATERIALIZED VIEW LOG ON T_OBJECTS WITH PRIMARY KEY, SEQUENCE;

    Create the relative demo table in MySQL:

CREATE TABLE t_objects (
  OBJECT_ID decimal(10,0) NOT NULL,  OWNER varchar(30) ,
  OBJECT_NAME varchar(128) ,   SUBOBJECT_NAME varchar(30) ,
  DATA_OBJECT_ID decimal(10,0) ,  OBJECT_TYPE varchar(18) ,
  CREATED datetime ,   LAST_DDL_TIME datetime ,
  TIMESTAMP varchar(19) ,  STATUS varchar(7) ,
  TEMPORARY varchar(1) ,  GENERATED varchar(1) ,
  SECONDARY varchar(1) ,  PRIMARY KEY  (OBJECT_ID)
);

    Create a configuration file (demo.conf):

# SOURCE  # PKEY      # MVIEW Log       # TARGET
T_OBJECTS # OBJECT_ID # MLOG$_T_OBJECTS # T_OBJECGS

    Run refresh_mysql.pl to start replication (I run it on windows with ActivePerl):

perl refresh_mysql.pl -s username#password#Oracle:tnsname -t username#password#MySQL:database:hostip -c demo.conf

    Now you can run some DML statement on t_objects from Oracle database, and then check whether the changes are applied to MySQL by this script.

    refresh_mysql.pl is originally designed to replicate changed data from Oracle to other databases. But now I find that it can be used to copy or replicate data between different databases such as from MySQL to Oracle. We just need to maintain a table with the same structure as the materialized view log table in Oracle, then manually populate the log rows (data copy), or by triggers (data replication) if the source database support it. So powerful it is for this simple Perl script.

    The log table must contains all the primary key columns and two extra columns ("SEQUENCE$$" and "DMLTYPE$$"). I have test it in the MySQL that the dollar character can appear in column name without any problem. The sample create syntax looks as following :

create table temp_table
{
    SEQUENCE$$    INT NOT NULL PRIMARY KEY,
    DMLTYPE$$     VARCHAR(1) DEFAULT 'I',
    pkey_col1     column_type,
    pkey_col...   column_type
);

    Create a configuration file for it, it will work for you. For multiple database support, I have to remove the double quote in the column name, because it will be treated as an constant string in MySQL.

    After some changes of the code, I have copy some data from MySQL to Oracle database. Because I don't know how to create trigger in MySQL, so I did not test data replication to Oracle, but I believe that it will work fine for tables with small data volume.

    The latest Perl DBI support array operation, use an array as a bind value when operating with database. From the database layer, it's not a new feature, like Oracle Pro*C & OCI interface. After use of the array interface of Perl, I have suggestion to add a new API interface to Perl DBI. Usually the Perl DBI will return the result based on row as following:

{
  ROW1  {col1, col2, col3, col4},
  ROW2  {col1, col2, col3, col4},
  .....
  ROWn  {col1, col2, col3, col4}
}

    But when binding, we need to bind a column based array :

$sth->bind_param_array( COL1={row1, row2, ... rown});
$sth->bind_param_array( COL2={row1, row2, ... rown});
.....
$sth->bind_param_array( COLn={row1, row2, ... rown});

    So we need do convertion in program, from the database interface layer, when using array fetch, the return result is actually column based, as following :

COL1={ROW1, ROW2, ..., ROWn}
COL2={ROW1, ROW2, ..., ROWn}
......
COLn={ROW1, ROW2, ..., ROWn}

    Perl do the column-row conversion for us, and we do the row-column conversion for array binding. Why not introduce an interface to return column based array in DBI for performance improve?