Hi Joan,
Can you set up a temporary holding table which is a closer match to the
ingres table?
Use copydb to create a copy.in script from the Ingres DB.
Change the copy.in script table name (take out indexes and unsupported
storage modifiers
Run the modified copy.in script into EA/Oracle.
Then try importing the data into the holding table.
Run some sql to transfer the data to the real table.
does this make sense?
Paul
-----Original Message-----
From: openroad-users-bounces (AT) peerlessit (DOT) com
[mailto
penroad-users-bounces (AT) peerlessit (DOT) com]On Behalf Of Berry, J (Joan)
Sent: Friday, 9 January 2009 12:42 AM
To: International OpenROAD Users; Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com; Chris M. Clark
Subject: Re: [Openroad-users] [Info-Ingres] Copying to flat
filecausingerrorthrough IngresGateway.
Hi Durwin,
I connected via an isql session to the ORACLE machine via the gateway and
ran the following :
1> copy table ral_new_staff() into 'c:\ral_new_staff.ingres'
(8 rows)
2> commit
3> copy table ral_new_staff() from 'c:\ral_new_staff.ingres'
(8 rows)
End of Request
And they both run ok... ummm
Tried using the copy into command on the Ingres Database on the production
server and this failed with the end of file encountered error.
So it's looking like it's the ORACLE NUMBER MAPPING or the actually
transfer of the binary object from Oracle to Ingres.
Not sure what to do now, I've pretty much run out of ideas L
Joan.
From: openroad-users-bounces (AT) peerlessit (DOT) com
[mailto
penroad-users-bounces (AT) peerlessit (DOT) com] On Behalf Of Durwin Wright
Sent: 08 January 2009 11:45
To: International OpenROAD Users; Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com; Chris M. Clark
Subject: Re: [Openroad-users] [Info-Ingres] Copying to flat
filecausingerror through IngresGateway.
I do not know if the following is your problem but if you do the following
you will get the E_CO002B in boty ingres and Enterprise Access
1.. Connect to a database
2.. Issue the command: copy table mytable() into 'mytable.dat'
3.. FTP mytable.dat as an ASCII file instead of a BINARY file to another
machine
4.. Connect to a database
5.. Create a new table called newtable that is identical to the old
mytable.
6.. Issue the following command: copy table newtable() from 'mytable.dat
'
You can also run into this ifi the architectures of the original client
platform and new platform are different. If this is the case then you can
connect via Ingres Net to the database on the new platform and do the 'copy
table into' and 'copy talbe from' on the same machine.
That might be a simple test that you can do (if anything that I said is
applicable). Try doing the COPY TABLE INTO and COPY TABLE FROM on the same
client and see if you get the same error. The only reason BINARY COPY
exists in the Enterprise Access product is to support Ingres Star. This is
how Ingres Star works.
There could also be a discrepancy between the ORACLE NUMBER MAPPING on the
source and target systems.
Thes are just a few ideas.
Durwin Wright | Sr. Architect | Durwin.Wright (AT) ingres (DOT) com | Ingres | 500
Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1
650-587-5523 | fax: +1 650-587-5550 | "Wag the Dog"
----------------------------------------------------------------------------
--
From: openroad-users-bounces (AT) peerlessit (DOT) com
[mailto
penroad-users-bounces (AT) peerlessit (DOT) com] On Behalf Of Berry, J (Joan)
Sent: Thursday, January 08, 2009 1:51 AM
To: Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com; Chris M. Clark; International
OpenROAD Users
Subject: Re: [Openroad-users] [Info-Ingres] Copying to flat file
causingerror through IngresGateway.
Hi Martin,
It appears the EA 2.6 only supports the Binary copy, so back to square one
and sorting the original error message. i.e. E_CO0024 COPY: Unexpected END
OF FILE while processing row 1.
E_CO002B COPY: 1 rows could not be processed.
E_CO002A COPY: Copy has been aborted.
And because of this 'end of file' error I went down the C0NL route.
The Cap value = FLOAT(126) and because I'm using Oracle this should be ok,
but I'm unsure L before the 'end of file error' I also get bad data value
for 3 of the columns, structural and column type differences are playing a
part from the in put into the flat file from oracle and the input into
Ingres.
Joan.
From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 08 January 2009 09:13
To: Berry, J (Joan)
Cc: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: RE: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Hi Joan,
The band column is nullable, yet the copy out and copy in commands do not
allow for nullable data. That's not a problem as long as there are no nulls
in the data.
Do you have any null data in the band column.or any of the others which
are nullable?
If so the copy commands will need to be altered along the lines of:
band=c0tab with null(']^NULL^[')
Also why does the ingres table use floats for fields which the oracle
table has as integer4's ?
Marty
From: Berry, J (Joan) [mailto:joan.berry (AT) stfc (DOT) ac.uk]
Sent: 07 January 2009 22:10
To: Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: RE: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Hi Martin,
Thanks for the suggestion, much appreciated. I've already tried c0tab and
c0comma, however I haven't checked the 4 character years, but I do have
floats as you suggest. This all started when the 'band' column changed (not
my idea !!) from numeric to alpha. A perfectly bulk copy was copy across the
gateway no problem.
This is the Oracle tables structure;-
PID integer 4 no
DLPID integer 4 yes
PWAID varchar 8 yes
TITLE varchar 30 yes
INITIALS varchar 15 yes
FORENAME varchar 30 yes
SURNAME varchar 100 yes
DATE_OF_BIRTH date yes
SEX varchar 1 yes
LABORATORY varchar 1 yes
ORG_UNIT varchar 4 yes
STRUCTURE varchar 18 yes
BAND varchar 15 yes
PAY_NO varchar 15 yes
BU_ID integer 4 yes
BU_NAME varchar 255 yes
START_DATE date yes
END_DATE date yes
FEDERAL_ID varchar 10 yes
STATUS varchar 50 yes
TIME_IMPORTED date yes
ADVERTISED_EMAIL_ADDRESS varchar 80 yes
And this is the Ingres Table structure:-
pid float 8 no
dlpid float 8 yes
pwaid varchar 8 yes
title varchar 30 yes
initials varchar 15 yes
forename varchar 30 yes
surname varchar 100 yes
date_of_birth date yes
sex varchar 1 yes
laboratory varchar 1 yes
org_unit varchar 4 yes
structure varchar 18 yes
band varchar 15 yes
pay_no varchar 15 yes
bu_id float 8 yes
bu_name varchar 255 yes
start_date date yes
end_date date yes
federal_id varchar 10 yes
status varchar 50 yes
time_imported date yes
advertised_email_address varchar 80 yes
Best Regards
Joan.
From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 07 January 2009 20:51
To: Ingres and related product discussion forum
Cc: Berry, J (Joan)
Subject: RE: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Hi Joan,
Yes this is exactly the right newsgroup to post Ingres questions to.
Try replacing all but the last 'c0NL' with 'c0tab'. The last 'c0NL' should
be 'c0nl'.
Note that this produces an ascii file. You need to be careful of dates and
floats with such files. Looking at the column names I'd guess you have many
dates in this data and probably no floats. Check the data in the file to
ensure you have 4 character years.
The command you've given to load the data uses a binary file not an ascii
file...ie they are incompatible.
If the download works without error then alter the copy for the upload to
read:
copy table ral_new_staff(
pid=c0tab,
dlpid=c0tab,
...etc...
) from 'C:ral_new_staff.txt';
Martin Bowes
----------------------------------------------------------------------------
--
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com on behalf of Berry, J
(Joan)
Sent: Wed 07/01/2009 16:52
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Dear All,
I wonder if anyone could shine some light on my problem. I have a table
in Oracle (10g) that I am copying out to a flat file. I have tried a bulk
copy which runs successfully through the gateway (EA 2.6/0605 (int.w32/00))
on windows 2003 server, however when I run the same command with the
following syntax :-
COPY table ral_new_staff (
pid = c0NL,
dlpid = c0NL,
pwaid = c0NL,
title = c0NL,
initials = c0NL,
forename = c0NL,
surname = c0NL,
date_of_birth = c0NL,
sex = c0NL,
laboratory = c0NL,
org_unit = c0NL,
structure = c0NL,
band = c0NL,
pay_no = c0NL,
bu_id = c0NL,
bu_name = c0NL,
start_date = c0NL,
end_date = c0NL,
federal_id = c0NL,
status = c0NL,
time_imported = c0NL,
advertised_email_address = c0NL
) into'c:/ral_new_staff.txt';
I get the following error.
E_GX0204 Syntax Error: Found a keyword or token other than that which was
expected
COPY: Invalid target expressions
(Wed Jan 07 16:29:45 2009)
If I run the copy into command on the Ingres database (II 2.6/0604
(int.w32/0000) on windows server 2003 I get the following message
1> copy table ral_new_staff()
from 'C:\schedules\FinanceProjectOwners\data\ral_new_st aff.txt'
E_CO0024 COPY: Unexpected END OF FILE while processing row 1.
E_CO002A COPY: Copy has been aborted.
Can anyone help? Am I one the right mailing list?
Joan.
__________________________________________________ ____________
Joan Berry - Administrative Database Team Leader
STFC - Science & Technology Facilities Council,
Corporate Information and Communications Technology,
Business Technology Unit, S10, Daresbury Laboratory,
Daresbury, Warrington, Cheshire, WA4 4AD.
Telephone : +44 (0) 1925 60(3412) , Mobile: 07920 703440
E-Mail : joan.berry (AT) stfc (DOT) ac.uk dmthelpdesk (AT) rl (DOT) ac.uk
Internal ADT Web: http://database7a.dl.ac.uk/ADTWeb/index.html
-------------------__________________________________________________ _____
_______
Re: [Info-Ingres] [Openroad-users] Copying to flatfilecausingerrorthrough IngresGateway. -
01-08-2009
, 09:46 AM
urwin.Wright (AT) ingres (DOT) com> | Ingres | 500 Arguello Street |
Linear Mode
