darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
1.01k stars 342 forks source link

[ORACLE] Export of data created as SYS.DATA causes migration failure #1571

Open chetank-yb opened 1 year ago

chetank-yb commented 1 year ago

Schema Used

CREATE TYPE pet_t IS OBJECT ( 
   tag_no   INTEGER 
  ,NAME     VARCHAR2 (60) 
  ,breed    VARCHAR2 (100) 
);

CREATE TYPE zoo_t IS TABLE OF pet_t;

CREATE TABLE wild_side (  
   ID NUMBER PRIMARY KEY  
 , DATA SYS.ANYDATA  
); 

DECLARE  
   my_cat    pet_t  
          := pet_t (50, 'Sister', 'Siamese');  
   my_bird   pet_t  
      := pet_t (100  
               ,'Mercury'  
               ,'African Grey Parrot'  
               );  
   my_pets   zoo_t := zoo_t (my_cat, my_bird);  
BEGIN  
   INSERT INTO wild_side  
        VALUES (1  
               ,SYS.ANYDATA.convertnumber  
                                         (5));  

   INSERT INTO wild_side  
        VALUES (3  
               ,SYS.ANYDATA.convertvarchar2  
                      ('Pretty crazy stuff!'));  

   INSERT INTO wild_side  
        VALUES (5  
               ,SYS.ANYDATA.convertdate  
                                    (SYSDATE));  

   INSERT INTO wild_side  
        VALUES (2  
               ,SYS.ANYDATA.convertobject  
                                    (my_bird));  

   INSERT INTO wild_side  
        VALUES (7  
               ,SYS.ANYDATA.convertcollection  
                                    (my_pets));  

   COMMIT;  
END; 

Expected : The table wild_side must be migrated Actual : The table wild_side is not migrated