Skip to Main Content
  • Questions

  • Oracle external table access fails with ORA-29913, ORA-29400, KUP-00554, KUP-01005: syntax error: found "comma": expecting one of: "colon, minussign, )"

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, NAGA.

Asked: June 12, 2019 - 8:19 am UTC

Last updated: June 19, 2019 - 11:13 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hi Tom

Below is my Code

CREATE TABLE "WEBELARG_ETL"."REP_ARG_EXT"
( "TRAN_ID" CHAR(1 BYTE),
"MRKT_ID" NUMBER(3,0),
"ACCT_NR" NUMBER(11,0),
"CREATN_LVL_NR" NUMBER(10,0),
"CREATN_LVL_CD" CHAR(7 BYTE),
"RPTG_LVL_NR" NUMBER(10,0),
"RPTG_LVL_CD" CHAR(7 BYTE),
"TRNDSTTR_IND" CHAR(1 BYTE),
"SLS_LDRSHP_STUS_TYP" CHAR(5 BYTE),
"SLS_LDR_ACCT_NR" CHAR(11 BYTE),
"ACCT_TYP" CHAR(6 BYTE),
"ACCT_STUS_CD" CHAR(5 BYTE),
"LOA_NR" NUMBER(10,0),
"BRTHDY_DT" NUMBER(14,0),
"APPT_DT" NUMBER(14,0),
"FRST_NM" CHAR(100 BYTE),
"MDDL_NM" CHAR(100 BYTE),
"LAST_NM" CHAR(100 BYTE),
"DYTM_PHON_NR" CHAR(15 BYTE),
"EVNG_PHON_NR" CHAR(15 BYTE),
"MOBILE_PHON_NR" CHAR(15 BYTE),
"FAX_PHON_NR" CHAR(15 BYTE),
"EMAIL_ADDR_TXT" CHAR(100 BYTE),
"CURR_SLS_CMPGN_NR" NUMBER(2,0),
"CURR_SLS_YR_NR" NUMBER(4,0),
"CURR_BAL_AMT" NUMBER(15,2),
"DUE_BAL_AMT" NUMBER(15,2),
"BTY_SHWCS_IND" CHAR(1 BYTE),
"PAST_DUE_BAL_AMT" NUMBER(15,2),
"PENDNG_CRDTS_AMT" NUMBER(15,2),
"LAST_PYMNT_AMT" NUMBER(15,2),
"MTHR_MDN_NM" CHAR(100 BYTE),
"PRSNL_IDNTFCTN_TXT" CHAR(50 BYTE),
"PRSNL_IDNTFCTN_TYP" CHAR(5 BYTE),
"CMPGN_ORD_CNT" NUMBER(4,0),
"TITL_CD" CHAR(6 BYTE),
"LANG_CD" CHAR(6 BYTE),
"REP_CLUB_CURRNT_POINT_CNT" NUMBER(11,0),
"REP_CLUB_PREV_POINT_CNT" NUMBER(11,0),
"REP_ACS_IND" CHAR(1 BYTE),
"SHPNG_FCLTY_CD" CHAR(8 BYTE),
"DYTM_PHON_STD_CD" CHAR(10 BYTE),
"EVNG_PHON_STD_CD" CHAR(10 BYTE),
"OFFC_PHON_NR" CHAR(15 BYTE),
"OFFC_PHON_STD_CD" CHAR(10 BYTE),
"MOBILE_PHON_STD_CD" CHAR(10 BYTE),
"BTY_CNTR_DLVRY_IND" CHAR(1 BYTE),
"MAX_CRDT_AMT" NUMBER(15,2),
"TOP_SLR_IND" CHAR(1 BYTE),
"CARR_CD" CHAR(15 BYTE),
"REP_STUS_CD" CHAR(5 BYTE),
"MAX_REG_ORD_CNT" NUMBER(3,0),
"DSTRBT_CNTR_CD" CHAR(5 BYTE),
"HM_DLVRY_IND" CHAR(1 BYTE),
"XPRES_ORD_ELGBLTY_IND" CHAR(1 BYTE),
"SLS_LDRSHP_NTWRK_CD" NUMBER(10,0),
"RTURN_AND_XCHNG_STOP_LIST_IND" CHAR(1 BYTE),
"RSN_CD_ELGBL_CD" CHAR(1 BYTE),
"LAST_PYMT_DT" NUMBER(14,0),
"PICK_UP_POINT_IND" CHAR(1 BYTE),
"MLPLN_CD" CHAR(1 BYTE),
"RMOVL_CMPGN_NR" NUMBER(6,0),
"RMOVL_DT" NUMBER(14,0),
"RMOVL_RSN_CD" NUMBER(2,0),
"RNSTMT_CMPGN_NR" NUMBER(4,0),
"RNSTMT_CMPGN_YR_NR" NUMBER(2,0),
"APPT_CMPGN_NR" NUMBER(4,0),
"APPT_CMPGN_YR_NR" NUMBER(2,0),
"AVLBL_CRDT_AMT" NUMBER(13,2),
"SPUR_CYCL_NR" CHAR(1 BYTE),
"BAD_DEBT_AMT" NUMBER(13,2),
"LDRSHP_LVL_1_CMPGN_YR_NR" NUMBER(6,0),
"LDRSHP_LOA_NR" NUMBER(5,0),
"LDRSHP_LVL_0_LOA_NR" NUMBER(5,0),
"LOYLTY_CARD_NR" NUMBER(5,0),
"STRTR_PACK_ORDRD_IND" CHAR(1 BYTE),
"NRDP_PRTCPTN_IND" CHAR(1 BYTE),
"REP_APPT_TYP" CHAR(2 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "ETLDIRARG_IN"
ACCESS PARAMETERS
( records delimited by "\n" characterset ee8mswin1250
load when ( (1:1)= 'U' OR (1:1)= 'I' OR (1:1)= 'D')
NOLOGFILE fields MISSING FIELD VALUES ARE NULL

(
TRAN_ID CHAR(1)
,MRKT_ID INTEGER EXTERNAL(3)
,ACCT_NR INTEGER EXTERNAL(11)
,CREATN_LVL_NR INTEGER EXTERNAL(10)
,CREATN_LVL_CD CHAR(7)
,RPTG_LVL_NR INTEGER EXTERNAL(10)
,RPTG_LVL_CD CHAR(7)
,TRNDSTTR_IND CHAR(1)
,SLS_LDRSHP_STUS_TYP CHAR(5)
,SLS_LDR_ACCT_NR CHAR(11)
,ACCT_TYP CHAR(6)
,ACCT_STUS_CD CHAR(5)
,LOA_NR INTEGER EXTERNAL(10)
,BRTHDY_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,APPT_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,FRST_NM CHAR(100)
,MDDL_NM CHAR(100)
,LAST_NM CHAR(100)
,DYTM_PHON_NR CHAR(15)
,EVNG_PHON_NR CHAR(15)
,MOBILE_PHON_NR CHAR(15)
,FAX_PHON_NR CHAR(15)
,EMAIL_ADDR_TXT CHAR(100)
,CURR_SLS_CMPGN_NR INTEGER EXTERNAL(2)
,CURR_SLS_YR_NR INTEGER EXTERNAL(4)
,CURR_BAL_AMT position(15,2)
,DUE_BAL_AMT position(15,2)
,BTY_SHWCS_IND CHAR(1)
,PAST_DUE_BAL_AMT position(15,2)
,PENDNG_CRDTS_AMT position(15,2)
,LAST_PYMNT_AMT position(15,2)
,MTHR_MDN_NM CHAR(100)
,PRSNL_IDNTFCTN_TXT CHAR(50)
,PRSNL_IDNTFCTN_TYP CHAR(5)
,CMPGN_ORD_CNT INTEGER EXTERNAL(4)
,TITL_CD CHAR(6)
,LANG_CD CHAR(6)
,REP_CLUB_CURRNT_POINT_CNT INTEGER EXTERNAL(11)
,REP_CLUB_PREV_POINT_CNT INTEGER EXTERNAL(11)
,REP_ACS_IND CHAR(1)
,SHPNG_FCLTY_CD CHAR(8)
,DYTM_PHON_STD_CD CHAR(10)
,EVNG_PHON_STD_CD CHAR(10)
,OFFC_PHON_NR CHAR(15)
,OFFC_PHON_STD_CD CHAR(10)
,MOBILE_PHON_STD_CD CHAR(10)
,BTY_CNTR_DLVRY_IND CHAR(1)
,MAX_CRDT_AMT position(15,2)
,TOP_SLR_IND CHAR(1)
,CARR_CD CHAR(15)
,REP_STUS_CD CHAR(5)
,MAX_REG_ORD_CNT INTEGER EXTERNAL(3)
,DSTRBT_CNTR_CD CHAR(5)
,HM_DLVRY_IND CHAR(1)
,XPRES_ORD_ELGBLTY_IND CHAR(1)
,SLS_LDRSHP_NTWRK_CD INTEGER EXTERNAL(10)
,RTURN_AND_XCHNG_STOP_LIST_IND CHAR(1)
,RSN_CD_ELGBL_CD CHAR(1)
,LAST_PYMT_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,PICK_UP_POINT_IND CHAR(1)
,MLPLN_CD CHAR(1)
,RMOVL_CMPGN_NR INTEGER EXTERNAL(6)
,RMOVL_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,RMOVL_RSN_CD INTEGER EXTERNAL(2)
,RNSTMT_CMPGN_NR INTEGER EXTERNAL(4)
,RNSTMT_CMPGN_YR_NR INTEGER EXTERNAL(2)
,APPT_CMPGN_NR INTEGER EXTERNAL(4)
,APPT_CMPGN_YR_NR INTEGER EXTERNAL(2)
,AVLBL_CRDT_AMT position(13,2)
,SPUR_CYCL_NR CHAR(1)
,BAD_DEBT_AMT position(13,2)
,LDRSHP_LVL_1_CMPGN_YR_NR INTEGER EXTERNAL(6)
,LDRSHP_LOA_NR INTEGER EXTERNAL(5)
,LDRSHP_LVL_0_LOA_NR INTEGER EXTERNAL(5)
,LOYLTY_CARD_NR INTEGER EXTERNAL(5)
,STRTR_PACK_ORDRD_IND CHAR(1)
,NRDP_PRTCPTN_IND CHAR(1)
,REP_APPT_TYP CHAR(2)
)
column transforms ( REP_ACS_IND from constant "Y",
SHPNG_FCLTY_CD from constant "0")
)
LOCATION
( "ETLDIRARG_IN":'REP.DAT'
)
)
REJECT LIMIT UNLIMITED ;




when select the data's from external table it shows below error
---------------------------------------------------------------

select * from rep_arg_ext

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 "comma": expecting one of: "colon, minussign, )"
KUP-01007: at line 31 column 44
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.


But I find below error in log when new .DAT file loaded
---------------------------------------------------------------
*** REP Error Log ***2019.06.07 01:30:28

REP Import process started 2019.06.07 01:30:28

ORA-06550: line 186, column 794:
PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got N

REP Import process completed 2019.06.07 01:30:28




Please Help to solve this issue I stuck in this issue a long ago

Thanks a Lot

and Chris said...

The problem's here:

 position(15,2)


You need to use colons to specify positions!

 position(15:2)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Need more assistance on same issue

NAGA A P, June 15, 2019 - 2:46 am UTC

Hi

When select * from external_table no error after changing
from position(15,2) to position(15:2)

But the log file it shows below error while loading .DAT file

ORA-06550: line 186, column 794:
PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got N

In my external table code not 186 line

and in my code I changed below columns

before change the column name in QA:
REP_ACS_IND CHAR(1)
SHPNG_FCLTY_CD CHAR(8)

After change the column name in QA:
REP_ACS_IND1 CHAR(1)
SHPNG_FCLTY_CD1 CHAR(8)

kindly respond as soon as possible

Thanks
NAGA

Chris Saxon
June 17, 2019 - 8:48 am UTC

PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got N

For some reason your code is expecting a DATE. But it's getting (presumably...) a NUMBER.

I've no idea why this is. I can't even see a line 186 in the code provided.

Bit step ahead to solve issue

NAGA A P, June 19, 2019 - 5:07 am UTC

Hi

I corrected error with your guidance, But only 2 records were updated out of 17533 records

I expect your reply as soon as possible

updated code:-
--------------

CREATE TABLE "WEBELARG_ETL"."REP_ARG_EXT"
( "TRAN_ID" CHAR(1 BYTE),
"MRKT_ID" NUMBER(3,0),
"ACCT_NR" NUMBER(11,0),
"CREATN_LVL_NR" NUMBER(10,0),
"CREATN_LVL_CD" CHAR(7 BYTE),
"RPTG_LVL_NR" NUMBER(10,0),
"RPTG_LVL_CD" CHAR(7 BYTE),
"TRNDSTTR_IND" CHAR(1 BYTE),
"SLS_LDRSHP_STUS_TYP" CHAR(5 BYTE),
"SLS_LDR_ACCT_NR" CHAR(11 BYTE),
"ACCT_TYP" CHAR(6 BYTE),
"ACCT_STUS_CD" CHAR(5 BYTE),
"LOA_NR" NUMBER(10,0),
"BRTHDY_DT" DATE,
"APPT_DT" DATE,
"FRST_NM" CHAR(100 BYTE),
"MDDL_NM" CHAR(100 BYTE),
"LAST_NM" CHAR(100 BYTE),
"DYTM_PHON_NR" CHAR(15 BYTE),
"EVNG_PHON_NR" CHAR(15 BYTE),
"MOBILE_PHON_NR" CHAR(15 BYTE),
"FAX_PHON_NR" CHAR(15 BYTE),
"EMAIL_ADDR_TXT" CHAR(100 BYTE),
"CURR_SLS_CMPGN_NR" NUMBER(2,0),
"CURR_SLS_YR_NR" NUMBER(4,0),
"CURR_BAL_AMT" NUMBER(15,2),
"DUE_BAL_AMT" NUMBER(15,2),
"BTY_SHWCS_IND" CHAR(1 BYTE),
"PAST_DUE_BAL_AMT" NUMBER(15,2),
"PENDNG_CRDTS_AMT" NUMBER(15,2),
"LAST_PYMNT_AMT" NUMBER(15,2),
"MTHR_MDN_NM" CHAR(100 BYTE),
"PRSNL_IDNTFCTN_TXT" CHAR(50 BYTE),
"PRSNL_IDNTFCTN_TYP" CHAR(5 BYTE),
"CMPGN_ORD_CNT" NUMBER(4,0),
"TITL_CD" CHAR(6 BYTE),
"LANG_CD" CHAR(6 BYTE),
"REP_CLUB_CURRNT_POINT_CNT" NUMBER(11,0),
"REP_CLUB_PREV_POINT_CNT" NUMBER(11,0),
"REP_ACS_IND" CHAR(1 BYTE),
"SHPNG_FCLTY_CD" CHAR(8 BYTE),
"DYTM_PHON_STD_CD" CHAR(10 BYTE),
"EVNG_PHON_STD_CD" CHAR(10 BYTE),
"OFFC_PHON_NR" CHAR(15 BYTE),
"OFFC_PHON_STD_CD" CHAR(10 BYTE),
"MOBILE_PHON_STD_CD" CHAR(10 BYTE),
"BTY_CNTR_DLVRY_IND" CHAR(1 BYTE),
"MAX_CRDT_AMT" NUMBER(15,2),
"TOP_SLR_IND" CHAR(1 BYTE),
"CARR_CD" CHAR(15 BYTE),
"REP_STUS_CD" CHAR(5 BYTE),
"MAX_REG_ORD_CNT" NUMBER(3,0),
"DSTRBT_CNTR_CD" CHAR(5 BYTE),
"HM_DLVRY_IND" CHAR(1 BYTE),
"XPRES_ORD_ELGBLTY_IND" CHAR(1 BYTE),
"SLS_LDRSHP_NTWRK_CD" NUMBER(10,0),
"RTURN_AND_XCHNG_STOP_LIST_IND" CHAR(1 BYTE),
"RSN_CD_ELGBL_CD" CHAR(1 BYTE),
"LAST_PYMT_DT" DATE,
"PICK_UP_POINT_IND" CHAR(1 BYTE),
"MLPLN_CD" CHAR(1 BYTE),
"RMOVL_CMPGN_NR" NUMBER(6,0),
"RMOVL_DT" DATE,
"RMOVL_RSN_CD" NUMBER(2,0),
"RNSTMT_CMPGN_NR" NUMBER(4,0),
"RNSTMT_CMPGN_YR_NR" NUMBER(2,0),
"APPT_CMPGN_NR" NUMBER(4,0),
"APPT_CMPGN_YR_NR" NUMBER(2,0),
"AVLBL_CRDT_AMT" NUMBER(13,2),
"SPUR_CYCL_NR" CHAR(1 BYTE),
"BAD_DEBT_AMT" NUMBER(13,2),
"LDRSHP_LVL_1_CMPGN_YR_NR" NUMBER(6,0),
"LDRSHP_LOA_NR" NUMBER(5,0),
"LDRSHP_LVL_0_LOA_NR" NUMBER(5,0),
"LOYLTY_CARD_NR" NUMBER(5,0),
"STRTR_PACK_ORDRD_IND" CHAR(1 BYTE),
"NRDP_PRTCPTN_IND" CHAR(1 BYTE),
"REP_APPT_TYP" CHAR(2 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "ETLDIRARG_IN"
ACCESS PARAMETERS
( records delimited by "\n" characterset ee8mswin1250
load when ( (1:1)= 'U' OR (1:1)= 'I' OR (1:1)= 'D')
NOLOGFILE fields MISSING FIELD VALUES ARE NULL

(
TRAN_ID CHAR(1)
,MRKT_ID INTEGER EXTERNAL(3)
,ACCT_NR INTEGER EXTERNAL(11)
,CREATN_LVL_NR INTEGER EXTERNAL(10)
,CREATN_LVL_CD CHAR(7)
,RPTG_LVL_NR INTEGER EXTERNAL(10)
,RPTG_LVL_CD CHAR(7)
,TRNDSTTR_IND CHAR(1)
,SLS_LDRSHP_STUS_TYP CHAR(5)
,SLS_LDR_ACCT_NR CHAR(11)
,ACCT_TYP CHAR(6)
,ACCT_STUS_CD CHAR(5)
,LOA_NR INTEGER EXTERNAL(10)
,BRTHDY_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,APPT_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,FRST_NM CHAR(100)
,MDDL_NM CHAR(100)
,LAST_NM CHAR(100)
,DYTM_PHON_NR CHAR(15)
,EVNG_PHON_NR CHAR(15)
,MOBILE_PHON_NR CHAR(15)
,FAX_PHON_NR CHAR(15)
,EMAIL_ADDR_TXT CHAR(100)
,CURR_SLS_CMPGN_NR INTEGER EXTERNAL(2)
,CURR_SLS_YR_NR INTEGER EXTERNAL(4)
,CURR_BAL_AMT position(582:597)
,DUE_BAL_AMT position(598:613)
,BTY_SHWCS_IND CHAR(1)
,PAST_DUE_BAL_AMT position(615:630)
,PENDNG_CRDTS_AMT position(631:646)
,LAST_PYMNT_AMT position(647:662)
,MTHR_MDN_NM CHAR(100)
,PRSNL_IDNTFCTN_TXT CHAR(50)
,PRSNL_IDNTFCTN_TYP CHAR(5)
,CMPGN_ORD_CNT INTEGER EXTERNAL(4)
,TITL_CD CHAR(6)
,LANG_CD CHAR(6)
,REP_CLUB_CURRNT_POINT_CNT INTEGER EXTERNAL(11)
,REP_CLUB_PREV_POINT_CNT INTEGER EXTERNAL(11)
,REP_ACS_IND1 CHAR(1)
,SHPNG_FCLTY_CD1 CHAR(8)
,DYTM_PHON_STD_CD CHAR(10)
,EVNG_PHON_STD_CD CHAR(10)
,OFFC_PHON_NR CHAR(15)
,OFFC_PHON_STD_CD CHAR(10)
,MOBILE_PHON_STD_CD CHAR(10)
,BTY_CNTR_DLVRY_IND CHAR(1)
,MAX_CRDT_AMT position(921:936)
,TOP_SLR_IND CHAR(1)
,CARR_CD CHAR(15)
,REP_STUS_CD CHAR(5)
,MAX_REG_ORD_CNT INTEGER EXTERNAL(3)
,DSTRBT_CNTR_CD CHAR(5)
,HM_DLVRY_IND CHAR(1)
,XPRES_ORD_ELGBLTY_IND CHAR(1)
,SLS_LDRSHP_NTWRK_CD INTEGER EXTERNAL(10)
,RTURN_AND_XCHNG_STOP_LIST_IND CHAR(1)
,RSN_CD_ELGBL_CD CHAR(1)
,LAST_PYMT_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,PICK_UP_POINT_IND CHAR(1)
,MLPLN_CD CHAR(1)
,RMOVL_CMPGN_NR INTEGER EXTERNAL(6)
,RMOVL_DT CHAR(14) date_format DATE mask "yyyymmddhh24miss"
,RMOVL_RSN_CD INTEGER EXTERNAL(2)
,RNSTMT_CMPGN_NR INTEGER EXTERNAL(4)
,RNSTMT_CMPGN_YR_NR INTEGER EXTERNAL(2)
,APPT_CMPGN_NR INTEGER EXTERNAL(4)
,APPT_CMPGN_YR_NR INTEGER EXTERNAL(2)
,AVLBL_CRDT_AMT position(1030:1043)
,SPUR_CYCL_NR CHAR(1)
,BAD_DEBT_AMT position(1045:1058)
,LDRSHP_LVL_1_CMPGN_YR_NR INTEGER EXTERNAL(6)
,LDRSHP_LOA_NR INTEGER EXTERNAL(5)
,LDRSHP_LVL_0_LOA_NR INTEGER EXTERNAL(5)
,LOYLTY_CARD_NR INTEGER EXTERNAL(5)
,STRTR_PACK_ORDRD_IND CHAR(1)
,NRDP_PRTCPTN_IND CHAR(1)
,REP_APPT_TYP CHAR(2)
)
column transforms ( REP_ACS_IND from constant "Y",
SHPNG_FCLTY_CD from constant "0")
)
LOCATION
( "ETLDIRARG_IN":'REP.DAT'
)
)
REJECT LIMIT UNLIMITED ;



log file:-
----------
*** REP Error Log ***2019.06.19 00:32:33

REP Import process started 2019.06.19 00:32:33

2019/06/19 00:32:34 REP 2 records are bulk merged.
2019/06/19 00:32:34 REP 0 records are loop merged.
2019/06/19 00:32:34 REP 0 records are skip merged.
2019/06/19 00:32:34 REP 0 records are skipped.

Bad file exists. Possible error records.
Please check the corresponding log/bad file.
REP Import process completed 2019.06.19 00:32:34

Chris Saxon
June 19, 2019 - 11:13 am UTC

I see no update statements here... How can I help effectively without a complete working example?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.