On Fri, 2005-09-02 at 12:29 -0700, Josh Berkus wrote:
> > still trying to hold on to my fantasy that I can hack Postgres (and
> > contrib/ora2pg) into submission.
>
> I'm happy to work with you on ora2pg
Cool.
It looks like I should have referred to contrib/oracle, not
contrib/ora2pg, but you got my point.
The latest version I found of ora2pg is at
http://www.samse.fr/GPL/ora2pg/ora2pg-3.3.tar.gz This seems to be more
recent than the version at contrib/oracle. For example, this newer
version has tablespace support. Given this as a starting point, I've
made the attached changes. Mostly I've added a few new config options,
but I also made a correction to the existing EXCLUDE option, and I
corrected a couple spelling/English errors along the way.
A big thing that's lacking is conversion for stored procedures and
functions. My initial approach to this was to use Perl to post-process
the PL/SQL code dumped by the export, making it look more like proper
Pl/pgSQL (e.g. VARCHAR2->VARCHAR). I'm no Perl hacker, and when I came
across significant PL/SQL <--> PL/pgSQL differences (e.g. PL/pgSQL
exception == rollback), I added to my approach the idea of hacking
PL/pgSQL to make it look more like PL/SQL. Attacking the problem from
both ends like this, I imagined that Nirvana would be reached somewhere
in the middle.
The beginning of my Perl-based attempt to convert PL/SQL into PL/pgSQL
is a pretty simple stand-alone script. I can send it if you like, but
I'm a Perl newbie, so you can probably do much better. My attempts to
make PL/pgSQL look like PL/SQL have been posted to -hackers and -patches
over the last couple months.
diff -c ora2pg_3.3/ora2pg.conf ora2pg/ora2pg.conf
*** ora2pg_3.3/ora2pg.conf 2004-12-24 16:05:40.000000000 +0000
--- ora2pg/ora2pg.conf 2005-09-02 20:38:48.900376220 +0000
***************
*** 56,61 ****
--- 56,68 ----
# Value must be a list of table name separated by space.
#EXCLUDE OTHER_TABLES
+ # Set whether to include invalid functions, procedures, and packages.
+ # Under Oracle's on-the-fly invalidation/recompilation model there
+ # may be any number of objects that have status of 'INVALID' but that
+ # are actually viable.
+ INCLUDE_INVALID 1
+
+
# Display table indice and exit program (do not perform any export)
SHOWTABLEID 0
***************
*** 139,148 ****
# Constraints will be checked at the end of each transaction.
DEFER_FKEY 0
! # If set to 1 replace portable numeric type into PostgreSQL internal type.
# Oracle data type NUMBER(p,s) is approximatively converted to smallint,
# integer, bigint, real and float PostgreSQL data type. If you have monetary
# fields you should preserve the numeric(p,s) PostgreSQL data type if you need
! # very good precision. NUMBER without precision are set to float.
! PG_NUMERIC_TYPE 1
--- 146,171 ----
# Constraints will be checked at the end of each transaction.
DEFER_FKEY 0
! # If set to 1 replace portable numeric type with PostgreSQL internal type.
# Oracle data type NUMBER(p,s) is approximatively converted to smallint,
# integer, bigint, real and float PostgreSQL data type. If you have monetary
# fields you should preserve the numeric(p,s) PostgreSQL data type if you need
! # very good precision (see PG_INTEGER_TYPE). NUMBER without precision are set to
! # float.
! PG_NUMERIC_TYPE 0
!
! # If set to 1 replace portable numeric type with PostgreSQL internal type,
! # for integers only. This behaves as PG_NUMERIC_TYPE with respect to
! # Oracle data type NUMBER(p), but preserves exact arithmetic on NUMBER(p,s)
! # columns by converting to PostgreSQL numeric(p,s). NUMBER without precision
! # maps to "numeric" without precision.
! PG_INTEGER_TYPE 1
!
! # If set to 1 map Oracle's DATE type to PostgreSQL DATE type. Oracle DATE type
! # can contain time information, so PostgreSQL "timestamp" should, in general, be
! # used to hold Oracle DATEs. However, Oracle also supports TIMESTAMP. Setting
! # PG_DATE_TYPE indicates that Oracle TIMESTAMPs are the only incoming date columns
! # with a time portion that needs to be preserved, and that incoming Oracle DATEs
! # effectively contain only a date portion.
! PG_DATE_TYPE 1
diff -c ora2pg_3.3/ora2pg.pl ora2pg/ora2pg.pl
*** ora2pg_3.3/ora2pg.pl 2004-12-24 16:05:40.000000000 +0000
--- ora2pg/ora2pg.pl 2005-07-07 18:01:53.000000000 +0000
***************
*** 40,45 ****
--- 40,46 ----
#tables => [EMAIL PROTECTED]'TABLES'}},
tables => $Config{'TABLES'},
exclude => $Config{'EXCLUDE'},
+ include_invalid => $Config{'INCLUDE_INVALID'} || 0,
showtableid => $Config{'SHOWTABLEID'} || 0,
min => $Config{'MIN'} || 0,
max => $Config{'MAX'} || 0,
***************
*** 56,66 ****
fkey_deferrable => $Config{'FKEY_DEFERRABLE'} || 0,
defer_fkey => $Config{'DEFER_FKEY'} || 0,
pg_numeric_type => $Config{'PG_NUMERIC_TYPE'} || 0,
);
exit 0 if ($Config{'SHOWTABLEID'});
! # Mofify export structure if required
if ($Config{'TYPE'} =~ /^(DATA|COPY)$/) {
for my $t (keys %{$Config{'MODIFY_STRUCT'}}) {
$schema->modify_struct($t, @{$Config{'MODIFY_STRUCT'}{$t}});
--- 57,69 ----
fkey_deferrable => $Config{'FKEY_DEFERRABLE'} || 0,
defer_fkey => $Config{'DEFER_FKEY'} || 0,
pg_numeric_type => $Config{'PG_NUMERIC_TYPE'} || 0,
+ pg_integer_type => $Config{'PG_INTEGER_TYPE'} || 0,
+ pg_date_type => $Config{'PG_DATE_TYPE'} || 0,
);
exit 0 if ($Config{'SHOWTABLEID'});
! # Modify export structure if required
if ($Config{'TYPE'} =~ /^(DATA|COPY)$/) {
for my $t (keys %{$Config{'MODIFY_STRUCT'}}) {
$schema->modify_struct($t, @{$Config{'MODIFY_STRUCT'}{$t}});
***************
*** 123,129 ****
$Config{"skip_\L$_\E"} = 1;
}
}
! } elsif (!grep(/^$var$/i, 'TABLES', 'MODIFY_STRUCT', 'REPLACE_TABLES', 'REPLACE_COLS', 'WHERE')) {
$Config{"\U$var\E"} = $val;
} elsif ( (uc($var) eq 'TABLES') || (uc($var) eq 'EXCLUDE') ) {
push(@{$Config{"\U$var\E"}}, split(/\s+/, $val) );
--- 126,132 ----
$Config{"skip_\L$_\E"} = 1;
}
}
! } elsif (!grep(/^$var$/i, 'TABLES', 'MODIFY_STRUCT', 'REPLACE_TABLES', 'REPLACE_COLS', 'WHERE', 'EXCLUDE')) {
$Config{"\U$var\E"} = $val;
} elsif ( (uc($var) eq 'TABLES') || (uc($var) eq 'EXCLUDE') ) {
push(@{$Config{"\U$var\E"}}, split(/\s+/, $val) );
diff -c ora2pg_3.3/Ora2Pg.pm ora2pg/Ora2Pg.pm
*** ora2pg_3.3/Ora2Pg.pm 2005-02-22 17:21:41.000000000 +0000
--- ora2pg/Ora2Pg.pm 2005-07-07 20:46:54.000000000 +0000
***************
*** 271,302 ****
Supported options are:
! - datasource : DBD datasource (required)
! - user : DBD user (optional with public access)
! - password : DBD password (optional with public access)
! - schema : Oracle internal schema to extract
! - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE,
! TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE,TABLESPACE
! - debug : Print the current state of the parsing
! - export_schema : Export Oracle schema to PostgreSQL 7.3 schema
! - tables : Extract only the given tables (arrayref) and set the extracting order
! - exclude : Exclude the given tables from extract (arrayref)
! - showtableid : Display only the table indice during extraction
! - min : Indice to begin extraction. Default to 0
! - max : Indice to end extraction. Default to 0 mean no limits
! - data_limit : Number max of tuples to return during data extraction (default 0 no limit)
! - case_sensitive: Allow to preserve Oracle object name as they are written. Default is not.
! - skip_fkeys : Skip foreign key constraints extraction. Default to 0 (extraction)
! - skip_pkeys : Skip primary keys extraction. Default to 0 (extraction)
! - skip_ukeys : Skip unique column constraints extraction. Default to 0 (extraction)
! - skip_indices : Skip all other index types extraction. Default to 0 (extraction)
! - skip_checks : Skip checks constraints extraction. Default to 0 (extraction)
! - bzip2 : Path to the Bzip2 program to compress data export. Default /usr/bin/bzip2
! - gen_user_pwd : When set to 1 replace default password 'change_my_secret' with a random string.
- fkey_deferrable: Force foreign key constraints to be exported as deferrable. Default 0: asis.
! - defer_fkey : Force all foreign key constraints to be deferred during data import. Default 0: asis.
! - pg_numeric_type: Convert Oracle NUMBER data type to internal PostgreSQL data type instead of use
of the slow numeric(p,s) data type
Attempt that this list should grow a little more because all initialization is
--- 271,307 ----
Supported options are:
! - datasource : DBD datasource (required)
! - user : DBD user (optional with public access)
! - password : DBD password (optional with public access)
! - schema : Oracle internal schema to extract
! - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE,
! TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE,TABLESPACE
! - debug : Print the current state of the parsing
! - export_schema : Export Oracle schema to PostgreSQL schema
! - tables : Extract only the given tables (arrayref) and set the extracting order
! - exclude : Exclude the given tables from extract (arrayref)
! - include_invalid: Include invalid functions/procedures/packages
! - showtableid : Display only the table indice during extraction
! - min : Indice to begin extraction. Default to 0
! - max : Indice to end extraction. Default to 0 mean no limits
! - data_limit : Number max of tuples to return during data extraction (default 0 no limit)
! - case_sensitive : Allow to preserve Oracle object name as they are written. Default is not.
! - skip_fkeys : Skip foreign key constraints extraction. Default to 0 (extraction)
! - skip_pkeys : Skip primary keys extraction. Default to 0 (extraction)
! - skip_ukeys : Skip unique column constraints extraction. Default to 0 (extraction)
! - skip_indices : Skip all other index types extraction. Default to 0 (extraction)
! - skip_checks : Skip checks constraints extraction. Default to 0 (extraction)
! - bzip2 : Path to the Bzip2 program to compress data export. Default /usr/bin/bzip2
! - gen_user_pwd : When set to 1 replace default password 'change_my_secret' with a random string.
- fkey_deferrable: Force foreign key constraints to be exported as deferrable. Default 0: asis.
! - defer_fkey : Force all foreign key constraints to be deferred during data import. Default 0: asis.
! - pg_numeric_type: Convert Oracle NUMBER data type to internal PostgreSQL data type instead of using
of the slow numeric(p,s) data type
+ - pg_integer_type: Convert Oracle NUMBER data type to internal PostgreSQL data type instead of using
+ of the slow numeric(p) data type -- integers only
+ - pg_date_type : Convert Oracle DATE data type to PostgreSQL DATE data type instead of using
+ the TIMESTAMP data type for all dates
Attempt that this list should grow a little more because all initialization is
***************
*** 529,534 ****
--- 534,541 ----
$self->{max} = $options{max} || 0;
+ $self->{include_invalid} = $options{include_invalid} || 0;
+
$self->{showtableid} = $options{showtableid} || 0;
$self->{dbh}->{LongReadLen} = 0;
***************
*** 560,565 ****
--- 567,574 ----
$self->{fkey_deferrable} = $options{fkey_deferrable} || 0;
$self->{defer_fkey} = $options{defer_fkey} || 0;
$self->{pg_numeric_type} = $options{pg_numeric_type} || 0;
+ $self->{pg_integer_type} = $options{pg_integer_type} || 0;
+ $self->{pg_date_type} = $options{pg_date_type} || 0;
$self->{type} = $options{type} || 'TABLE';
***************
*** 909,915 ****
$sql_header .= "--\n";
$sql_header .= "-- This program is free software; you can redistribute it and/or modify it under\n";
$sql_header .= "-- the same terms as Perl itself.\n\n";
! if ($self->{type} ne 'COPY' || $self->{defer_fkey}) {
$sql_header .= "BEGIN TRANSACTION;\n\n";
}
--- 918,924 ----
$sql_header .= "--\n";
$sql_header .= "-- This program is free software; you can redistribute it and/or modify it under\n";
$sql_header .= "-- the same terms as Perl itself.\n\n";
! if ($self->{type} ne 'COPY' && $self->{type} ne 'FUNCTION' && $self->{type} ne 'PROCEDURE' || $self->{defer_fkey}) {
$sql_header .= "BEGIN TRANSACTION;\n\n";
}
***************
*** 1145,1156 ****
map { s/^.* out //is } @argu;
map { $_ = $self->_sql_type(uc($_)) } @argu;
$self->{functions}{$fct} =~ /return ([^\s]*) is/is;
! $self->{functions}{$fct} = "-- Oracle function declaration, please edit to match PostgreSQL syntax.\n$self->{functions}{$fct}";
! if (!$self->{case_sensitive}) {
! $sql_output .= "-- PostgreSQL possible function declaration, please edit to match your needs.\nCREATE FUNCTION \L$fct\E(" . join(',', @argu) . ") RETURNS " . $self->_sql_type(uc($1)) . " AS '\n$self->{functions}{$fct}\n' LANGUAGE 'sql'\n\n";
! } else {
! $sql_output .= "-- PostgreSQL possible function declaration, please edit to match your needs.\nCREATE FUNCTION $fct(" . join(',', @argu) . ") RETURNS " . $self->_sql_type(uc($1)) . " AS '\n$self->{functions}{$fct}\n' LANGUAGE 'sql'\n\n";
! }
}
if (!$sql_output) {
--- 1154,1160 ----
map { s/^.* out //is } @argu;
map { $_ = $self->_sql_type(uc($_)) } @argu;
$self->{functions}{$fct} =~ /return ([^\s]*) is/is;
! $sql_output .= "\nCREATE\n $self->{functions}{$fct}";
}
if (!$sql_output) {
***************
*** 1681,1687 ****
'CHAR' => 'char',
'NCHAR' => 'char',
# VARCHAR types the limit is 2000 bytes in Oracle 7 and 4000 in Oracle 8.
! # PG varchar type has max length iset to 8104 so it should match all needs
'VARCHAR' => 'varchar',
'NVARCHAR' => 'varchar',
'VARCHAR2' => 'varchar',
--- 1685,1691 ----
'CHAR' => 'char',
'NCHAR' => 'char',
# VARCHAR types the limit is 2000 bytes in Oracle 7 and 4000 in Oracle 8.
! # PG varchar type has max length set to 8104 so it should match all needs
'VARCHAR' => 'varchar',
'NVARCHAR' => 'varchar',
'VARCHAR2' => 'varchar',
***************
*** 1689,1694 ****
--- 1693,1699 ----
# The DATE data type is used to store the date and time information.
# Pg type timestamp should match all needs
'DATE' => 'timestamp',
+ 'TIMESTAMP(6)' => 'timestamp',
# Type LONG is like VARCHAR2 but with up to 2Gb.
# PG type text should match all needs or if you want you could use blob
'LONG' => 'text', # Character data of variable length
***************
*** 1723,1733 ****
# Type VARCHAR(2) must have a given length
$len = 1 if (!$len && ($type eq "CHAR"));
return "$TYPE{$type}($len)";
} elsif ($type eq "NUMBER") {
# This is an integer
if (!$scale) {
if ($precision) {
! if ($self->{pg_numeric_type}) {
if ($precision < 5) {
return 'smallint';
} elsif ($precision < 10) {
--- 1728,1743 ----
# Type VARCHAR(2) must have a given length
$len = 1 if (!$len && ($type eq "CHAR"));
return "$TYPE{$type}($len)";
+ } elsif ($type eq "DATE") {
+ if ($self->{pg_date_type}) {
+ return 'date';
+ }
+ return "$TYPE{$type}";
} elsif ($type eq "NUMBER") {
# This is an integer
if (!$scale) {
if ($precision) {
! if ($self->{pg_numeric_type} || $self->{pg_integer_type}) {
if ($precision < 5) {
return 'smallint';
} elsif ($precision < 10) {
***************
*** 1738,1744 ****
}
return "numeric($precision)";
} elsif ($self->{pg_numeric_type}) {
! # Most of the time interger should be enought?
return 'float';
}
} else {
--- 1748,1754 ----
}
return "numeric($precision)";
} elsif ($self->{pg_numeric_type}) {
! # Most of the time integer should be enough?
return 'float';
}
} else {
***************
*** 2268,2274 ****
my($self, $type) = @_;
# Retrieve all indexes
! my $str = "SELECT DISTINCT OBJECT_NAME,OWNER FROM $self->{prefix}_OBJECTS WHERE OBJECT_TYPE='$type' AND STATUS='VALID'";
if (!$self->{schema}) {
$str .= " AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','PERFSTAT')";
} else {
--- 2278,2287 ----
my($self, $type) = @_;
# Retrieve all indexes
! my $str = "SELECT DISTINCT OBJECT_NAME,OWNER FROM $self->{prefix}_OBJECTS WHERE OBJECT_TYPE='$type'";
! if (!$self->{include_invalid}) {
! $str .= " AND STATUS='VALID'";
! }
if (!$self->{schema}) {
$str .= " AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','PERFSTAT')";
} else {
***************
*** 2307,2313 ****
my ($self) = @_;
# Retrieve all indexes
! my $str = "SELECT DISTINCT OBJECT_NAME,OWNER FROM $self->{prefix}_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND STATUS='VALID'";
if (!$self->{schema}) {
$str .= " AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','PERFSTAT')";
} else {
--- 2320,2329 ----
my ($self) = @_;
# Retrieve all indexes
! my $str = "SELECT DISTINCT OBJECT_NAME,OWNER FROM $self->{prefix}_OBJECTS WHERE OBJECT_TYPE='PACKAGE'";
! if (!$self->{include_invalid}) {
! $str .= " AND STATUS='VALID'";
! }
if (!$self->{schema}) {
$str .= " AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','PERFSTAT')";
} else {
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
