How do I fix Oracle KUP-01005 syntax error

I created an external table
create table users_acct
( acct_login varchar2 (10 char),
acct_gov number (10,0),
acct_c number (10,0),
acct_ven varchar2 (50 char),
acct_date timestamp (6),
acct_timestamp timestamp(6)
)
organization external
(
type ORACLE_LOADER
default directory ACCT_DATA
access parameters
(
records delimited by "ACCT-EOR"
badfile BAD_FILES:'acct%a_%p.bad'
logfile ACCT_LOGS:'acct%a_%p.log'
fields terminated by "ACCT-EOR" optionally enclosed by '"' LRTRIM
MISSING FIELD VALUES ARE NULL
reject rows with all null fields
(acct_login ,
acct_gov ,
acct_c ,
acct_ven,
acct_date char(26) date_format timestamp mask yyyy-mm-dd hh24:mi:ss.ff6,
acct_timestamp char(26) date_format timestamp mask yyyy-mm-dd hh24:mi:ss.ff6
)
)
location
(
'accts.txt'
)
)REJECT LIMIT UNLIMITED
/

Table created.
SQL> select count(*) from users_acct;
select count(*) from users_acct
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "minussign": expecting one of: "comma, defaultif, nullif,)"
KUP-01007: at line 13 column 49

I used the same access parameters written the same way for another external table and did not get this error. Could someone help me resolve this issue?
sikyalaSenior Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
"found "minussign": expecting one of: "comma, defaultif, nullif"

My guess is you have a data problem.

Check the bad and log file for clues.
sikyalaSenior Database AdministratorAuthor Commented:
neither files were created. There is no log file or bad file
slightwv (䄆 Netminder) Commented:
Can you post some sample data?

I can try to reproduce it on my end.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sikyalaSenior Database AdministratorAuthor Commented:
ok
sdstuberCommented:
put your masks in double quotes


acct_date CHAR(26) DATE_FORMAT TIMESTAMP MASK "yyyy-mm-dd hh24:mi:ss.ff6",
acct_timestamp CHAR(26) DATE_FORMAT TIMESTAMP MASK "yyyy-mm-dd hh24:mi:ss.ff6"


the table will create without it (as you probably saw) but it won't load without them


From the utilities guide on DATE/TIMESTAMP formats...

http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm

The length must be enclosed in parentheses and the mask in quotation marks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sikyalaSenior Database AdministratorAuthor Commented:
ok i will try that

sample data

31969ACC-EOCleonardjACCT-EOC0ACCT-EOC0ACCT-EOCLevel 3 LLACCT-EOC 2012-03-08 00:00:00.000ACCT-EOC2012-03-08 10:39:25.000ACCT-EOR31970ACCT-EOCkcrumpACCT-EOC0ACCT-EOC0ACCT-EOCCentury ABCACCT-EOC2012-03-08 00:00:00.000ACCT-EOC2012-03-08 10:39:50.000ACCT-EOR
slightwv (䄆 Netminder) Commented:
I believe sdstuber is correct.  I'll wait for you to verify that before I do any testing.

FYI:  "ff6".  It looks like you only have 3 fractional seconds so it should be ff3
sikyalaSenior Database AdministratorAuthor Commented:
he was absolutely right
thanks
sdstuberCommented:
you multiple errors

Your field delimiter and your record delimiter are the same in your parameters

it looks like your are using ACCT-EOC for fields in the data

your first field is missing the T in ACCT-EOC

your parameters only specify 6 fields, but your sample data appears to have 7 fields per record
sikyalaSenior Database AdministratorAuthor Commented:
I looked at the external table I created successfully and I saw that it had double quotes
sikyalaSenior Database AdministratorAuthor Commented:
thank you so much
sdstuberCommented:
I created it like this... adding the extra first field and it worked


CREATE TABLE users_acct
( acct_id NUMBER(10,0),
acct_login VARCHAR2 (10 CHAR),
acct_gov NUMBER (10,0),
acct_c NUMBER (10,0),
acct_ven VARCHAR2 (50 CHAR),
acct_date TIMESTAMP (6),
acct_timestamp TIMESTAMP(6)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY acct_data
ACCESS PARAMETERS
(
RECORDS DELIMITED BY "ACCT-EOR"
BADFILE bad_files:'acct%a_%p.bad'
LOGFILE acct_logs:'acct%a_%p.log'
FIELDS TERMINATED BY "ACCT-EOC" OPTIONALLY ENCLOSED BY '"' LRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(acct_id  ,
acct_login ,
acct_gov ,
acct_c ,
acct_ven,
acct_date CHAR(26) DATE_FORMAT TIMESTAMP  MASK "yyyy-mm-dd hh24:mi:ss.ff6",
acct_timestamp CHAR(26)   DATE_FORMAT TIMESTAMP MASK  "yyyy-mm-dd hh24:mi:ss.ff6"
)
)
LOCATION
(
'accts.txt'
)
)REJECT LIMIT UNLIMITED
/


Note the ff3 vs ff6 didn't matter - both read the sample data correctly after fixing the first delimiter to include the "T"
sikyalaSenior Database AdministratorAuthor Commented:
that was a typo I submit question on a different machine and had to type everything in question. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.