Solved

How do I fix Oracle KUP-01005 syntax error

Posted on 2014-01-08
13
4,707 Views
Last Modified: 2014-01-09
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?
0
Comment
Question by:sikyala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
13 Comments
 
LVL 77

Expert Comment

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

My guess is you have a data problem.

Check the bad and log file for clues.
0
 

Author Comment

by:sikyala
ID: 39765496
neither files were created. There is no log file or bad file
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39765511
Can you post some sample data?

I can try to reproduce it on my end.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sikyala
ID: 39765520
ok
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39765552
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.
0
 

Author Comment

by:sikyala
ID: 39765594
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
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39765601
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
0
 

Author Comment

by:sikyala
ID: 39765628
he was absolutely right
thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39765632
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
0
 

Author Comment

by:sikyala
ID: 39765634
I looked at the external table I created successfully and I saw that it had double quotes
0
 

Author Closing Comment

by:sikyala
ID: 39765636
thank you so much
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39765664
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"
0
 

Author Comment

by:sikyala
ID: 39769206
that was a typo I submit question on a different machine and had to type everything in question. Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question