Solved

How do I fix Oracle KUP-01005 syntax error

Posted on 2014-01-08
13
4,447 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle function to insert records? 15 48
error in my cursor 5 41
oracle collections 2 22
Loading flat file data in tables 2 42
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

820 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