diff options
| -rw-r--r-- | contrib/oracle/CHANGES | 24 | ||||
| -rw-r--r-- | contrib/oracle/Ora2Pg.pm | 679 | ||||
| -rw-r--r-- | contrib/oracle/README.ora2pg | 295 | ||||
| -rw-r--r-- | contrib/oracle/TODO | 9 | ||||
| -rw-r--r-- | contrib/oracle/ora2pg.html | 822 | ||||
| -rwxr-xr-x | contrib/oracle/ora2pg.pl | 25 |
6 files changed, 1224 insertions, 630 deletions
diff --git a/contrib/oracle/CHANGES b/contrib/oracle/CHANGES index 341f84d0c2a..34851ecb659 100644 --- a/contrib/oracle/CHANGES +++ b/contrib/oracle/CHANGES @@ -24,4 +24,28 @@ - Fix incorrect type translation and many other bug fix - Add schema only extraction by option schema => 'MYSCHEM' +2001 06 27 - Version 1.4 + - Add online Oracle data extraction and insertion into PG database. + - Data export as insert statement (type => DATA) + - Data export as copy from stdin statement (type => COPY) + +2001 12 28 - Version 1.5 + - Fix LongReadLen problem when exporting Oracle data on LONG and LOB types + Thanks to Stephane Schildknecht for reporting and testing the fix. + - Add more precision on NUMBER type conversion + - Add conversion of type LONG, LOB, FILE + - Fix a problem when extracting data, sometime table could need to be prefixed + by the schema name. + - Fix output of Oracle data extraction. It now require a call to the function + export_data(). +2002 01 07 - Version 1.6 + - Fix problem exporting NULL value. Thanks to Stephane Schildknecht. + +2002 02 14 - Version 1.7 + - Remove export of OUTLINE object type. Thanks to Jean-Paul ARGUDO. + +2002 03 05 - Version 1.8 + - Add Oracle type FLOAT conversion to float8. + - Add column alias extraction on view. Thanks to Jean-Francois RIPOUTEAU + - Add PACKAGE extraction (type => DATA). diff --git a/contrib/oracle/Ora2Pg.pm b/contrib/oracle/Ora2Pg.pm index 91b9dff757d..fa42430c32f 100644 --- a/contrib/oracle/Ora2Pg.pm +++ b/contrib/oracle/Ora2Pg.pm @@ -13,13 +13,13 @@ package Ora2Pg; # the same terms as Perl itself. #------------------------------------------------------------------------------ -use strict; -use vars qw($VERSION); +#use strict; +use vars qw($VERSION $PSQL); use Carp qw(confess); use DBI; -$VERSION = "1.2"; - +$VERSION = "1.8"; +$PSQL = "psql"; =head1 NAME @@ -46,6 +46,11 @@ Ora2Pg - Oracle to PostgreSQL database schema converter datasource => $dbsrc, # Database DBD datasource user => $dbuser, # Database user password => $dbpwd, # Database password + { + PrintError => 0, + RaiseError => 1, + AutoCommit => 0 + } ); # Create the POSTGRESQL representation of all objects in the database @@ -88,42 +93,83 @@ or if you only want to extract tables 10 to 20: max => 20 # End extraction at indice 20 ); -To choose a particular schema just set the following option to your schema name : +To choose a particular Oracle schema to export just set the following option +to your schema name: schema => 'APPS' -To know at which indices table can be found during extraction use the option: +This schema definition can also be needed when you want to export data. If export +failed and complain that the table doesn't exists use this to prefix the table name +by the schema name. + +To know at which indices tables can be found during extraction use the option: showtableid => 1 -To extract all views set the option type as follow: +To extract all views set the type option as follow: type => 'VIEW' -To extract all grants set the option type as follow: +To extract all grants set the type option as follow: type => 'GRANT' -To extract all sequences set the option type as follow: +To extract all sequences set the type option as follow: type => 'SEQUENCE' -To extract all triggers set the option type as follow: +To extract all triggers set the type option as follow: type => 'TRIGGER' -To extract all functions set the option type as follow: +To extract all functions set the type option as follow: type => 'FUNCTION' -To extract all procedures set the option type as follow: +To extract all procedures set the type option as follow: type => 'PROCEDURE' -Default is table schema extraction +To extract all packages and body set the type option as follow: + + type => 'PACKAGE' + +Default is table extraction type => 'TABLE' +To extract all data from table extraction as INSERT statement use: + + type => 'DATA' + +To extract all data from table extraction as COPY statement use: + + type => 'COPY' + +and data_limit => n to specify the max tuples to return. If you set +this options to 0 or nothing, no limitation are used. Additional option +'table', 'min' and 'max' can also be used. + +When use of COPY or DATA you can export data by calling method: + +$schema->export_data("output.sql"); + +Data are dumped to the given filename or to STDOUT with no argument. +You can also send these data directly to a PostgreSQL backend using + the following method: + +$schema->send_to_pgdb($destdatasrc,$destuser,$destpasswd); + +In this case you must call export_data() without argument after the +call to method send_to_pgdb(). + +If you set type to COPY and you want to dump data directly to a PG database, +you must call method send_to_pgdb but data will not be sent via DBD::Pg but +they will be load to the database using the psql command. Calling this method +is istill required to be able to extract database name, hostname and port +information. Edit the $PSQL variable to match the path of your psql +command (nothing to edit if psql is in your path). + =head1 DESCRIPTION @@ -141,6 +187,9 @@ It currently dump the database schema (tables, views, sequences, indexes, grants with primary, unique and foreign keys into PostgreSQL syntax without editing the SQL code generated. +It now can dump Oracle data into PostgreSQL DB as online process. You can choose +what columns can be exported for each table. + Functions, procedures and triggers PL/SQL code generated must be reviewed to match the PostgreSQL syntax. Some usefull recommandation on porting Oracle to PostgreSQL can be found at http://techdocs.postgresql.org/ under the "Converting from other @@ -161,9 +210,9 @@ Features must include: with unique, primary and foreign key. - Grants/privileges export by user and group. - Table selection (by name and max table) export. - - Predefined functions/triggers/procedures export. + - Predefined functions/triggers/procedures/packages export. + - Data export. - Sql query converter (todo) - - Data export (todo) My knowledge regarding database is really poor especially for Oracle so contribution is welcome. @@ -171,7 +220,7 @@ so contribution is welcome. =head1 REQUIREMENT -You just need the DBI and DBD::Oracle perl module to be installed +You just need the DBI, DBD::Pg and DBD::Oracle perl module to be installed @@ -187,12 +236,14 @@ Supported options are: - 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 + - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE, + TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE - debug : Print the current state of the parsing - tables : Extract only the given tables (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 10) Attempt that this list should grow a little more because all initialization is done by this way. @@ -215,10 +266,26 @@ sub new } +=head2 export_data FILENAME + +Print SQL data output to a filename or +to STDOUT if no file is given. + +Must be used only if type option is set to DATA or COPY +=cut + +sub export_data +{ + my ($self, $outfile) = @_; + + $self->_get_sql_data($outfile); +} + + =head2 export_sql FILENAME Print SQL conversion output to a filename or -to STDOUT if no file is given. +simply return these data if no file is given. =cut @@ -233,13 +300,66 @@ sub export_schema close FILE; return; } + # Return data as string return $self->_get_sql_data(); } -#### Private subroutines +=head2 send_to_pgdb DEST_DATASRC DEST_USER DEST_PASSWD + +Open a DB handle to a PostgreSQL database + +=cut + +sub send_to_pgdb +{ + my ($self, $destsrc, $destuser, $destpasswd) = @_; + + # Connect the database + $self->{dbhdest} = DBI->connect($destsrc, $destuser, $destpasswd); + + $destsrc =~ /dbname=([^;]*)/; + $self->{dbname} = $1; + $destsrc =~ /host=([^;]*)/; + $self->{dbhost} = $1; + $self->{dbhost} = 'localhost' if (!$self->{dbhost}); + $destsrc =~ /port=([^;]*)/; + $self->{dbport} = $1; + $self->{dbport} = 5432 if (!$self->{dbport}); + $self->{dbuser} = $destuser; + + # Check for connection failure + if (!$self->{dbhdest}) { + die "Error : $DBI::err ... $DBI::errstr\n"; + } + +} + + +=head2 modify_struct TABLE_NAME ARRAYOF_FIELDNAME + +Modify a table structure during export. Only given fieldname +will be exported. + +=cut + +sub modify_struct +{ + my ($self, $table, @fields) = @_; + + map { $_ = lc($_) } @fields; + $table = lc($table); + + push(@{$self->{modify}{$table}}, @fields); + +} + + + + +#### Private subroutines #### =head1 PRIVATE METHODS @@ -262,6 +382,11 @@ sub _init die "Error : $DBI::err ... $DBI::errstr\n"; } + # Save the DB connection + $self->{datasource} = $options{datasource}; + $self->{user} = $options{user}; + $self->{password} = $options{password}; + $self->{debug} = 0; $self->{debug} = 1 if ($options{debug}); @@ -281,10 +406,14 @@ sub _init $self->{showtableid} = $options{showtableid} if ($options{showtableid}); $self->{dbh}->{LongReadLen} = 0; - #$self->{dbh}->{LongTrunkOk} = 1; + #$self->{dbh}->{LongTruncOk} = 1; + + $self->{data_limit} = 10; + $self->{data_current} = 0; + $self->{data_limit} = $options{data_limit} if (exists $options{data_limit}); # Retreive all table informations - if (!exists $options{type} || ($options{type} eq 'TABLE')) { + if (!exists $options{type} || ($options{type} eq 'TABLE') || ($options{type} eq 'DATA') || ($options{type} eq 'COPY')) { $self->_tables(); } elsif ($options{type} eq 'VIEW') { $self->{dbh}->{LongReadLen} = 100000; @@ -299,8 +428,11 @@ sub _init } elsif (($options{type} eq 'FUNCTION') || ($options{type} eq 'PROCEDURE')) { $self->{dbh}->{LongReadLen} = 100000; $self->_functions($options{type}); + } elsif ($options{type} eq 'PACKAGE') { + $self->{dbh}->{LongReadLen} = 100000; + $self->_packages(); } else { - die "type option must be TABLE, VIEW, GRANT, SEQUENCE, TRIGGER, FUNCTION or PROCEDURE\n"; + die "type option must be TABLE, VIEW, GRANT, SEQUENCE, TRIGGER, PACKAGE, FUNCTION or PROCEDURE\n"; } $self->{type} = $options{type}; @@ -393,6 +525,24 @@ print STDERR "Retrieving functions information...\n" if ($self->{debug}); } +=head2 _packages + +This function is used to retrieve all packages information. + +Set the main hash $self->{packages}. + +=cut + +sub _packages +{ + my ($self) = @_; + +print STDERR "Retrieving packages information...\n" if ($self->{debug}); + $self->{packages} = $self->_get_packages(); + +} + + =head2 _tables This function is used to retrieve all table information. @@ -451,7 +601,7 @@ print STDERR "Max table dump set to $self->{max}.\n" if ($self->{debug} && $self foreach my $t (@$table) { # Jump to desired extraction if (grep(/^${@$t}[2]$/, @done)) { -print STDERR "SSSSSS duplicate ${@$t}[0] - ${@$t}[1] - ${@$t}[2]\n"; +print STDERR "Duplicate entry found: ${@$t}[0] - ${@$t}[1] - ${@$t}[2]\n"; } else { push(@done, ${@$t}[2]); } @@ -540,6 +690,8 @@ print STDERR "Max view dump set to $self->{max}.\n" if ($self->{debug} && $self- print STDERR "[$i] " if ($self->{max} || $self->{min}); print STDERR "Scanning $table...\n" if ($self->{debug}); $self->{views}{$table}{text} = $view_infos{$table}; + ## Added JFR : 3/3/02 : Retrieve also aliases from views + $self->{views}{$table}{alias}= $view_infos{$table}{alias}; $i++; } @@ -554,7 +706,7 @@ Returns a string containing the entire SQL Schema definition compatible with Pos sub _get_sql_data { - my ($self) = @_; + my ($self, $outfile) = @_; my $sql_header = "-- Generated by Ora2Pg, the Oracle database Schema converter, version $VERSION\n"; $sql_header .= "-- Copyright 2000 Gilles DAROLD. All rights reserved.\n"; @@ -569,7 +721,21 @@ sub _get_sql_data if ($self->{type} eq 'VIEW') { print STDERR "Add views definition...\n" if ($self->{debug}); foreach my $view (sort keys %{$self->{views}}) { - $sql_output .= "CREATE VIEW \"\L$view\E\" AS $self->{views}{$view}{text};\n"; + if (!@{$self->{views}{$view}{alias}}) { + $sql_output .= "CREATE VIEW \"\L$view\E\" AS $self->{views}{$view}{text};\n"; + } else { + $sql_output .= "CREATE VIEW \"\L$view\E\" ("; + my $count = 0; + foreach my $d (@{$self->{views}{$view}{alias}}) { + if ($count == 0) { + $count = 1; + } else { + $sql_output .= ", " + } + $sql_output .= "$d->[0]"; + } + $sql_output .= ") AS $self->{views}{$view}{text};\n"; + } } if (!$sql_output) { @@ -578,7 +744,7 @@ print STDERR "Add views definition...\n" if ($self->{debug}); $sql_output .= "\n"; } - return $sql_header . $sql_output . "\nEND TRANSACTION"; + return $sql_header . $sql_output . "\nEND TRANSACTION;\n"; } # Process grant only @@ -629,7 +795,7 @@ print STDERR "Add groups/users privileges...\n" if ($self->{debug}); $sql_output .= "\n" . $grants . "\n"; - return $sql_header . $sql_output . "\nEND TRANSACTION"; + return $sql_header . $sql_output . "\nEND TRANSACTION;\n"; } # Process sequences only @@ -653,7 +819,7 @@ print STDERR "Add sequences definition...\n" if ($self->{debug}); $sql_output = "-- Nothing found of type $self->{type}\n"; } - return $sql_header . $sql_output . "\nEND TRANSACTION"; + return $sql_header . $sql_output . "\nEND TRANSACTION;\n"; } # Process triggers only. PL/SQL code is pre-converted to PL/PGSQL following @@ -697,7 +863,7 @@ print STDERR "Add triggers definition...\n" if ($self->{debug}); $sql_output = "-- Nothing found of type $self->{type}\n"; } - return $sql_header . $sql_output . "\nEND TRANSACTION"; + return $sql_header . $sql_output . "\nEND TRANSACTION;\n"; } # Process functions only @@ -727,9 +893,248 @@ print STDERR "Add functions definition...\n" if ($self->{debug}); $sql_output = "-- Nothing found of type $self->{type}\n"; } - return $sql_header . $sql_output . "\nEND TRANSACTION"; + return $sql_header . $sql_output . "\nEND TRANSACTION;\n"; } + # Process functions only + if ($self->{type} eq 'PACKAGE') { +print STDERR "Add packages definition...\n" if ($self->{debug}); + foreach my $pkg (sort keys %{$self->{packages}}) { + $sql_output .= "-- Oracle package '$pkg' declaration, please edit to match PostgreSQL syntax.\n"; + $sql_output .= "$self->{packages}{$pkg}\n"; + $sql_output .= "-- End of Oracle package '$pkg' declaration\n\n"; + } + + if (!$sql_output) { + $sql_output = "-- Nothing found of type $self->{type}\n"; + } + + return $sql_header . $sql_output . "\nEND TRANSACTION;\n"; + } + + + + # Extract data only + if (($self->{type} eq 'DATA') || ($self->{type} eq 'COPY')) { + # Connect the database + $self->{dbh} = DBI->connect($self->{datasource}, $self->{user}, $self->{password}); + # Check for connection failure + if (!$self->{dbh}) { + die "Error : $DBI::err ... $DBI::errstr\n"; + } + + if (!$self->{dbhdest}) { + if ($outfile) { + open(FILE,">$outfile") or die "Can't open $outfile: $!"; + print FILE $sql_header; + } else { + print $sql_header; + } + } else { + if ($self->{type} eq 'COPY') { + open(DBH, "| $PSQL -h $self->{dbhost} -p $self->{dbport} -d $self->{dbname}") or die "Can't open $PSQL command, $!\n"; + } + } + + foreach my $table (keys %{$self->{tables}}) { +print STDERR "Dumping table $table...\n" if ($self->{debug}); + my @tt = (); + my @nn = (); + my $s_out = "INSERT INTO \"\L$table\E\" ("; + if ($self->{type} eq 'COPY') { + $s_out = "COPY \"\L$table\E\" FROM stdin;\n"; + } + + foreach my $i ( 0 .. $#{$self->{tables}{$table}{field_name}} ) { + my $fieldname = ${$self->{tables}{$table}{field_name}}[$i]; + if (exists $self->{modify}{"\L$table\E"}) { + next if (!grep(/\L$fieldname\E/, @{$self->{modify}{"\L$table\E"}})); + } + foreach my $f (@{$self->{tables}{$table}{column_info}}) { + next if (${$f}[0] ne "$fieldname"); + my $type = $self->_sql_type(${$f}[1], ${$f}[2], ${$f}[5], ${$f}[6]); + $type = "${$f}[1], ${$f}[2]" if (!$type); + push(@tt, $type); + push(@nn, ${$f}[0]); + if ($self->{type} ne 'COPY') { + $s_out .= "\"\L${$f}[0]\E\","; + } + last; + } + } + + if ($self->{type} ne 'COPY') { + $s_out =~ s/,$//; + $s_out .= ") VALUES ("; + } + # Extract all data from the current table + $self->{data_current} = 0; + $self->{data_end} = 0; + while ( !$self->{data_end} ) { + my $sth = $self->_get_data($table, \@nn, \@tt); + $self->{data_end} = 1 if (!$self->{data_limit}); + my $count = 0; + my $sql = ''; + if ($self->{type} eq 'COPY') { + if ($self->{dbhdest}) { + $sql = $s_out; + } else { + if ($outfile) { + print FILE $s_out; + } else { + print $s_out; + } + } + } + while (my $row = $sth->fetch) { + if ($self->{type} ne 'COPY') { + if ($self->{dbhdest}) { + $sql .= $s_out; + } else { + if ($outfile) { + print FILE $s_out; + } else { + print $s_out; + } + } + } + for (my $i = 0; $i <= $#{$row}; $i++) { + if ($self->{type} ne 'COPY') { + if ($tt[$i] =~ /(char|date|time|text)/) { + $row->[$i] =~ s/'/''/gs; + if ($row->[$i]) { + $row->[$i] = "'$row->[$i]'"; + } else { + $row->[$i] = 'NULL'; + } + if ($self->{dbhdest}) { + $sql .= $row->[$i]; + } else { + if ($outfile) { + print FILE $row->[$i]; + } else { + print $row->[$i]; + } + } + } else { + if (!$row->[$i]) { + $row->[$i] = 'NULL'; + } + if ($self->{dbhdest}) { + $sql .= $row->[$i]; + } else { + if ($outfile) { + print FILE $row->[$i]; + } else { + print $row->[$i]; + } + } + } + if ($i < $#{$row}) { + if ($self->{dbhdest}) { + $sql .= ","; + } else { + if ($outfile) { + print FILE ","; + } else { + print ","; + } + } + } + } else { + if (!$row->[$i]) { + $row->[$i] = '\N'; + } + if ($self->{dbhdest}) { + $sql .= $row->[$i]; + } else { + if ($outfile) { + print FILE $row->[$i]; + } else { + print $row->[$i]; + } + } + if ($i < $#{$row}) { + if ($self->{dbhdest}) { + $sql .= "\t"; + } else { + if ($outfile) { + print FILE "\t"; + } else { + print "\t"; + } + } + } else { + if ($self->{dbhdest}) { + $sql .= "\n"; + } else { + if ($outfile) { + print FILE "\n"; + } else { + print "\n"; + } + } + } + } + } + if ($self->{type} ne 'COPY') { + if ($self->{dbhdest}) { + $sql .= ");\n"; + } else { + if ($outfile) { + print FILE ");\n"; + } else { + print ");\n"; + } + } + } + $count++; + } + if ($self->{type} eq 'COPY') { + if ($self->{dbhdest}) { + $sql .= "\\.\n"; + } else { + if ($outfile) { + print FILE "\\.\n"; + } else { + print "\\.\n"; + } + } + } + if ($self->{data_limit}) { + $self->{data_end} = 1 if ($count+1 < $self->{data_limit}); + } + # Insert data if we are in online processing mode + if ($self->{dbhdest}) { + if ($self->{type} ne 'COPY') { + my $s = $self->{dbhdest}->prepare($sql) or die $self->{dbhdest}->errstr . "\n"; + $s->execute or die $s->errstr . "\n"; + } else { + print DBH "$sql"; + } + } + } + } + + # Disconnect from the database + $self->{dbh}->disconnect() if ($self->{dbh}); + + if (!$self->{dbhdest}) { + if ($outfile) { + print FILE "\nEND TRANSACTION;\n"; + } else { + print "\nEND TRANSACTION;\n"; + } + } + + $self->{dbhdest}->disconnect() if ($self->{dbhdest}); + + if ($self->{type} eq 'COPY') { + close DBH; + } + + return; + } # Dump the database structure @@ -741,7 +1146,7 @@ print STDERR "Dumping table $table...\n" if ($self->{debug}); foreach my $i ( 0 .. $#{$self->{tables}{$table}{field_name}} ) { foreach my $f (@{$self->{tables}{$table}{column_info}}) { next if (${$f}[0] ne "${$self->{tables}{$table}{field_name}}[$i]"); - my $type = $self->_sql_type(${$f}[1], ${$f}[2]); + my $type = $self->_sql_type(${$f}[1], ${$f}[2], ${$f}[5], ${$f}[6]); $type = "${$f}[1], ${$f}[2]" if (!$type); $sql_output .= "\t\"\L${$f}[0]\E\" $type"; # Set the primary key definition @@ -803,11 +1208,63 @@ print STDERR "Dumping table $table...\n" if ($self->{debug}); $sql_output = "-- Nothing found of type TABLE\n"; } - return $sql_header . $sql_output . "\nEND TRANSACTION"; + return $sql_header . $sql_output . "\nEND TRANSACTION;\n"; +} + + +=head2 _get_data TABLE + +This function implements a Oracle-native data extraction. + +Return a list of array reference containing the data + +=cut + +sub _get_data +{ + my ($self, $table, $name, $type) = @_; + + my $str = "SELECT "; + my $tmp = "SELECT "; + for my $k (0 .. $#{$name}) { + if ( $type->[$k] =~ /(date|time)/) { + $str .= "to_char($name->[$k], 'YYYY-MM-DD'),"; + } else { + $str .= "$name->[$k],"; + } + $tmp .= "$name->[$k],"; + } + $str =~ s/,$//; + $tmp =~ s/,$//; + my $tmp2 = $tmp; + $tmp2 =~ s/SELECT /SELECT ROWNUM as noline,/; + + # Fix a problem when the table need to be prefixed by the schema + if ($self->{schema}) { + $table = "$self->{schema}.$table"; + } + if ($self->{data_limit}) { + $str = $tmp . " FROM ( $tmp2 FROM ( $tmp FROM $table) "; + $str .= " WHERE ROWNUM < ($self->{data_limit} + $self->{data_current})) "; + $str .= " WHERE noline >= $self->{data_current}"; + } else { + $str .= " FROM $table"; + } + $self->{data_current} += $self->{data_limit}; + + # Fix a problem when exporting type LONG and LOB + $self->{dbh}->{'LongReadLen'} = 1023*1024; + $self->{dbh}->{'LongTruncOk'} = 1; + + my $sth = $self->{dbh}->prepare($str) or die $sth->errstr . "\n"; + $sth->execute or die $sth->errstr . "\n"; + + return $sth; + } -=head2 _sql_type INTERNAL_TYPE LENGTH +=head2 _sql_type INTERNAL_TYPE LENGTH PRECISION SCALE This function return the PostgreSQL datatype corresponding to the Oracle internal type. @@ -816,32 +1273,70 @@ Oracle internal type. sub _sql_type { - my ($self, $type, $len) = @_; + my ($self, $type, $len, $precision, $scale) = @_; my %TYPE = ( + # Oracle only has one flexible underlying numeric type, NUMBER. + # Without precision and scale it is set to PG type float8 to match all needs 'NUMBER' => 'float8', - 'LONG' => 'integer', + # CHAR types limit of 2000 bytes with default to 1 if no length is given. + # PG char type has max length set to 8104 so it should match all needs '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', + 'NVARCHAR2' => 'varchar', + # The DATE data type is used to store the date and time information. + # Pg type datetime should match all needs 'DATE' => 'datetime', + # 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 + 'LONG RAW' => 'text', # Raw binary data of variable length + # Types LOB and FILE are like LONG but with up to 4Gb. + # PG type text should match all needs or if you want you could use blob (large object) + 'CLOB' => 'text', # A large object containing single-byte characters + 'NLOB' => 'text', # A large object containing national character set data + 'BLOB' => 'text', # Binary large object + 'BFILE' => 'text', # Locator for external large binary file + # The RAW type is presented as hexadecimal characters. The contents are treated as binary data. Limit of 2000 bytes + # Pg type text should match all needs or if you want you could use blob (large object) 'RAW' => 'text', 'ROWID' => 'oid', 'LONG RAW' => 'binary', + 'FLOAT' => 'float8' ); # Overide the length - $len = '' if ($type eq 'NUMBER'); + $len = $precision if ( ($type eq 'NUMBER') && $precision ); if (exists $TYPE{$type}) { if ($len) { - if (($type eq "NUMBER") || ($type eq "LONG")) { - return "$TYPE{$type}($len)"; - } elsif (($type eq "CHAR") || ($type =~ /VARCHAR/)) { + if ( ($type eq "CHAR") || ($type =~ /VARCHAR/) ) { + # Type CHAR have default length set to 1 + # 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) { + return "numeric($precision)"; + } + } else { + if ($precision) { + return "decimal($precision,$scale)"; + } + } + return "$TYPE{$type}"; } else { return "$TYPE{$type}"; } } else { + return $TYPE{$type}; } } @@ -872,7 +1367,7 @@ sub _column_info my ($self, $table) = @_; my $sth = $self->{dbh}->prepare(<<END) or die $self->{dbh}->errstr; -SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT +SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='$table' END @@ -880,7 +1375,7 @@ END my $data = $sth->fetchall_arrayref(); if ($self->{debug}) { foreach my $d (@$data) { -print STDERR "\t$d->[0] => type:$d->[1] , length:$d->[2] , nullable:$d->[3] , default:$d->[4]\n"; +print STDERR "\t$d->[0] => type:$d->[1] , length:$d->[2], precision:$d->[5], scale:$d->[6], nullable:$d->[3] , default:$d->[4]\n"; } } @@ -1033,7 +1528,7 @@ sub _get_users # Retrieve all USERS defined in this database my $str = "SELECT USERNAME FROM DBA_USERS"; if (!$self->{schema}) { - $str .= " WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM' AND USERNAME <> 'DBSNMP'"; + $str .= " WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM' AND USERNAME <> 'DBSNMP' AND USERNAME <> 'OUTLN'"; } else { $str .= " WHERE USERNAME = '$self->{schema}'"; } @@ -1067,7 +1562,7 @@ sub _get_roles # Retrieve all ROLES defined in this database my $str = "SELECT GRANTED_ROLE,GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTEE NOT IN (select distinct role from dba_roles)"; if (!$self->{schema}) { - $str .= " AND GRANTEE <> 'SYS' AND GRANTEE <> 'SYSTEM' AND GRANTEE <> 'DBSNMP'"; + $str .= " AND GRANTEE <> 'SYS' AND GRANTEE <> 'SYSTEM' AND GRANTEE <> 'DBSNMP' AND GRANTEE <> 'OUTLN'"; } else { $str .= " AND GRANTEE = '$self->{schema}'"; } @@ -1103,7 +1598,7 @@ sub _get_all_grants if ($self->{schema}) { $str .= " WHERE GRANTEE = '$self->{schema}'"; } else { - $str .= " WHERE GRANTEE <> 'SYS' AND GRANTEE <> 'SYSTEM' AND GRANTEE <> 'DBSNMP'"; + $str .= " WHERE GRANTEE <> 'SYS' AND GRANTEE <> 'SYSTEM' AND GRANTEE <> 'DBSNMP' AND GRANTEE <> 'OUTLN'"; } $str .= " ORDER BY TABLE_NAME"; @@ -1167,7 +1662,7 @@ sub _get_sequences # Retrieve all indexes my $str = "SELECT DISTINCT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER, CACHE_SIZE, CYCLE_FLAG FROM DBA_SEQUENCES"; if (!$self->{schema}) { - $str .= " WHERE SEQUENCE_OWNER <> 'SYS' AND SEQUENCE_OWNER <> 'SYSTEM' AND SEQUENCE_OWNER <> 'DBSNMP'"; + $str .= " WHERE SEQUENCE_OWNER <> 'SYS' AND SEQUENCE_OWNER <> 'SYSTEM' AND SEQUENCE_OWNER <> 'DBSNMP' AND SEQUENCE_OWNER <> 'OUTLN'"; } else { $str .= " WHERE SEQUENCE_OWNER = '$self->{schema}'"; } @@ -1198,7 +1693,7 @@ sub _get_views # Retrieve all views my $str = "SELECT VIEW_NAME,TEXT FROM DBA_VIEWS"; if (!$self->{schema}) { - $str .= " WHERE OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND OWNER <> 'DBSNMP'"; + $str .= " WHERE OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND OWNER <> 'DBSNMP' AND OWNER <> 'OUTLN'"; } else { $str .= " WHERE OWNER = '$self->{schema}'"; } @@ -1208,11 +1703,46 @@ sub _get_views my %data = (); while (my $row = $sth->fetch) { $data{$row->[0]} = $row->[1]; + @{$data{$row->[0]}{alias}} = $self->_alias_info ($row->[0]); } return %data; } +=head2 _alias_info + +This function implements a Oracle-native column information. + +Return a list of array reference containing the following informations +for each alias of the given view + +[( + column name, + column id +)] + +=cut + +sub _alias_info +{ + my ($self, $view) = @_; + + my $sth = $self->{dbh}->prepare(<<END) or die $self->{dbh}->errstr; +SELECT COLUMN_NAME, COLUMN_ID +FROM DBA_TAB_COLUMNS +WHERE TABLE_NAME='$view' +END + $sth->execute or die $sth->errstr; + my $data = $sth->fetchall_arrayref(); + if ($self->{debug}) { + foreach my $d (@$data) { + print STDERR "\t$d->[0] => column id:$d->[1]\n"; + } + } + + return @$data; + +} =head2 _get_triggers @@ -1229,7 +1759,7 @@ sub _get_triggers # Retrieve all indexes my $str = "SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, TRIGGER_BODY FROM DBA_TRIGGERS WHERE STATUS='ENABLED'"; if (!$self->{schema}) { - $str .= " AND OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND OWNER <> 'DBSNMP'"; + $str .= " AND OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND OWNER <> 'DBSNMP' AND OWNER <> 'OUTLN'"; } else { $str .= " AND OWNER = '$self->{schema}'"; } @@ -1260,7 +1790,7 @@ sub _get_functions # Retrieve all indexes my $str = "SELECT DISTINCT OBJECT_NAME,OWNER FROM DBA_OBJECTS WHERE OBJECT_TYPE='$type' AND STATUS='VALID'"; if (!$self->{schema}) { - $str .= " AND OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND OWNER <> 'DBSNMP'"; + $str .= " AND OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND OWNER <> 'DBSNMP' AND OWNER <> 'OUTLN'"; } else { $str .= " AND OWNER = '$self->{schema}'"; } @@ -1284,6 +1814,48 @@ sub _get_functions } +=head2 _get_packages + +This function implements a Oracle-native packages information. + +Return a hash of all function name with their PLSQL code + +=cut + +sub _get_packages +{ + my ($self) = @_; + + # Retrieve all indexes + my $str = "SELECT DISTINCT OBJECT_NAME,OWNER FROM DBA_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND STATUS='VALID'"; + if (!$self->{schema}) { + $str .= " AND OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND OWNER <> 'DBSNMP' AND OWNER <> 'OUTLN'"; + } else { + $str .= " AND OWNER = '$self->{schema}'"; + } + + my $sth = $self->{dbh}->prepare($str) or die $self->{dbh}->errstr; + $sth->execute or die $sth->errstr; + + my %packages = (); + my @fct_done = (); + while (my $row = $sth->fetch) { +print STDERR "\tFound Package: $row->[0]\n" if ($self->{debug}); + next if (grep(/^$row->[0]$/, @fct_done)); + push(@fct_done, $row->[0]); + my $sql = "SELECT TEXT FROM DBA_SOURCE WHERE OWNER='$row->[1]' AND NAME='$row->[0]' ORDER BY LINE"; + my $sth2 = $self->{dbh}->prepare($sql) or die $self->{dbh}->errstr; + $sth2->execute or die $sth2->errstr; + while (my $r = $sth2->fetch) { + $packages{"$row->[0]"} .= $r->[0]; + } + } + + return \%packages; +} + + + =head2 _table_info This function retrieve all Oracle-native tables information. @@ -1311,7 +1883,7 @@ sub _table_info if ($self->{schema}) { $sql .= " and at.OWNER='$self->{schema}'"; } else { - $sql .= "and at.OWNER <> 'SYS' and at.OWNER <> 'SYSTEM' and at.OWNER <> 'DBSNMP'"; + $sql .= "AND at.OWNER <> 'SYS' AND at.OWNER <> 'SYSTEM' AND at.OWNER <> 'DBSNMP' AND at.OWNER <> 'OUTLN'"; } $sql .= " order by tc.TABLE_TYPE, at.OWNER, at.TABLE_NAME"; my $sth = $self->{dbh}->prepare( $sql ) or return undef; @@ -1328,6 +1900,7 @@ __END__ Gilles Darold <gilles@darold.net> + =head1 COPYRIGHT Copyright (c) 2001 Gilles Darold - All rights reserved. @@ -1343,9 +1916,15 @@ it can move and not be compatible with older version so I will do my best to give you official support for Ora2Pg. Your volontee to help construct it and your contribution are welcome. + =head1 SEE ALSO -L<DBI>, L<DBD::Oracle> +L<DBI>, L<DBD::Oracle>, L<DBD::Pg> + + +=head1 ACKNOWLEDGEMENTS + +Thanks to Jason Servetar who decided me to implement data extraction. =cut diff --git a/contrib/oracle/README.ora2pg b/contrib/oracle/README.ora2pg index 159ac038b19..2d23f816af8 100644 --- a/contrib/oracle/README.ora2pg +++ b/contrib/oracle/README.ora2pg @@ -20,6 +20,11 @@ SYNOPSIS datasource => $dbsrc, # Database DBD datasource user => $dbuser, # Database user password => $dbpwd, # Database password + { + PrintError => 0, + RaiseError => 1, + AutoCommit => 0 + } ); # Create the POSTGRESQL representation of all objects in the database @@ -62,74 +67,116 @@ SYNOPSIS max => 20 # End extraction at indice 20 ); - To choose a particular schema just set the following option to - your schema name : + To choose a particular Oracle schema to export just set the following + option to your schema name: schema => 'APPS' - To know at which indices table can be found during extraction - use the option: + This schema definition can also be needed when you want to export data. + If export failed and complain that the table doesn't exists use this to + prefix the table name by the schema name. + + To know at which indices tables can be found during extraction use the + option: showtableid => 1 - To extract all views set the option type as follow: + To extract all views set the type option as follow: type => 'VIEW' - To extract all grants set the option type as follow: + To extract all grants set the type option as follow: type => 'GRANT' - To extract all sequences set the option type as follow: + To extract all sequences set the type option as follow: type => 'SEQUENCE' - To extract all triggers set the option type as follow: + To extract all triggers set the type option as follow: type => 'TRIGGER' - To extract all functions set the option type as follow: + To extract all functions set the type option as follow: type => 'FUNCTION' - To extract all procedures set the option type as follow: + To extract all procedures set the type option as follow: type => 'PROCEDURE' - Default is table schema extraction + To extract all packages and body set the type option as follow: + + type => 'PACKAGE' + + Default is table extraction type => 'TABLE' + To extract all data from table extraction as INSERT statement use: + + type => 'DATA' + + To extract all data from table extraction as COPY statement use: + + type => 'COPY' + + and data_limit => n to specify the max tuples to return. If you set this + options to 0 or nothing, no limitation are used. Additional option + 'table', 'min' and 'max' can also be used. + + When use of COPY or DATA you can export data by calling method: + + $schema->export_data("output.sql"); + + Data are dumped to the given filename or to STDOUT with no argument. You + can also send these data directly to a PostgreSQL backend using the + following method: + + $schema->send_to_pgdb($destdatasrc,$destuser,$destpasswd); + + In this case you must call export_data() without argument after the call + to method send_to_pgdb(). + + If you set type to COPY and you want to dump data directly to a PG + database, you must call method send_to_pgdb but data will not be sent + via DBD::Pg but they will be load to the database using the psql + command. Calling this method is istill required to be able to extract + database name, hostname and port information. Edit the $PSQL variable to + match the path of your psql command (nothing to edit if psql is in your + path). + DESCRIPTION - Ora2Pg is a perl OO module used to export an Oracle database - schema to a PostgreSQL compatible schema. + Ora2Pg is a perl OO module used to export an Oracle database schema to a + PostgreSQL compatible schema. - It simply connect to your Oracle database, extract its structure - and generate a SQL script that you can load into your PostgreSQL - database. + It simply connect to your Oracle database, extract its structure and + generate a SQL script that you can load into your PostgreSQL database. - I'm not a Oracle DBA so I don't really know something about its - internal structure so you may find some incorrect things. Please - tell me what is wrong and what can be better. + I'm not a Oracle DBA so I don't really know something about its internal + structure so you may find some incorrect things. Please tell me what is + wrong and what can be better. It currently dump the database schema (tables, views, sequences, - indexes, grants), with primary, unique and foreign keys into - PostgreSQL syntax without editing the SQL code generated. + indexes, grants), with primary, unique and foreign keys into PostgreSQL + syntax without editing the SQL code generated. + + It now can dump Oracle data into PostgreSQL DB as online process. You + can choose what columns can be exported for each table. Functions, procedures and triggers PL/SQL code generated must be - reviewed to match the PostgreSQL syntax. Some usefull - recommandation on porting Oracle to PostgreSQL can be found at + reviewed to match the PostgreSQL syntax. Some usefull recommandation on + porting Oracle to PostgreSQL can be found at http://techdocs.postgresql.org/ under the "Converting from other - Databases to PostgreSQL" Oracle part. I just notice one thing - more is that the trunc() function in Oracle is the same for - number or date so be carefull when porting to PostgreSQL to use - trunc() for number and date_trunc() for date. + Databases to PostgreSQL" Oracle part. I just notice one thing more is + that the trunc() function in Oracle is the same for number or date so be + carefull when porting to PostgreSQL to use trunc() for number and + date_trunc() for date. ABSTRACT - The goal of the Ora2Pg perl module is to cover all part needed - to export an Oracle database to a PostgreSQL database without - other thing that provide the connection parameters to the Oracle - database. + The goal of the Ora2Pg perl module is to cover all part needed to export + an Oracle database to a PostgreSQL database without other thing that + provide the connection parameters to the Oracle database. Features must include: @@ -137,15 +184,15 @@ ABSTRACT with unique, primary and foreign key. - Grants/privileges export by user and group. - Table selection (by name and max table) export. - - Predefined functions/triggers/procedures export. + - Predefined functions/triggers/procedures/packages export. + - Data export. - Sql query converter (todo) - - Data export (todo) - My knowledge regarding database is really poor especially for - Oracle so contribution is welcome. + My knowledge regarding database is really poor especially for Oracle so + contribution is welcome. REQUIREMENT - You just need the DBI and DBD::Oracle perl module to be + You just need the DBI, DBD::Pg and DBD::Oracle perl module to be installed PUBLIC METHODS @@ -159,36 +206,57 @@ PUBLIC METHODS - 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 + - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE, + TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE - debug : Print the current state of the parsing - tables : Extract only the given tables (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 10) Attempt that this list should grow a little more because all initialization is done by this way. + export_data FILENAME + + Print SQL data output to a filename or to STDOUT if no file is given. + + Must be used only if type option is set to DATA or COPY =cut + + sub export_data { my ($self, $outfile) = @_; + + $self->_get_sql_data($outfile); + } + export_sql FILENAME - Print SQL conversion output to a filename or to STDOUT if no - file is given. + Print SQL conversion output to a filename or simply return these data if + no file is given. + + send_to_pgdb DEST_DATASRC DEST_USER DEST_PASSWD + + Open a DB handle to a PostgreSQL database + + modify_struct TABLE_NAME ARRAYOF_FIELDNAME + + Modify a table structure during export. Only given fieldname will be + exported. PRIVATE METHODS _init HASH_OPTIONS - Initialize a Ora2Pg object instance with a connexion to the - Oracle database. + Initialize a Ora2Pg object instance with a connexion to the Oracle + database. _grants This function is used to retrieve all privilege information. - It extract all Oracle's ROLES to convert them as Postgres groups - and search all users associated to these roles. + It extract all Oracle's ROLES to convert them as Postgres groups and + search all users associated to these roles. - Set the main hash $self->{groups}. Set the main hash $self- - >{grantss}. + Set the main hash $self->{groups}. Set the main hash $self->{grantss}. _sequences @@ -208,29 +276,35 @@ PRIVATE METHODS Set the main hash $self->{functions}. + _packages + + This function is used to retrieve all packages information. + + Set the main hash $self->{packages}. + _tables This function is used to retrieve all table information. - Set the main hash of the database structure $self->{tables}. - Keys are the names of all tables retrieved from the current - database. Each table information compose an array associated to - the table_info key as array reference. In other way: + Set the main hash of the database structure $self->{tables}. Keys are + the names of all tables retrieved from the current database. Each table + information compose an array associated to the table_info key as array + reference. In other way: $self->{tables}{$class_name}{table_info} = [(OWNER,TYPE)]; - DBI TYPE can be TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, - LOCAL TEMPORARY, ALIAS, SYNONYM or a data source specific type - identifier. This only extract TABLE type. + DBI TYPE can be TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL + TEMPORARY, ALIAS, SYNONYM or a data source specific type identifier. + This only extract TABLE type. - It also get the following informations in the DBI object to - affect the main hash of the database structure : + It also get the following informations in the DBI object to affect the + main hash of the database structure : $self->{tables}{$class_name}{field_name} = $sth->{NAME}; $self->{tables}{$class_name}{field_type} = $sth->{TYPE}; - It also call these other private subroutine to affect the main - hash of the database structure : + It also call these other private subroutine to affect the main hash of + the database structure : @{$self->{tables}{$class_name}{column_info}} = $self->_column_info($class_name); @{$self->{tables}{$class_name}{primary_key}} = $self->_primary_key($class_name); @@ -241,9 +315,9 @@ PRIVATE METHODS This function is used to retrieve all views information. - Set the main hash of the views definition $self->{views}. Keys - are the names of all views retrieved from the current database - values are the text definition of the views. + Set the main hash of the views definition $self->{views}. Keys are the + names of all views retrieved from the current database values are the + text definition of the views. It then set the main hash as follow: @@ -252,58 +326,62 @@ PRIVATE METHODS _get_sql_data - Returns a string containing the entire SQL Schema definition - compatible with PostgreSQL + Returns a string containing the entire SQL Schema definition compatible + with PostgreSQL + + _get_data TABLE + + This function implements a Oracle-native data extraction. - _sql_type INTERNAL_TYPE LENGTH + Return a list of array reference containing the data - This function return the PostgreSQL datatype corresponding to - the Oracle internal type. + _sql_type INTERNAL_TYPE LENGTH PRECISION SCALE + + This function return the PostgreSQL datatype corresponding to the Oracle + internal type. _column_info TABLE This function implements a Oracle-native column information. - Return a list of array reference containing the following - informations for each column the given a table + Return a list of array reference containing the following informations + for each column the given a table - [( column name, column type, column length, nullable column, - default value )] + [( column name, column type, column length, nullable column, default + value )] _primary_key TABLE - This function implements a Oracle-native primary key column - information. + This function implements a Oracle-native primary key column information. - Return a list of all column name defined as primary key for the - given table. + Return a list of all column name defined as primary key for the given + table. _unique_key TABLE - This function implements a Oracle-native unique key column - information. + This function implements a Oracle-native unique key column information. - Return a list of all column name defined as unique key for the - given table. + Return a list of all column name defined as unique key for the given + table. _foreign_key TABLE This function implements a Oracle-native foreign key reference information. - Return a list of hash of hash of array reference. Ouuf! Nothing - very difficult. The first hash is composed of all foreign key - name. The second hash just have two key known as 'local' and - remote' corresponding to the local table where the foreign key - is defined and the remote table where the key refer. + Return a list of hash of hash of array reference. Ouuf! Nothing very + difficult. The first hash is composed of all foreign key name. The + second hash just have two key known as 'local' and remote' corresponding + to the local table where the foreign key is defined and the remote table + where the key refer. The foreign key name is composed as follow: 'local_table_name->remote_table_name' - Foreign key data consist in two array representing at the same - indice the local field and the remote field where the first one - refer to the second. Just like this: + Foreign key data consist in two array representing at the same indice + the local field and the remote field where the first one refer to the + second. Just like this: @{$link{$fkey_name}{local}} = @local_columns; @{$link{$fkey_name}{remote}} = @remote_columns; @@ -318,31 +396,27 @@ PRIVATE METHODS This function implements a Oracle-native roles information. - Return a hash of all groups (roles) as an array of associated - users. + Return a hash of all groups (roles) as an array of associated users. _get_all_grants - This function implements a Oracle-native user privilege - information. + This function implements a Oracle-native user privilege information. - Return a hash of all tables grants as an array of associated - users. + Return a hash of all tables grants as an array of associated users. _get_indexes TABLE This function implements a Oracle-native indexes information. - Return hash of array containing all unique index and a hash of - array of all indexes name which are not primary keys for the - given table. + Return hash of array containing all unique index and a hash of array of + all indexes name which are not primary keys for the given table. _get_sequences This function implements a Oracle-native sequences information. - Return a hash of array of sequence name with MIN_VALUE, - MAX_VALUE, INCREMENT and LAST_NUMBER for the given table. + Return a hash of array of sequence name with MIN_VALUE, MAX_VALUE, + INCREMENT and LAST_NUMBER for the given table. _get_views @@ -350,6 +424,15 @@ PRIVATE METHODS Return a hash of view name with the SQL query it is based on. + _alias_info + + This function implements a Oracle-native column information. + + Return a list of array reference containing the following informations + for each alias of the given view + + [( column name, column id )] + _get_triggers This function implements a Oracle-native triggers information. @@ -362,6 +445,12 @@ PRIVATE METHODS Return a hash of all function name with their PLSQL code + _get_packages + + This function implements a Oracle-native packages information. + + Return a hash of all function name with their PLSQL code + _table_info This function retrieve all Oracle-native tables information. @@ -374,16 +463,18 @@ AUTHOR COPYRIGHT Copyright (c) 2001 Gilles Darold - All rights reserved. - This program is free software; you can redistribute it and/or - modify it under the same terms as Perl itself. + This program is free software; you can redistribute it and/or modify it + under the same terms as Perl itself. BUGS This perl module is in the same state as my knowledge regarding - database, it can move and not be compatible with older version - so I will do my best to give you official support for Ora2Pg. - Your volontee to help construct it and your contribution are - welcome. + database, it can move and not be compatible with older version so I will + do my best to give you official support for Ora2Pg. Your volontee to + help construct it and your contribution are welcome. SEE ALSO - the DBI manpage, the DBD::Oracle manpage + the DBI manpage, the DBD::Oracle manpage, the DBD::Pg manpage + +ACKNOWLEDGEMENTS + Thanks to Jason Servetar who decided me to implement data extraction. diff --git a/contrib/oracle/TODO b/contrib/oracle/TODO index 8f9455aa22d..c9f43921704 100644 --- a/contrib/oracle/TODO +++ b/contrib/oracle/TODO @@ -1,13 +1,10 @@ -Here are some more thing to do : +Add possible call to perl function for each field value exported +(data conversion on the fly before dump) - - More precision in type conversion based on length - (I've no good DB to do that) - -This will be done as soon as possible + - Fix problem regarding table/constraint output order. The following need your help : - - Extracting/converting datas. - SQL queries converter. - PL/SQL code converter. diff --git a/contrib/oracle/ora2pg.html b/contrib/oracle/ora2pg.html index 7f1376d0550..544ddcfae54 100644 --- a/contrib/oracle/ora2pg.html +++ b/contrib/oracle/ora2pg.html @@ -1,107 +1,110 @@ <HTML> <HEAD> <TITLE>Ora2Pg - Oracle to PostgreSQL database schema converter</TITLE> -<LINK REV="made" HREF="mailto:root@porky.devel.redhat.com"> +<LINK REV="made" HREF="mailto:darold@localhost.localdomain"> </HEAD> <BODY> +<A NAME="__index__"></A> <!-- INDEX BEGIN --> <UL> - <LI><A HREF="#NAME">NAME</A> - <LI><A HREF="#SYNOPSIS">SYNOPSIS</A> - <LI><A HREF="#DESCRIPTION">DESCRIPTION</A> - <LI><A HREF="#ABSTRACT">ABSTRACT</A> - <LI><A HREF="#REQUIREMENT">REQUIREMENT</A> - <LI><A HREF="#PUBLIC_METHODS">PUBLIC METHODS</A> + <LI><A HREF="#name">NAME</A></LI> + <LI><A HREF="#synopsis">SYNOPSIS</A></LI> + <LI><A HREF="#description">DESCRIPTION</A></LI> + <LI><A HREF="#abstract">ABSTRACT</A></LI> + <LI><A HREF="#requirement">REQUIREMENT</A></LI> + <LI><A HREF="#public methods">PUBLIC METHODS</A></LI> <UL> - <LI><A HREF="#new_HASH_OPTIONS">new HASH_OPTIONS</A> - <LI><A HREF="#export_sql_FILENAME">export_sql FILENAME</A> + <LI><A HREF="#new hash_options">new HASH_OPTIONS</A></LI> + <LI><A HREF="#export_data filename">export_data FILENAME</A></LI> + <LI><A HREF="#export_sql filename">export_sql FILENAME</A></LI> + <LI><A HREF="#send_to_pgdb dest_datasrc dest_user dest_passwd">send_to_pgdb DEST_DATASRC DEST_USER DEST_PASSWD</A></LI> + <LI><A HREF="#modify_struct table_name arrayof_fieldname">modify_struct TABLE_NAME ARRAYOF_FIELDNAME</A></LI> </UL> - <LI><A HREF="#PRIVATE_METHODS">PRIVATE METHODS</A> + <LI><A HREF="#private methods">PRIVATE METHODS</A></LI> <UL> - <LI><A HREF="#_init_HASH_OPTIONS">_init HASH_OPTIONS</A> - <LI><A HREF="#_grants">_grants</A> - <LI><A HREF="#_sequences">_sequences</A> - <LI><A HREF="#_triggers">_triggers</A> - <LI><A HREF="#_functions">_functions</A> - <LI><A HREF="#_tables">_tables</A> - <LI><A HREF="#_views">_views</A> - <LI><A HREF="#_get_sql_data">_get_sql_data</A> - <LI><A HREF="#_sql_type_INTERNAL_TYPE_LENGTH">_sql_type INTERNAL_TYPE LENGTH</A> - <LI><A HREF="#_column_info_TABLE">_column_info TABLE</A> - <LI><A HREF="#_primary_key_TABLE">_primary_key TABLE</A> - <LI><A HREF="#_unique_key_TABLE">_unique_key TABLE</A> - <LI><A HREF="#_foreign_key_TABLE">_foreign_key TABLE</A> - <LI><A HREF="#_get_users">_get_users</A> - <LI><A HREF="#_get_roles">_get_roles</A> - <LI><A HREF="#_get_all_grants">_get_all_grants</A> - <LI><A HREF="#_get_indexes_TABLE">_get_indexes TABLE</A> - <LI><A HREF="#_get_sequences">_get_sequences</A> - <LI><A HREF="#_get_views">_get_views</A> - <LI><A HREF="#_get_triggers">_get_triggers</A> - <LI><A HREF="#_get_functions">_get_functions</A> - <LI><A HREF="#_table_info">_table_info</A> + <LI><A HREF="#_init hash_options">_init HASH_OPTIONS</A></LI> + <LI><A HREF="#_grants">_grants</A></LI> + <LI><A HREF="#_sequences">_sequences</A></LI> + <LI><A HREF="#_triggers">_triggers</A></LI> + <LI><A HREF="#_functions">_functions</A></LI> + <LI><A HREF="#_packages">_packages</A></LI> + <LI><A HREF="#_tables">_tables</A></LI> + <LI><A HREF="#_views">_views</A></LI> + <LI><A HREF="#_get_sql_data">_get_sql_data</A></LI> + <LI><A HREF="#_get_data table">_get_data TABLE</A></LI> + <LI><A HREF="#_sql_type internal_type length precision scale">_sql_type INTERNAL_TYPE LENGTH PRECISION SCALE</A></LI> + <LI><A HREF="#_column_info table">_column_info TABLE</A></LI> + <LI><A HREF="#_primary_key table">_primary_key TABLE</A></LI> + <LI><A HREF="#_unique_key table">_unique_key TABLE</A></LI> + <LI><A HREF="#_foreign_key table">_foreign_key TABLE</A></LI> + <LI><A HREF="#_get_users">_get_users</A></LI> + <LI><A HREF="#_get_roles">_get_roles</A></LI> + <LI><A HREF="#_get_all_grants">_get_all_grants</A></LI> + <LI><A HREF="#_get_indexes table">_get_indexes TABLE</A></LI> + <LI><A HREF="#_get_sequences">_get_sequences</A></LI> + <LI><A HREF="#_get_views">_get_views</A></LI> + <LI><A HREF="#_alias_info">_alias_info</A></LI> + <LI><A HREF="#_get_triggers">_get_triggers</A></LI> + <LI><A HREF="#_get_functions">_get_functions</A></LI> + <LI><A HREF="#_get_packages">_get_packages</A></LI> + <LI><A HREF="#_table_info">_table_info</A></LI> </UL> - <LI><A HREF="#AUTHOR">AUTHOR</A> - <LI><A HREF="#COPYRIGHT">COPYRIGHT</A> - <LI><A HREF="#BUGS">BUGS</A> - <LI><A HREF="#SEE_ALSO">SEE ALSO</A> + <LI><A HREF="#author">AUTHOR</A></LI> + <LI><A HREF="#copyright">COPYRIGHT</A></LI> + <LI><A HREF="#bugs">BUGS</A></LI> + <LI><A HREF="#see also">SEE ALSO</A></LI> + <LI><A HREF="#acknowledgements">ACKNOWLEDGEMENTS</A></LI> </UL> <!-- INDEX END --> <HR> <P> -<H1><A NAME="NAME">NAME</A></H1> -<P> -Ora2Pg - Oracle to PostgreSQL database schema converter - +<H1><A NAME="name">NAME</A></H1> +<P>Ora2Pg - Oracle to PostgreSQL database schema converter</P> <P> <HR> -<H1><A NAME="SYNOPSIS">SYNOPSIS</A></H1> -<P> -<PRE> BEGIN { +<H1><A NAME="synopsis">SYNOPSIS</A></H1> +<PRE> + BEGIN { $ENV{ORACLE_HOME} = '/usr/local/oracle/oracle816'; - } -</PRE> -<P> -<PRE> use strict; -</PRE> -<P> -<PRE> use Ora2Pg; -</PRE> -<P> -<PRE> # Init the database connection + }</PRE> +<PRE> + use strict;</PRE> +<PRE> + use Ora2Pg;</PRE> +<PRE> + # Init the database connection my $dbsrc = 'dbi:Oracle:host=testdb.samse.fr;sid=TEST;port=1521'; my $dbuser = 'system'; - my $dbpwd = 'manager'; -</PRE> -<P> -<PRE> # Create an instance of the Ora2Pg perl module + my $dbpwd = 'manager';</PRE> +<PRE> + # Create an instance of the Ora2Pg perl module my $schema = new Ora2Pg ( datasource => $dbsrc, # Database DBD datasource user => $dbuser, # Database user password => $dbpwd, # Database password - ); -</PRE> -<P> -<PRE> # Create the POSTGRESQL representation of all objects in the database - $schema->export_schema("output.sql"); -</PRE> -<P> -<PRE> exit(0); -</PRE> -<P> -or if you only want to extract some tables: - -<P> -<PRE> # Create an instance of the Ora2Pg perl module + { + PrintError => 0, + RaiseError => 1, + AutoCommit => 0 + } + );</PRE> +<PRE> + # Create the POSTGRESQL representation of all objects in the database + $schema->export_schema("output.sql");</PRE> +<PRE> + exit(0);</PRE> +<P>or if you only want to extract some tables:</P> +<PRE> + # Create an instance of the Ora2Pg perl module my @tables = ('tab1', 'tab2', 'tab3'); my $schema = new Ora2Pg ( datasource => $dbsrc, # Database DBD datasource @@ -111,483 +114,366 @@ or if you only want to extract some tables: or # Tables to extract tables => [('tab1','tab2')], debug => 1 # To show somethings when running - ); -</PRE> -<P> -or if you only want to extract the 10 first tables: - -<P> -<PRE> # Create an instance of the Ora2Pg perl module + );</PRE> +<P>or if you only want to extract the 10 first tables:</P> +<PRE> + # Create an instance of the Ora2Pg perl module my $schema = new Ora2Pg ( datasource => $dbsrc, # Database DBD datasource user => $dbuser, # Database user password => $dbpwd, # Database password max => 10 # 10 first tables to extract - ); -</PRE> -<P> -or if you only want to extract tables 10 to 20: - -<P> -<PRE> # Create an instance of the Ora2Pg perl module + );</PRE> +<P>or if you only want to extract tables 10 to 20:</P> +<PRE> + # Create an instance of the Ora2Pg perl module my $schema = new Ora2Pg ( datasource => $dbsrc, # Database DBD datasource user => $dbuser, # Database user password => $dbpwd, # Database password min => 10, # Begin extraction at indice 10 max => 20 # End extraction at indice 20 - ); -</PRE> -<P> -To choose a particular schema just set the following option to your schema -name : - -<P> -<PRE> schema => 'APPS' -</PRE> -<P> -To know at which indices table can be found during extraction use the -option: - -<P> -<PRE> showtableid => 1 -</PRE> -<P> -To extract all views set the option type as follow: - -<P> -<PRE> type => 'VIEW' -</PRE> -<P> -To extract all grants set the option type as follow: - -<P> -<PRE> type => 'GRANT' -</PRE> -<P> -To extract all sequences set the option type as follow: - -<P> -<PRE> type => 'SEQUENCE' -</PRE> -<P> -To extract all triggers set the option type as follow: - -<P> -<PRE> type => 'TRIGGER' -</PRE> -<P> -To extract all functions set the option type as follow: - -<P> -<PRE> type => 'FUNCTION' -</PRE> -<P> -To extract all procedures set the option type as follow: - -<P> -<PRE> type => 'PROCEDURE' -</PRE> -<P> -Default is table schema extraction - -<P> -<PRE> type => 'TABLE' -</PRE> -<P> -<HR> -<H1><A NAME="DESCRIPTION">DESCRIPTION</A></H1> -<P> -Ora2Pg is a perl OO module used to export an Oracle database schema to a -PostgreSQL compatible schema. - -<P> -It simply connect to your Oracle database, extract its structure and -generate a SQL script that you can load into your PostgreSQL database. - -<P> -I'm not a Oracle DBA so I don't really know something about its internal + );</PRE> +<P>To choose a particular Oracle schema to export just set the following option +to your schema name:</P> +<PRE> + schema => 'APPS'</PRE> +<P>This schema definition can also be needed when you want to export data. If export +failed and complain that the table doesn't exists use this to prefix the table name +by the schema name.</P> +<P>To know at which indices tables can be found during extraction use the option:</P> +<PRE> + showtableid => 1</PRE> +<P>To extract all views set the type option as follow:</P> +<PRE> + type => 'VIEW'</PRE> +<P>To extract all grants set the type option as follow:</P> +<PRE> + type => 'GRANT'</PRE> +<P>To extract all sequences set the type option as follow:</P> +<PRE> + type => 'SEQUENCE'</PRE> +<P>To extract all triggers set the type option as follow:</P> +<PRE> + type => 'TRIGGER'</PRE> +<P>To extract all functions set the type option as follow:</P> +<PRE> + type => 'FUNCTION'</PRE> +<P>To extract all procedures set the type option as follow:</P> +<PRE> + type => 'PROCEDURE'</PRE> +<P>To extract all packages and body set the type option as follow:</P> +<PRE> + type => 'PACKAGE'</PRE> +<P>Default is table extraction</P> +<PRE> + type => 'TABLE'</PRE> +<P>To extract all data from table extraction as INSERT statement use:</P> +<PRE> + type => 'DATA'</PRE> +<P>To extract all data from table extraction as COPY statement use:</P> +<PRE> + type => 'COPY'</PRE> +<P>and data_limit => n to specify the max tuples to return. If you set +this options to 0 or nothing, no limitation are used. Additional option +'table', 'min' and 'max' can also be used.</P> +<P>When use of COPY or DATA you can export data by calling method:</P> +<P>$schema->export_data(``output.sql'');</P> +<P>Data are dumped to the given filename or to STDOUT with no argument. +You can also send these data directly to a PostgreSQL backend using + the following method:</P> +<P>$schema->send_to_pgdb($destdatasrc,$destuser,$destpasswd);</P> +<P>In this case you must call <CODE>export_data()</CODE> without argument after the +call to method send_to_pgdb().</P> +<P>If you set type to COPY and you want to dump data directly to a PG database, +you must call method send_to_pgdb but data will not be sent via DBD::Pg but +they will be load to the database using the psql command. Calling this method +is istill required to be able to extract database name, hostname and port +information. Edit the $PSQL variable to match the path of your psql +command (nothing to edit if psql is in your path).</P> +<P> +<HR> +<H1><A NAME="description">DESCRIPTION</A></H1> +<P>Ora2Pg is a perl OO module used to export an Oracle database schema +to a PostgreSQL compatible schema.</P> +<P>It simply connect to your Oracle database, extract its structure and +generate a SQL script that you can load into your PostgreSQL database.</P> +<P>I'm not a Oracle DBA so I don't really know something about its internal structure so you may find some incorrect things. Please tell me what is -wrong and what can be better. - -<P> -It currently dump the database schema (tables, views, sequences, indexes, -grants), with primary, unique and foreign keys into PostgreSQL syntax -without editing the SQL code generated. - -<P> -Functions, procedures and triggers PL/SQL code generated must be reviewed -to match the PostgreSQL syntax. Some usefull recommandation on porting -Oracle to PostgreSQL can be found at <A -HREF="http://techdocs.postgresql.org/">http://techdocs.postgresql.org/</A> -under the ``Converting from other Databases to PostgreSQL'' Oracle part. I -just notice one thing more is that the <CODE>trunc()</CODE> function in -Oracle is the same for number or date so be carefull when porting to -PostgreSQL to use <CODE>trunc()</CODE> for number and -<CODE>date_trunc()</CODE> for date. - -<P> -<HR> -<H1><A NAME="ABSTRACT">ABSTRACT</A></H1> -<P> -The goal of the Ora2Pg perl module is to cover all part needed to export an -Oracle database to a PostgreSQL database without other thing that provide -the connection parameters to the Oracle database. - -<P> -Features must include: - -<P> -<PRE> - Database schema export (tables, views, sequences, indexes), +wrong and what can be better.</P> +<P>It currently dump the database schema (tables, views, sequences, indexes, grants), +with primary, unique and foreign keys into PostgreSQL syntax without editing the +SQL code generated.</P> +<P>It now can dump Oracle data into PostgreSQL DB as online process. You can choose +what columns can be exported for each table.</P> +<P>Functions, procedures and triggers PL/SQL code generated must be reviewed to match +the PostgreSQL syntax. Some usefull recommandation on porting Oracle to PostgreSQL +can be found at <A HREF="http://techdocs.postgresql.org/">http://techdocs.postgresql.org/</A> under the ``Converting from other +Databases to PostgreSQL'' Oracle part. I just notice one thing more is that the +<CODE>trunc()</CODE> function in Oracle is the same for number or date so be carefull when +porting to PostgreSQL to use <CODE>trunc()</CODE> for number and <CODE>date_trunc()</CODE> for date.</P> +<P> +<HR> +<H1><A NAME="abstract">ABSTRACT</A></H1> +<P>The goal of the Ora2Pg perl module is to cover all part needed to export +an Oracle database to a PostgreSQL database without other thing that provide +the connection parameters to the Oracle database.</P> +<P>Features must include:</P> +<PRE> + - Database schema export (tables, views, sequences, indexes), with unique, primary and foreign key. - Grants/privileges export by user and group. - Table selection (by name and max table) export. - - Predefined functions/triggers/procedures export. - - Sql query converter (todo) - - Data export (todo) -</PRE> -<P> -My knowledge regarding database is really poor especially for Oracle so -contribution is welcome. - + - Predefined functions/triggers/procedures/packages export. + - Data export. + - Sql query converter (todo)</PRE> +<P>My knowledge regarding database is really poor especially for Oracle +so contribution is welcome.</P> <P> <HR> -<H1><A NAME="REQUIREMENT">REQUIREMENT</A></H1> -<P> -You just need the DBI and DBD::Oracle perl module to be installed - +<H1><A NAME="requirement">REQUIREMENT</A></H1> +<P>You just need the DBI, DBD::Pg and DBD::Oracle perl module to be installed</P> <P> <HR> -<H1><A NAME="PUBLIC_METHODS">PUBLIC METHODS</A></H1> +<H1><A NAME="public methods">PUBLIC METHODS</A></H1> <P> -<HR> -<H2><A NAME="new_HASH_OPTIONS">new HASH_OPTIONS</A></H2> -<P> -Creates a new Ora2Pg object. - -<P> -Supported options are: - -<P> -<PRE> - datasource : DBD datasource (required) +<H2><A NAME="new hash_options">new HASH_OPTIONS</A></H2> +<P>Creates a new Ora2Pg object.</P> +<P>Supported options are:</P> +<PRE> + - 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 + - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE, + TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE - debug : Print the current state of the parsing - tables : Extract only the given tables (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 -</PRE> + - data_limit : Number max of tuples to return during data extraction (default 10)</PRE> +<P>Attempt that this list should grow a little more because all initialization is +done by this way.</P> <P> -Attempt that this list should grow a little more because all initialization -is done by this way. - +<H2><A NAME="export_data filename">export_data FILENAME</A></H2> +<P>Print SQL data output to a filename or +to STDOUT if no file is given.</P> +<P>Must be used only if type option is set to DATA or COPY +=cut</P> +<P>sub export_data +{ + my ($self, $outfile) = @_;</P> +<PRE> + $self->_get_sql_data($outfile); +}</PRE> <P> -<HR> -<H2><A NAME="export_sql_FILENAME">export_sql FILENAME</A></H2> +<H2><A NAME="export_sql filename">export_sql FILENAME</A></H2> +<P>Print SQL conversion output to a filename or +simply return these data if no file is given.</P> <P> -Print SQL conversion output to a filename or to STDOUT if no file is given. - +<H2><A NAME="send_to_pgdb dest_datasrc dest_user dest_passwd">send_to_pgdb DEST_DATASRC DEST_USER DEST_PASSWD</A></H2> +<P>Open a DB handle to a PostgreSQL database</P> <P> -<HR> -<H1><A NAME="PRIVATE_METHODS">PRIVATE METHODS</A></H1> +<H2><A NAME="modify_struct table_name arrayof_fieldname">modify_struct TABLE_NAME ARRAYOF_FIELDNAME</A></H2> +<P>Modify a table structure during export. Only given fieldname +will be exported.</P> <P> <HR> -<H2><A NAME="_init_HASH_OPTIONS">_init HASH_OPTIONS</A></H2> +<H1><A NAME="private methods">PRIVATE METHODS</A></H1> <P> -Initialize a Ora2Pg object instance with a connexion to the Oracle -database. - +<H2><A NAME="_init hash_options">_init HASH_OPTIONS</A></H2> +<P>Initialize a Ora2Pg object instance with a connexion to the +Oracle database.</P> <P> -<HR> <H2><A NAME="_grants">_grants</A></H2> +<P>This function is used to retrieve all privilege information.</P> +<P>It extract all Oracle's ROLES to convert them as Postgres groups +and search all users associated to these roles.</P> +<P>Set the main hash $self->{groups}. +Set the main hash $self->{grantss}.</P> <P> -This function is used to retrieve all privilege information. - -<P> -It extract all Oracle's ROLES to convert them as Postgres groups and search -all users associated to these roles. - -<P> -Set the main hash $self->{groups}. Set the main hash -$self->{grantss}. - -<P> -<HR> <H2><A NAME="_sequences">_sequences</A></H2> +<P>This function is used to retrieve all sequences information.</P> +<P>Set the main hash $self->{sequences}.</P> <P> -This function is used to retrieve all sequences information. - -<P> -Set the main hash $self->{sequences}. - -<P> -<HR> <H2><A NAME="_triggers">_triggers</A></H2> +<P>This function is used to retrieve all triggers information.</P> +<P>Set the main hash $self->{triggers}.</P> <P> -This function is used to retrieve all triggers information. - -<P> -Set the main hash $self->{triggers}. - -<P> -<HR> <H2><A NAME="_functions">_functions</A></H2> +<P>This function is used to retrieve all functions information.</P> +<P>Set the main hash $self->{functions}.</P> <P> -This function is used to retrieve all functions information. - +<H2><A NAME="_packages">_packages</A></H2> +<P>This function is used to retrieve all packages information.</P> +<P>Set the main hash $self->{packages}.</P> <P> -Set the main hash $self->{functions}. - -<P> -<HR> <H2><A NAME="_tables">_tables</A></H2> -<P> -This function is used to retrieve all table information. - -<P> -Set the main hash of the database structure $self->{tables}. Keys are -the names of all tables retrieved from the current database. Each table -information compose an array associated to the table_info key as array -reference. In other way: - -<P> -<PRE> $self->{tables}{$class_name}{table_info} = [(OWNER,TYPE)]; -</PRE> -<P> -DBI TYPE can be TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL -TEMPORARY, ALIAS, SYNONYM or a data source specific type identifier. This -only extract TABLE type. - -<P> -It also get the following informations in the DBI object to affect the main -hash of the database structure : - -<P> -<PRE> $self->{tables}{$class_name}{field_name} = $sth->{NAME}; - $self->{tables}{$class_name}{field_type} = $sth->{TYPE}; -</PRE> -<P> -It also call these other private subroutine to affect the main hash of the -database structure : - -<P> -<PRE> @{$self->{tables}{$class_name}{column_info}} = $self->_column_info($class_name); +<P>This function is used to retrieve all table information.</P> +<P>Set the main hash of the database structure $self->{tables}. +Keys are the names of all tables retrieved from the current +database. Each table information compose an array associated +to the table_info key as array reference. In other way:</P> +<PRE> + $self->{tables}{$class_name}{table_info} = [(OWNER,TYPE)];</PRE> +<P>DBI TYPE can be TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, +ALIAS, SYNONYM or a data source specific type identifier. This only extract +TABLE type.</P> +<P>It also get the following informations in the DBI object to affect the +main hash of the database structure :</P> +<PRE> + $self->{tables}{$class_name}{field_name} = $sth->{NAME}; + $self->{tables}{$class_name}{field_type} = $sth->{TYPE};</PRE> +<P>It also call these other private subroutine to affect the main hash +of the database structure :</P> +<PRE> + @{$self->{tables}{$class_name}{column_info}} = $self->_column_info($class_name); @{$self->{tables}{$class_name}{primary_key}} = $self->_primary_key($class_name); @{$self->{tables}{$class_name}{unique_key}} = $self->_unique_key($class_name); - @{$self->{tables}{$class_name}{foreign_key}} = $self->_foreign_key($class_name); -</PRE> + @{$self->{tables}{$class_name}{foreign_key}} = $self->_foreign_key($class_name);</PRE> <P> -<HR> <H2><A NAME="_views">_views</A></H2> +<P>This function is used to retrieve all views information.</P> +<P>Set the main hash of the views definition $self->{views}. +Keys are the names of all views retrieved from the current +database values are the text definition of the views.</P> +<P>It then set the main hash as follow:</P> +<PRE> + # Definition of the view + $self->{views}{$table}{text} = $view_infos{$table};</PRE> <P> -This function is used to retrieve all views information. - -<P> -Set the main hash of the views definition $self->{views}. Keys are the -names of all views retrieved from the current database values are the text -definition of the views. - -<P> -It then set the main hash as follow: - -<P> -<PRE> # Definition of the view - $self->{views}{$table}{text} = $view_infos{$table}; -</PRE> -<P> -<HR> <H2><A NAME="_get_sql_data">_get_sql_data</A></H2> +<P>Returns a string containing the entire SQL Schema definition compatible with PostgreSQL</P> +<P> +<H2><A NAME="_get_data table">_get_data TABLE</A></H2> +<P>This function implements a Oracle-native data extraction.</P> +<P>Return a list of array reference containing the data</P> +<P> +<H2><A NAME="_sql_type internal_type length precision scale">_sql_type INTERNAL_TYPE LENGTH PRECISION SCALE</A></H2> +<P>This function return the PostgreSQL datatype corresponding to the +Oracle internal type.</P> +<P> +<H2><A NAME="_column_info table">_column_info TABLE</A></H2> +<P>This function implements a Oracle-native column information.</P> +<P>Return a list of array reference containing the following informations +for each column the given a table</P> +<P>[( + column name, + column type, + column length, + nullable column, + default value +)]</P> +<P> +<H2><A NAME="_primary_key table">_primary_key TABLE</A></H2> +<P>This function implements a Oracle-native primary key column +information.</P> +<P>Return a list of all column name defined as primary key +for the given table.</P> +<P> +<H2><A NAME="_unique_key table">_unique_key TABLE</A></H2> +<P>This function implements a Oracle-native unique key column +information.</P> +<P>Return a list of all column name defined as unique key +for the given table.</P> +<P> +<H2><A NAME="_foreign_key table">_foreign_key TABLE</A></H2> +<P>This function implements a Oracle-native foreign key reference +information.</P> +<P>Return a list of hash of hash of array reference. Ouuf! Nothing very difficult. +The first hash is composed of all foreign key name. The second hash just have +two key known as 'local' and remote' corresponding to the local table where the +foreign key is defined and the remote table where the key refer.</P> +<P>The foreign key name is composed as follow:</P> +<PRE> + 'local_table_name->remote_table_name'</PRE> +<P>Foreign key data consist in two array representing at the same indice the local +field and the remote field where the first one refer to the second. +Just like this:</P> +<PRE> + @{$link{$fkey_name}{local}} = @local_columns; + @{$link{$fkey_name}{remote}} = @remote_columns;</PRE> <P> -Returns a string containing the entire SQL Schema definition compatible -with PostgreSQL - -<P> -<HR> -<H2><A NAME="_sql_type_INTERNAL_TYPE_LENGTH">_sql_type INTERNAL_TYPE LENGTH</A></H2> -<P> -This function return the PostgreSQL datatype corresponding to the Oracle -internal type. - -<P> -<HR> -<H2><A NAME="_column_info_TABLE">_column_info TABLE</A></H2> -<P> -This function implements a Oracle-native column information. - -<P> -Return a list of array reference containing the following informations for -each column the given a table - -<P> -[( column name, column type, column length, nullable column, default value -)] - -<P> -<HR> -<H2><A NAME="_primary_key_TABLE">_primary_key TABLE</A></H2> -<P> -This function implements a Oracle-native primary key column information. - -<P> -Return a list of all column name defined as primary key for the given -table. - -<P> -<HR> -<H2><A NAME="_unique_key_TABLE">_unique_key TABLE</A></H2> -<P> -This function implements a Oracle-native unique key column information. - -<P> -Return a list of all column name defined as unique key for the given table. - -<P> -<HR> -<H2><A NAME="_foreign_key_TABLE">_foreign_key TABLE</A></H2> -<P> -This function implements a Oracle-native foreign key reference information. - -<P> -Return a list of hash of hash of array reference. Ouuf! Nothing very -difficult. The first hash is composed of all foreign key name. The second -hash just have two key known as 'local' and remote' corresponding to the -local table where the foreign key is defined and the remote table where the -key refer. - -<P> -The foreign key name is composed as follow: - -<P> -<PRE> 'local_table_name->remote_table_name' -</PRE> -<P> -Foreign key data consist in two array representing at the same indice the -local field and the remote field where the first one refer to the second. -Just like this: - -<P> -<PRE> @{$link{$fkey_name}{local}} = @local_columns; - @{$link{$fkey_name}{remote}} = @remote_columns; -</PRE> -<P> -<HR> <H2><A NAME="_get_users">_get_users</A></H2> +<P>This function implements a Oracle-native users information.</P> +<P>Return a hash of all users as an array.</P> <P> -This function implements a Oracle-native users information. - -<P> -Return a hash of all users as an array. - -<P> -<HR> <H2><A NAME="_get_roles">_get_roles</A></H2> +<P>This function implements a Oracle-native roles +information.</P> +<P>Return a hash of all groups (roles) as an array of associated users.</P> <P> -This function implements a Oracle-native roles information. - -<P> -Return a hash of all groups (roles) as an array of associated users. - -<P> -<HR> <H2><A NAME="_get_all_grants">_get_all_grants</A></H2> +<P>This function implements a Oracle-native user privilege +information.</P> +<P>Return a hash of all tables grants as an array of associated users.</P> <P> -This function implements a Oracle-native user privilege information. - +<H2><A NAME="_get_indexes table">_get_indexes TABLE</A></H2> +<P>This function implements a Oracle-native indexes information.</P> +<P>Return hash of array containing all unique index and a hash of +array of all indexes name which are not primary keys for the +given table.</P> <P> -Return a hash of all tables grants as an array of associated users. - -<P> -<HR> -<H2><A NAME="_get_indexes_TABLE">_get_indexes TABLE</A></H2> -<P> -This function implements a Oracle-native indexes information. - -<P> -Return hash of array containing all unique index and a hash of array of all -indexes name which are not primary keys for the given table. - -<P> -<HR> <H2><A NAME="_get_sequences">_get_sequences</A></H2> +<P>This function implements a Oracle-native sequences +information.</P> +<P>Return a hash of array of sequence name with MIN_VALUE, MAX_VALUE, +INCREMENT and LAST_NUMBER for the given table.</P> <P> -This function implements a Oracle-native sequences information. - -<P> -Return a hash of array of sequence name with MIN_VALUE, MAX_VALUE, -INCREMENT and LAST_NUMBER for the given table. - -<P> -<HR> <H2><A NAME="_get_views">_get_views</A></H2> +<P>This function implements a Oracle-native views information.</P> +<P>Return a hash of view name with the SQL query it is based on.</P> +<P> +<H2><A NAME="_alias_info">_alias_info</A></H2> +<P>This function implements a Oracle-native column information.</P> +<P>Return a list of array reference containing the following informations +for each alias of the given view</P> +<P>[( + column name, + column id +)]</P> <P> -This function implements a Oracle-native views information. - -<P> -Return a hash of view name with the SQL query it is based on. - -<P> -<HR> <H2><A NAME="_get_triggers">_get_triggers</A></H2> +<P>This function implements a Oracle-native triggers information.</P> +<P>Return an array of refarray of all triggers informations</P> <P> -This function implements a Oracle-native triggers information. - -<P> -Return an array of refarray of all triggers informations - -<P> -<HR> <H2><A NAME="_get_functions">_get_functions</A></H2> +<P>This function implements a Oracle-native functions information.</P> +<P>Return a hash of all function name with their PLSQL code</P> <P> -This function implements a Oracle-native functions information. - -<P> -Return a hash of all function name with their PLSQL code - +<H2><A NAME="_get_packages">_get_packages</A></H2> +<P>This function implements a Oracle-native packages information.</P> +<P>Return a hash of all function name with their PLSQL code</P> <P> -<HR> <H2><A NAME="_table_info">_table_info</A></H2> -<P> -This function retrieve all Oracle-native tables information. - -<P> -Return a handle to a DB query statement - +<P>This function retrieve all Oracle-native tables information.</P> +<P>Return a handle to a DB query statement</P> <P> <HR> -<H1><A NAME="AUTHOR">AUTHOR</A></H1> -<P> -Gilles Darold <<A -HREF="mailto:gilles@darold.net">gilles@darold.net</A>> - +<H1><A NAME="author">AUTHOR</A></H1> +<P>Gilles Darold <<A HREF="mailto:gilles@darold.net">gilles@darold.net</A>></P> <P> <HR> -<H1><A NAME="COPYRIGHT">COPYRIGHT</A></H1> -<P> -Copyright (c) 2001 Gilles Darold - All rights reserved. - -<P> -This program is free software; you can redistribute it and/or modify it -under the same terms as Perl itself. - +<H1><A NAME="copyright">COPYRIGHT</A></H1> +<P>Copyright (c) 2001 Gilles Darold - All rights reserved.</P> +<P>This program is free software; you can redistribute it and/or modify it under +the same terms as Perl itself.</P> <P> <HR> -<H1><A NAME="BUGS">BUGS</A></H1> -<P> -This perl module is in the same state as my knowledge regarding database, +<H1><A NAME="bugs">BUGS</A></H1> +<P>This perl module is in the same state as my knowledge regarding database, it can move and not be compatible with older version so I will do my best -to give you official support for Ora2Pg. Your volontee to help construct it -and your contribution are welcome. - +to give you official support for Ora2Pg. Your volontee to help construct +it and your contribution are welcome.</P> <P> <HR> -<H1><A NAME="SEE_ALSO">SEE ALSO</A></H1> +<H1><A NAME="see also">SEE ALSO</A></H1> +<P><EM>DBI</EM>, <A HREF="/DBD/Oracle.html">the DBD::Oracle manpage</A>, <A HREF="/DBD/Pg.html">the DBD::Pg manpage</A></P> <P> -<EM>DBI</EM>, <A HREF="/DBD/Oracle.html">DBD::Oracle</A> - - +<HR> +<H1><A NAME="acknowledgements">ACKNOWLEDGEMENTS</A></H1> +<P>Thanks to Jason Servetar who decided me to implement data extraction.</P> </BODY> diff --git a/contrib/oracle/ora2pg.pl b/contrib/oracle/ora2pg.pl index b33d60b84b3..3a15fb7bed4 100755 --- a/contrib/oracle/ora2pg.pl +++ b/contrib/oracle/ora2pg.pl @@ -8,7 +8,7 @@ # Copyright: Copyright (c) 2000 : Gilles Darold - All rights reserved - # Function : Script used to convert Oracle Database schema to PostgreSQL #------------------------------------------------------------------------------ -# Version : 1.0 +# Version : 1.1 #------------------------------------------------------------------------------ BEGIN { @@ -32,6 +32,9 @@ my $schema = new Ora2Pg ( debug => 1, # Verbose mode schema => 'APPS', # Extract only APPS schema type => 'TABLE', # Extract table +# type => 'PACKAGE', # Extract PACKAGE information +# type => 'DATA', # Extract data with output as INSERT statement +# type => 'COPY', # Extract data with output as COPY statement # type => 'VIEW', # Extract views # type => 'GRANT', # Extract privileges # type => 'SEQUENCE', # Extract sequences @@ -45,11 +48,25 @@ my $schema = new Ora2Pg ( # tables => [('TRIP_DATA')], # Foreign key # showtableid => 1, # Display only table indice during extraction # min => 1, # Extract begin at indice 3 -# max => 10 # Extract ended at indice 5 +# max => 10, # Extract ended at indice 5 +# data_limit => 1000, # Extract all data by dump of 1000 tuples +# data_limit => 0, # Extract all data in one pass. Be sure to have enougth memory ); -# Create the POSTGRESQL representation of all objects in the database -$schema->export_schema("output.sql"); +# Just export data of the following fields from table 's_txcot' +#$schema->modify_struct('s_txcot','dossier', 'rub', 'datapp'); + +# Function to use for extraction when type option is set to DATA or COPY + # Send exported data to a PostgreSQL database + #$schema->send_to_pgdb('dbi:Pg:dbname=template1;host=localhost;port=5432','test','test'); + + # Output the data extracted from Oracle DB to a file or to STDOUT if no argument. + # If you set the send_to_pgdb() method the output is given to PG database. See above + #$schema->export_data("output.sql"); + +# Function to use ifor extraction with other type + # Create the POSTGRESQL representation of all objects in the database + $schema->export_schema("output.sql"); exit(0); |