Thread: Oracle->Postgres Migration Problems.

I am trying to use the migration plugin to migrate my tables and data from
Orace 8i to Postgres 7.2 on Redhat 7.2

I'm doing a small test to see how well it works, but I'm not getting very
far. I have Oracle installed, and have a System DSN setup. If I pick a
standard table (varchars, numbers, dates) and do the conversion, I get:

An error occured at: 15/4/2002 2:24:42 PM:
9: Subscript out of range

Rolling back... Done.

I read through the mailing archive, and noticed that Dave had asked that
debugging turned on and the contents of the log window sent, so that's what
I've done (nothing else in there that I could find). I've just subscribed to
the mailing list, but I've looked to make sure I wasn't posting a topic that
had been answered earlier; but if I've missed it, I apologize.

The logs are below (note that I asked for lower-case conversion of all
tables, indexes, etc). I x'd out any connection info for security reasons.

BTW, a great tool, aside from this little glitch.

David


15/4/2002 2:24:41 PM - Entering pgSchema:pgServer.ExecSQL("BEGIN",
"Connection: Provider=MSDASQL.1;Extended
Properties="DRIVER={PostgreSQL};DATABASE=xxx;SERVER=192.168.12.137;PORT=5
432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0
;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKN
OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1;COMMLOG=1;OPT
IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=
1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_"")
15/4/2002 2:24:41 PM - SQL (xxxx): BEGIN
15/4/2002 2:24:41 PM - Creating table: BOATS
15/4/2002 2:24:41 PM - Entering Database Migration
Wizard:frmWizard.txtStatus_Change()
15/4/2002 2:24:42 PM - Entering Database Migration
Wizard:frmWizard.txtStatus_Change()
15/4/2002 2:24:42 PM - Entering pgSchema:pgServer.Property Get Databases()
15/4/2002 2:24:42 PM - Entering pgSchema:Databases.Property Get
Item("xxxx")
15/4/2002 2:24:42 PM - Entering pgSchema:pgDatabase.Execute("ROLLBACK",
0, -1)
15/4/2002 2:24:42 PM - Entering pgSchema:pgDatabase.dbConnect()
15/4/2002 2:24:42 PM - Entering pgSchema:pgServer.ExecSQL("ROLLBACK",
"Connection: Provider=MSDASQL.1;Extended
Properties="DRIVER={PostgreSQL};DATABASE=xxxx;SERVER=192.168.12.137;PORT=5
432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0
;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKN
OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1;COMMLOG=1;OPT
IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=
1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_"")
15/4/2002 2:24:42 PM - SQL (xxxx): ROLLBACK
15/4/2002 2:24:42 PM - Entering Database Migration
Wizard:frmWizard.txtStatus_Change()
15/4/2002 2:24:42 PM - Done - 1.54 Secs.

Hi David,

Do you have sufficient privileges on the Oracle system? I have seen both
Oracle & MS SQL Server fail to return column names in the past when the uid
had sufficient privileges to connect and see that a table existed, but not
to look at the table structure.

Regards, Dave.

> -----Original Message-----
> From: David Griffiths [mailto:dgriffiths@boats.com]
> Sent: 15 April 2002 22:33
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Oracle->Postgres Migration Problems.
>
>
> I am trying to use the migration plugin to migrate my tables
> and data from Orace 8i to Postgres 7.2 on Redhat 7.2
>
> I'm doing a small test to see how well it works, but I'm not
> getting very far. I have Oracle installed, and have a System
> DSN setup. If I pick a standard table (varchars, numbers,
> dates) and do the conversion, I get:
>
> An error occured at: 15/4/2002 2:24:42 PM:
> 9: Subscript out of range
>
> Rolling back... Done.
>
> I read through the mailing archive, and noticed that Dave had
> asked that debugging turned on and the contents of the log
> window sent, so that's what I've done (nothing else in there
> that I could find). I've just subscribed to the mailing list,
> but I've looked to make sure I wasn't posting a topic that
> had been answered earlier; but if I've missed it, I apologize.
>
> The logs are below (note that I asked for lower-case
> conversion of all tables, indexes, etc). I x'd out any
> connection info for security reasons.
>
> BTW, a great tool, aside from this little glitch.
>
> David
>
>
> 15/4/2002 2:24:41 PM - Entering pgSchema:pgServer.ExecSQL("BEGIN",
> "Connection: Provider=MSDASQL.1;Extended
> Properties="DRIVER={PostgreSQL};DATABASE=xxx;SERVER=192.168.12
.137;PORT=5
> 432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;S
> HOWOIDCOLUMN=0
> ;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SO
> CKET=4096;UNKN
> OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1
> ;COMMLOG=1;OPT
> IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNS
> ASLONGVARCHAR=
> 1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIX
> ES=dd_"")
> 15/4/2002 2:24:41 PM - SQL (xxxx): BEGIN
> 15/4/2002 2:24:41 PM - Creating table: BOATS
> 15/4/2002 2:24:41 PM - Entering Database Migration
> Wizard:frmWizard.txtStatus_Change()
> 15/4/2002 2:24:42 PM - Entering Database Migration
> Wizard:frmWizard.txtStatus_Change()
> 15/4/2002 2:24:42 PM - Entering pgSchema:pgServer.Property
> Get Databases() 15/4/2002 2:24:42 PM - Entering
> pgSchema:Databases.Property Get
> Item("xxxx")
> 15/4/2002 2:24:42 PM - Entering
> pgSchema:pgDatabase.Execute("ROLLBACK",
> 0, -1)
> 15/4/2002 2:24:42 PM - Entering
> pgSchema:pgDatabase.dbConnect() 15/4/2002 2:24:42 PM -
> Entering pgSchema:pgServer.ExecSQL("ROLLBACK",
> "Connection: Provider=MSDASQL.1;Extended
> Properties="DRIVER={PostgreSQL};DATABASE=xxxx;SERVER=192.168.1
2.137;PORT=5
> 432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;S
> HOWOIDCOLUMN=0
> ;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SO
> CKET=4096;UNKN
> OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1
> ;COMMLOG=1;OPT
> IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNS
> ASLONGVARCHAR=
> 1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIX
> ES=dd_"")
> 15/4/2002 2:24:42 PM - SQL (xxxx): ROLLBACK
> 15/4/2002 2:24:42 PM - Entering Database Migration
> Wizard:frmWizard.txtStatus_Change()
> 15/4/2002 2:24:42 PM - Done - 1.54 Secs.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an
> appropriate subscribe-nomail command to
> majordomo@postgresql.org so that your message can get through
> to the mailing list cleanly
>

Yup - I'm the DBA for the database, and was logging in with a user with the
DBA role assigned to them. I've also selected from the 'col' view on Oracle
to see the columns as that user.

David.

----- Original Message -----
From: "Dave Page" <dpage@vale-housing.co.uk>
To: "'David Griffiths'" <dgriffiths@boats.com>;
<pgadmin-support@postgresql.org>
Sent: Tuesday, April 16, 2002 12:59 AM
Subject: RE: [pgadmin-support] Oracle->Postgres Migration Problems.


> Hi David,
>
> Do you have sufficient privileges on the Oracle system? I have seen both
> Oracle & MS SQL Server fail to return column names in the past when the
uid
> had sufficient privileges to connect and see that a table existed, but not
> to look at the table structure.
>
> Regards, Dave.
>
> > -----Original Message-----
> > From: David Griffiths [mailto:dgriffiths@boats.com]
> > Sent: 15 April 2002 22:33
> > To: pgadmin-support@postgresql.org
> > Subject: [pgadmin-support] Oracle->Postgres Migration Problems.
> >
> >
> > I am trying to use the migration plugin to migrate my tables
> > and data from Orace 8i to Postgres 7.2 on Redhat 7.2
> >
> > I'm doing a small test to see how well it works, but I'm not
> > getting very far. I have Oracle installed, and have a System
> > DSN setup. If I pick a standard table (varchars, numbers,
> > dates) and do the conversion, I get:
> >
> > An error occured at: 15/4/2002 2:24:42 PM:
> > 9: Subscript out of range
> >
> > Rolling back... Done.
> >
> > I read through the mailing archive, and noticed that Dave had
> > asked that debugging turned on and the contents of the log
> > window sent, so that's what I've done (nothing else in there
> > that I could find). I've just subscribed to the mailing list,
> > but I've looked to make sure I wasn't posting a topic that
> > had been answered earlier; but if I've missed it, I apologize.
> >
> > The logs are below (note that I asked for lower-case
> > conversion of all tables, indexes, etc). I x'd out any
> > connection info for security reasons.
> >
> > BTW, a great tool, aside from this little glitch.
> >
> > David
> >
> >
> > 15/4/2002 2:24:41 PM - Entering pgSchema:pgServer.ExecSQL("BEGIN",
> > "Connection: Provider=MSDASQL.1;Extended
> > Properties="DRIVER={PostgreSQL};DATABASE=xxx;SERVER=192.168.12
> .137;PORT=5
> > 432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;S
> > HOWOIDCOLUMN=0
> > ;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SO
> > CKET=4096;UNKN
> > OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1
> > ;COMMLOG=1;OPT
> > IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNS
> > ASLONGVARCHAR=
> > 1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIX
> > ES=dd_"")
> > 15/4/2002 2:24:41 PM - SQL (xxxx): BEGIN
> > 15/4/2002 2:24:41 PM - Creating table: BOATS
> > 15/4/2002 2:24:41 PM - Entering Database Migration
> > Wizard:frmWizard.txtStatus_Change()
> > 15/4/2002 2:24:42 PM - Entering Database Migration
> > Wizard:frmWizard.txtStatus_Change()
> > 15/4/2002 2:24:42 PM - Entering pgSchema:pgServer.Property
> > Get Databases() 15/4/2002 2:24:42 PM - Entering
> > pgSchema:Databases.Property Get
> > Item("xxxx")
> > 15/4/2002 2:24:42 PM - Entering
> > pgSchema:pgDatabase.Execute("ROLLBACK",
> > 0, -1)
> > 15/4/2002 2:24:42 PM - Entering
> > pgSchema:pgDatabase.dbConnect() 15/4/2002 2:24:42 PM -
> > Entering pgSchema:pgServer.ExecSQL("ROLLBACK",
> > "Connection: Provider=MSDASQL.1;Extended
> > Properties="DRIVER={PostgreSQL};DATABASE=xxxx;SERVER=192.168.1
> 2.137;PORT=5
> > 432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;S
> > HOWOIDCOLUMN=0
> > ;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SO
> > CKET=4096;UNKN
> > OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1
> > ;COMMLOG=1;OPT
> > IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNS
> > ASLONGVARCHAR=
> > 1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIX
> > ES=dd_"")
> > 15/4/2002 2:24:42 PM - SQL (xxxx): ROLLBACK
> > 15/4/2002 2:24:42 PM - Entering Database Migration
> > Wizard:frmWizard.txtStatus_Change()
> > 15/4/2002 2:24:42 PM - Done - 1.54 Secs.
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an
> > appropriate subscribe-nomail command to
> > majordomo@postgresql.org so that your message can get through
> > to the mailing list cleanly
> >

Can you view the columns in the table on Oracle using an ODBC tool like
Microsoft Query?

Regards, Dave.

> -----Original Message-----
> From: David Griffiths [mailto:dgriffiths@boats.com]
> Sent: 16 April 2002 17:31
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Oracle->Postgres Migration Problems.
>
>
> Yup - I'm the DBA for the database, and was logging in with a
> user with the DBA role assigned to them. I've also selected
> from the 'col' view on Oracle to see the columns as that user.
>
> David.
>
> ----- Original Message -----
> From: "Dave Page" <dpage@vale-housing.co.uk>
> To: "'David Griffiths'" <dgriffiths@boats.com>;
> <pgadmin-support@postgresql.org>
> Sent: Tuesday, April 16, 2002 12:59 AM
> Subject: RE: [pgadmin-support] Oracle->Postgres Migration Problems.
>
>
> > Hi David,
> >
> > Do you have sufficient privileges on the Oracle system? I have seen
> > both Oracle & MS SQL Server fail to return column names in the past
> > when the
> uid
> > had sufficient privileges to connect and see that a table
> existed, but
> > not to look at the table structure.
> >
> > Regards, Dave.
> >
> > > -----Original Message-----
> > > From: David Griffiths [mailto:dgriffiths@boats.com]
> > > Sent: 15 April 2002 22:33
> > > To: pgadmin-support@postgresql.org
> > > Subject: [pgadmin-support] Oracle->Postgres Migration Problems.
> > >
> > >
> > > I am trying to use the migration plugin to migrate my tables and
> > > data from Orace 8i to Postgres 7.2 on Redhat 7.2
> > >
> > > I'm doing a small test to see how well it works, but I'm
> not getting
> > > very far. I have Oracle installed, and have a System DSN
> setup. If I
> > > pick a standard table (varchars, numbers,
> > > dates) and do the conversion, I get:
> > >
> > > An error occured at: 15/4/2002 2:24:42 PM:
> > > 9: Subscript out of range
> > >
> > > Rolling back... Done.
> > >
> > > I read through the mailing archive, and noticed that Dave
> had asked
> > > that debugging turned on and the contents of the log
> window sent, so
> > > that's what I've done (nothing else in there that I could find).
> > > I've just subscribed to the mailing list, but I've looked to make
> > > sure I wasn't posting a topic that had been answered
> earlier; but if
> > > I've missed it, I apologize.
> > >
> > > The logs are below (note that I asked for lower-case
> conversion of
> > > all tables, indexes, etc). I x'd out any connection info for
> > > security reasons.
> > >
> > > BTW, a great tool, aside from this little glitch.
> > >
> > > David
> > >
> > >
> > > 15/4/2002 2:24:41 PM - Entering pgSchema:pgServer.ExecSQL("BEGIN",
> > > "Connection: Provider=MSDASQL.1;Extended
> > > Properties="DRIVER={PostgreSQL};DATABASE=xxx;SERVER=192.168.12
> > .137;PORT=5
> > > 432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;S
> > > HOWOIDCOLUMN=0
> > > ;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SO
> > > CKET=4096;UNKN
> > > OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1
> > > ;COMMLOG=1;OPT
> > > IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNS
> > > ASLONGVARCHAR=
> > > 1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIX
> > > ES=dd_"")
> > > 15/4/2002 2:24:41 PM - SQL (xxxx): BEGIN
> > > 15/4/2002 2:24:41 PM - Creating table: BOATS
> > > 15/4/2002 2:24:41 PM - Entering Database Migration
> > > Wizard:frmWizard.txtStatus_Change()
> > > 15/4/2002 2:24:42 PM - Entering Database Migration
> > > Wizard:frmWizard.txtStatus_Change()
> > > 15/4/2002 2:24:42 PM - Entering pgSchema:pgServer.Property Get
> > > Databases() 15/4/2002 2:24:42 PM - Entering
> > > pgSchema:Databases.Property Get
> > > Item("xxxx")
> > > 15/4/2002 2:24:42 PM - Entering
> > > pgSchema:pgDatabase.Execute("ROLLBACK",
> > > 0, -1)
> > > 15/4/2002 2:24:42 PM - Entering
> > > pgSchema:pgDatabase.dbConnect() 15/4/2002 2:24:42 PM - Entering
> > > pgSchema:pgServer.ExecSQL("ROLLBACK",
> > > "Connection: Provider=MSDASQL.1;Extended
> > > Properties="DRIVER={PostgreSQL};DATABASE=xxxx;SERVER=192.168.1
> > 2.137;PORT=5
> > > 432;UID=postgres;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;S
> > > HOWOIDCOLUMN=0
> > > ;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SO
> > > CKET=4096;UNKN
> > > OWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=1
> > > ;COMMLOG=1;OPT
> > > IMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNS
> > > ASLONGVARCHAR=
> > > 1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIX
> > > ES=dd_"")
> > > 15/4/2002 2:24:42 PM - SQL (xxxx): ROLLBACK
> > > 15/4/2002 2:24:42 PM - Entering Database Migration
> > > Wizard:frmWizard.txtStatus_Change()
> > > 15/4/2002 2:24:42 PM - Done - 1.54 Secs.
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an
> appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an
> appropriate subscribe-nomail command to
> majordomo@postgresql.org so that your message can get through
> to the mailing list cleanly
>