How to convert views using oracle objects

[Aravind Babu Miryala]

Hi,

I have tables and views defined in oracle schema as given below how should I migrate them to MySQL. EMPPROFILE view defined below is used in XSQL(Servlet provided by oracle can also be used with MySQL) to insert records to database.


CREATE TABLE EMPACCESS(
ID VARCHAR2(255) NOT NULL,
LISTTYPE VARCHAR2(7) NOT NULL,
ACTIVE VARCHAR2(7) NOT NULL);


ALTER TABLE EMPACCESS ADD CONSTRAINT EMP_PK PRIMARY KEY (ID) ;
ALTER TABLE EMPACCESS ADD CONSTRAINT EMP_ID_CK CHECK(ID LIKE 'EMP%');
ALTER TABLE EMPACCESS ADD CONSTRAINT EMP_LISTTYPE_CK CHECK(LISTTYPE IN('Black', 'White'));
ALTER TABLE EMPACCESS ADD CONSTRAINT EMP_ACTIVE_CK CHECK(ACTIVE IN('True', 'False'));


CREATE TABLE EMPLOCATION(
ID_REF VARCHAR2(255) NOT NULL,
LOCATIONNO VARCHAR2(255) NOT NULL);

ALTER TABLE EMPLOCATION ADD CONSTRAINT EMPLOCATION_PK PRIMARY KEY (ID_REF, LOCATIONNO);
ALTER TABLE EMPLOCATION ADD CONSTRAINT EMPLOCATION_IDREF_FK FOREIGN KEY (ID_REF) REFERENCES EMPACCESS(ID);


CREATE OR REPLACE TYPE LOCATION_T AS OBJECT(
ID_REF VARCHAR2(255),
LOCATIONNO VARCHAR2(255));
/

CREATE OR REPLACE TYPE LOCATION_LIST AS TABLE OF LOCATION_T;
/


CREATE OR REPLACE TYPE EMPPROFILE_T AS OBJECT(
ID VARCHAR2(255),
LISTTYPE VARCHAR2(7),
ACTIVE VARCHAR2(7),
LOCATION LOCATION_LIST);
/

CREATE OR REPLACE VIEW EMPPROFILE OF EMPPROFILE_T
WITH OBJECT OID(ID)
AS SELECT PID,
LISTTYPE,
ACTIVE,
CAST(
MULTISET(
SELECT ID_REF, LOCATIONNO
FROM EMPLOCATION
WHERE EMPACCESS.ID = EMPLOCATION.ID_REF)
AS LOCATION_LIST) LOCATION
FROM EMPACCESS;