Solved

How do I fix Oracle KUP-01005 syntax error

Posted on 2014-01-08
13
4,963 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

624 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