Hi Xavier,



I have had a few responses from the user group, so what I have done is
altered the tables to the structures, which accounts for trapping the
'nulls'



The Oracle table looks like this:

CREATE TABLE "INGRES"."RAL_NEW_STAFF" ("PID" NUMBER DEFAULT 0 NOT

NULL, "DLPID" NUMBER DEFAULT 0, "PWAID" VARCHAR2(8 byte)

DEFAULT ' ', "TITLE" VARCHAR2(30 byte) DEFAULT ' ',

"INITIALS" VARCHAR2(15 byte) DEFAULT ' ', "FORENAME"

VARCHAR2(30 byte) DEFAULT ' ', "SURNAME" VARCHAR2(100 byte)

DEFAULT ' ', "DATE_OF_BIRTH" DATE DEFAULT '01-JAN-1900 ',

"SEX" VARCHAR2(1 byte) DEFAULT ' ', "LABORATORY" VARCHAR2(1

byte) DEFAULT ' ', "ORG_UNIT" VARCHAR2(4 byte) DEFAULT ' ',

"STRUCTURE" VARCHAR2(18 byte) DEFAULT ' ', "BAND" VARCHAR2(15

byte) DEFAULT ' ', "PAY_NO" VARCHAR2(15 byte) DEFAULT ' ',

"BU_ID" NUMBER DEFAULT 0, "BU_NAME" VARCHAR2(255 byte)

DEFAULT ' ', "START_DATE" DATE DEFAULT '01-JAN-1900 ',

"END_DATE" DATE DEFAULT '01-JAN-1900 ', "FEDERAL_ID"

VARCHAR2(10 byte) DEFAULT ' ', "STATUS" VARCHAR2(50 byte)

DEFAULT ' ', "TIME_IMPORTED" DATE DEFAULT '01-JAN-1900 ',

"ADVERTISED_EMAIL_ADDRESS" VARCHAR2(80 byte) DEFAULT ' ')

TABLESPACE "USERS" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS

255

STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING

MONITORING



And the Ingres table looks like this :-



Name: ral_new_staff

Owner: ingresdba

Created: 07/01/2009 12:20:18

Location: ii_database

Type: user table

Version: II2.6

Page size: 2048

Cache priority: 0

Alter table version: 0

Alter table totwidth: 755

Row width: 755

Number of rows: 0

Storage structure: heap

Compression: none

Duplicate Rows: allowed

Number of pages: 3

Overflow data pages: 0

Journaling: enabled

Base table for view: no

Permissions: none

Integrities: none

Optimizer statistics: none



Column Information:

Key

Column Name Type Length Nulls Defaults Seq

pid float 8 no no

dlpid float 8 yes yes

pwaid varchar 8 yes yes

title varchar 30 yes yes

initials varchar 15 yes yes

forename varchar 30 yes yes

surname varchar 100 yes yes

date_of_birth date yes yes

sex varchar 1 yes yes

laboratory varchar 1 yes yes

org_unit varchar 4 yes yes

structure varchar 18 yes yes

band varchar 15 yes yes

pay_no varchar 15 yes yes

bu_id float 8 yes yes

bu_name varchar 255 yes yes

start_date date yes yes

end_date date yes yes

federal_id varchar 10 yes yes

status varchar 50 yes yes

time_imported date yes yes

advertised_email_address varchar 80 yes yes



Secondary indexes: none



Does this help?



Joan.



From: srvcdesk (AT) ingres (DOT) com [mailto:srvcdesk (AT) ingres (DOT) com]
Sent: 07 January 2009 17:23
To: Berry, J (Joan)
Subject: Issue 133365 Email



Please do not reply to this notification. You can view/update the issue
by clicking on Issue 133365
<http://servicedesk.ingres.com/CAisd/...CH+FACTORY=iss
+SKIPLIST=1+QBE.EQ.id=432350> . Note, you will need to have an account
for using Ingres Corp Service Desk and may be prompted to log in prior
to viewing the issue.



Hello Joan,

Happy New Year 2009 !

Your mail had been forwarded to support Team that is the right place to
help you if you have problems or questions you're welcome !

I grabbed your issue at Level1 Support Europe.

First thing i saw is that if you are on a windows server the path of
your target file is incorrect :
copy .......
into'c:/ral_new_staff.txt';

on windows please can you try this :
copy (.....)
into 'c:\ral_new_staff.txt';

What is the exact release of Ingres + patch on server where you have
problem ?

Let me know if this help
Best regards.
Xavier Magnan
Technical Support Representative
Ingres Corporation
xavier.magnan (AT) ingres (DOT) com

PHONE 0800 91 72 12 and 2 for French
FAX +33 (0)1 72 75 72 99

www.ingres.com

This transmission is confidential and intended solely for the use of the
recipient named above. It may contain confidential, proprietary, or
legally privileged information. If you are not the intended recipient,
you are hereby notified that any unauthorized review, use, disclosure or
distribution is strictly prohibited. If you have received this
transmission in error, please contact the sender by reply e-mail and
delete the original transmission and all copies from your system.


Issue Summary/Title

Re: Copying to flat file causing error through Ingres Gateway.



Issue Description

----- Original Message -----
From: Berry, J (Joan)
To: Ingres Support
Sent: Wednesday, January 07, 2009 11:46 AM
Subject: Copying to flat file causing error through Ingres Gateway.


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