Ora2Pg question

From: "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Ora2Pg question
Date: 2003-09-23 20:12:05
Message-ID: 73309C2FDD95D11192E60008C7B1D5BB05FED8E5@snt452.corp.bcbsm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)darold(dot)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 => \(at)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

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2003-09-23 20:15:39 Re: Questions about my ifnull function
Previous Message scott.marlowe 2003-09-23 20:10:19 Re: About GPL and proprietary software