Solved

How do I fix Oracle KUP-01005 syntax error

Posted on 2014-01-08
13
4,298 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 76

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 76

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 73

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 76

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 73

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 73

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Fine Tune your automatic Updates for Ubuntu / Debian
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 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