Ora2Pg question - Mailing list pgsql-general

From Johnson, Shaunn
Subject Ora2Pg question
Date
Msg-id 73309C2FDD95D11192E60008C7B1D5BB05FED8E5@snt452.corp.bcbsm.com
Whole thread Raw
List pgsql-general

Howdy:

Running PostgreSQL 7.2 and Oracle 9.2.0.1
on RedHat Linux 7.2.

I'm trying to use the Ora2pg.pl script that
I found on the PostgreSQL site.  I'm hoping
someone could point out what's wrong with
this.

I believe that I have made the correct adjustments
to the ora2pg.pl script that would let me connect
to Oracle and use the export function, but I get the
following when I run the script:

1) - a list of tables :
Retrieving table information...
ARRAY(0x833e2f0)
Duplicate entry found:  - user - PLAN_TABLE
Duplicate entry found:  - user - PLAN_TABLE
Duplicate entry found:  - user - PLAN_TABLE
Duplicate entry found:  - user - PLAN_TABLE
Duplicate entry found:  - user - PLAN_TABLE

2) - i finally get my target table, but then the script dies:

[snip]
Scanning SYS_ED_INP ( user SYS_ED_INP TABLE )...
Getting column info for table 'SYS_ED_INP' / owner 'user'
  MBR_ID => type:VARCHAR2 , length:11, precision:, scale:, nullable:Y
, default:
  CODE => type:VARCHAR2 , length:2, precision:, scale:, nullable:Y ,
default:
  PROC_1 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,default:
  PROC_2 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,default:
  PROC_3 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,default:
  DIAG_1 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,default:
  DIAG_2 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,default:
  DIAG_3 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,default:
  DIAG_4 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,
default:
DIAG_5 => type:VARCHAR2 , length:6, precision:, scale:, nullable:Y
,default:
  FROM_DT => type:DATE , length:7, precision:, scale:, nullable:Y ,
default:
  THRU_DT => type:DATE , length:7, precision:, scale:, nullable:Y ,
default:
  BILLINGPROVIDERNUM => type:VARCHAR2 , length:12, precision:,
scale:, nullable:Y , default:
  BILLINGPROVIDERNAME => type:VARCHAR2 , length:28, precision:,
scale:, nullable:Y , default:
  DIAGNOSISDESCRIPTION => type:VARCHAR2 , length:60, precision:,
scale:, nullable:Y , default:
  ADDED => type:DATE , length:7, precision:, scale:, nullable:Y ,default:
  ID => type:NUMBER , length:22, precision:, scale:, nullable:Y ,default:
  PRODUCT => type:VARCHAR2 , length:1, precision:, scale:, nullable:Y, default:
Duplicate entry found:  - user - USERBOTHLOGIN

Database handle destroyed without explicit disconnect.

[/snip]

questions:

1) am i supposed to put some disconnect statement
in the program?

2) i'm concerend about the list of tables it's finding saying
'duplicate' ... what is that really talking about and why
is it doing that?

3) how can i get the program to only export the data
of ONE table (I have the table created, but empty on
PostgreSQL already)?

NOTE:  this is what the program looks like now

[snip ora2pg.pl]
#!/usr/bin/perl
#---------------------------------------------------------------------------
---
# Project  : Oracle to Postgresql converter
# Name     : ora2pg.pl
# Language : perl, v5.6.1
# OS       : linux RedHat 7.3 kernel 2.4.18-17.7.xsmp
# Author   : Gilles Darold, gilles@darold.net
# Copyright: Copyright (c) 2000-2002 : Gilles Darold - All rights reserved
-
# Function : Script used to convert Oracle Database to PostgreSQL
#---------------------------------------------------------------------------
---
# Version  : 2.0
#---------------------------------------------------------------------------
---

BEGIN {
        $ENV{ORACLE_HOME} = '/var/opt/oracle/product/9.2.0';
}

use strict;

use Ora2Pg;

# Initialyze the database connection
my $dbsrc = 'dbi:Oracle:host=local.system.com;sid=test_inst';
my $dbuser = 'system';
my $dbpwd = 'manager';

# Create an instance of the Ora2Pg perl module
my @tables = ('SYS_ED_INP');
my $schema = new Ora2Pg (
       datasource => $dbsrc,           # Database DBD datasource
       user => $dbuser,                # Database user
       password => $dbpwd,             # Database password
       tables => \@tables,
       debug => 1                      # To show somethings when running
);

#### Function to use for extraction when type option is set to DATA or COPY

        # Send exported data directly to a PostgreSQL database
        $schema->send_to_pgdb('dbi:Pg:dbname=test_db','user');

        # Output the data extracted from Oracle DB to a file or to STDOUT
if
no
argument.
        #$schema->export_data("/var/tmp/output.sql");

#### Function to use for extraction of other type

        # Create the POSTGRESQL representation of all objects in the
database
        #$schema->export_schema("output.functions.sql");

exit(0);

[/snip ora2pg.pl]

Suggestions welcomed.  Thanks!

-X

pgsql-general by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: Questions about my ifnull function
Next
From: "scott.marlowe"
Date:
Subject: Re: About GPL and proprietary software