PL/pgSQL: EXCEPTION NOSAVEPOINT

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 => \@{$Config{'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 {