ora2pg incompatible with Oracle 8i 8.1.7.4.0 #1914
Description
When using ora2pg against an Oracle 8.1.7.4.0 database, the tool fails with the following error:
FATAL: ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <> indicator at char 201 in ' SELECT DISTINCT A.COLUMN_NAME,A.CONSTRAINT_NAME,A.OWNER,A.POSITION,B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE,B.DEFERRABLE,B.DEFERRED,B.GENE RATED,B.TABLE_NAME,B.OWNER,'' AS INDEX_NAME FROM ALL_CONS_COLUMNS A <>JOIN ALL_CONSTRAINTS B ON...
This is due to use of ANSI SQL JOIN ... ON syntax in Oracle.pm line 1381 and 1382 (https://github.com/darold/ora2pg/blob/8f89999fcaf5087eaa02db88bc8a929ef2991384/lib/Ora2Pg/Oracle.pm#L1381C1-L1382C136) in version 25, which is not supported in Oracle 8. Oracle 8 requires comma-seperated table lists and WHERE clauses instead.
The fix in my local version changed the code as a quick get around, but I am not a Perl programmer and this was just a quick work around.
my $sql = qq{SELECT DISTINCT A.COLUMN_NAME,A.CONSTRAINT_NAME,A.OWNER,A.POSITION,B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE,B.DEFERRABLE,B.DEFERRED,B.GENERATED,B.TABLE_NAME,B.OWNER,$indexname
FROM $self->{prefix}_CONS_COLUMNS A, $self->{prefix}_CONSTRAINTS B};
if ($owner) {
$sql .= " WHERE A.OWNER = '$owner'";
} else {
$sql .= " WHERE A.OWNER NOT IN ('" . join("','", @{$self->{sysusers}}) . "')";
}
**$sql .= "AND B.CONSTRAINT_NAME = A.CONSTRAINT_NAME AND B.OWNER = A.OWNER";**
$sql .= " AND B.CONSTRAINT_TYPE IN $cons_types";
$sql .= " AND B.TABLE_NAME='$table'" if ($table);
$sql .= " AND B.STATUS='ENABLED' ";
Suggested Fix
use of existing database flag if ($self->{db_version} !~ /Release 8/) to support an oracle 8 valid sql statement.
P.S. Really appreciate this tool - it's reduced a lot of the effort to export data from a legacy database. Thanks for all the hard work maintaining it!