Hello Jerry,
I don't know if can use views to connect directly MySQL to Oracle.
Nevertheless, you can create a batch which export data from Oracle and import in MySQL.
Scripts I use to replicate data through the two DB :
1/ Export Oracle (exportQuery.txt)
________________
whenever oserror exit oscode
whenever sqlerror exit sql.sqlcode
set term off
set newpage 0
set space 0
set linesize 1000
set pagesize 0
set echo off
set feedback off
set heading off
set COLSEP ' '
set ver off
spool myTable.txt
SELECT field1, field2,TO_CHAR(field2,'YY/MM/DD')...
FROM MYSHEMA.MYTABLE
ORDER BY PRYMAIREKEY;
/** for ex. here, third argument is a date and must be casted **/
spool off
EXIT
2/ Import MySQL (importQuery.sql)
_______________
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO' */;
LOAD DATA INFILE 'myTable.txt' REPLACE INTO TABLE SCHEMA.MYTABLE;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
3/ Batch .BAT to run on Windows(containing import and export)
_____________________________________________________________
:begin
@ECHO off
cls
ECHO.
ECHO.
ECHO *****************
ECHO * Export Oracle *
ECHO *****************
ECHO.
sqlplus -s login/password@schema @exportQuery
ECHO.
ECHO RETURN CODE : %ERRORLEVEL%
ECHO.
ECHO.
ECHO *****************
ECHO * Import MySQL *
ECHO *****************
ECHO.
mysql mytable < importQuery.sql -u root -ppassword
ECHO.
ECHO RETURN CODE : %ERRORLEVEL%
ECHO.
ECHO *** END OF BATCH ***
Try to use portable data types on Oracle and MySQL.
http://home.fnal.gov/~dbox/SQL_API_Portability.html