search

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.03k stars 341 forks source link

Error when obtaining data from Oracle #1392

Closed mperdikeas closed 2 years ago

mperdikeas commented 2 years ago

Ora2Pg v23.1

$ LD_LIBRARY_PATH=~/software/oracle-instant-client/instantclient_21_3 ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf FATAL: ORA-00936: missing expression (DBD ERROR: error possibly near <> indicator at char 311 in 'SELECT DISTINCT A.INDEX_NAME,A.COLUMN_NAME,B.UNIQUENESS,A.COLUMN_POSITION,B.INDEX_TYPE,B.TABLE_TYPE,B.GENERATED,B.JOIN_INDEX,A.TABLE_NAME,A.INDEX_OWNER,B.TABLESPACE_NAME,B.ITYP_NAME,B.PARAMETERS,A.DESCEND FROM DBA_IND_COLUMNS A JOIN DBA_INDEXES B ON (B.INDEX_NAME=A.INDEX_NAME AND B.OWNER=A.INDEX_OWNER) WHERE <>AND A.INDEX_OWNER NOT IN ('OE','HR','SCOTT','ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','DIP','PM','SH','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000','FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT','SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200','DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF','AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS','OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN','AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED','DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER') AND B.TEMPORARY = 'N' AND ( NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p1) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p2) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p3) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p4) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p5) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p6) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p7) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p8) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p9) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p10) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p11) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p12) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p13) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p14) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p15) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p16) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p17) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p18) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p19) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p20) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p21) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p22) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p23) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p24) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p25) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p26) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p27) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p28) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p29) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p30) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p31) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p32) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p33) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p34) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p35) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p36) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p37) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p38) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p39) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p40) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p41) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p42) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p43) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p44) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p45) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p46) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p47) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p48) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p49) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p50) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p51) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p52) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p53) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p54) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p55) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p56) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p57) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p58) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p59) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p60) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p61) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p62) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p63) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p64) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p65) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p66) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p67) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p68) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p69) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p70) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p71) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p72) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p73) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p74) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p75) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p76) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p77) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p78) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p79) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p80) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p81) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p82) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p83) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p84) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p85) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p86) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p87) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p88) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p89) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p90) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p91) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p92) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p93) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p94) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p95) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p96) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p97) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p98) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p99) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p100) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p101) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p102) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p103) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p104) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p105) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p106) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p107) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p108) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p109) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p110)) AND (A.INDEX_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, TABLE_NAME FROM DBA_OBJECT_TABLES) AND (A.INDEX_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, MVIEW_NAME FROM DBA_MVIEWS UNION ALL SELECT LOG_OWNER, LOG_TABLE FROM DBA_MVIEW_LOGS) ORDER BY A.COLUMN_POSITION ') Aborting export...

darold commented 2 years ago

Please upgrade to latest development code, this have been fixed yesterday.