Home » SQL & PL/SQL » SQL & PL/SQL » Error while creating EXT Tables (11g)
Error while creating EXT Tables [message #558669] Mon, 25 June 2012 12:49 Go to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Hello,

I am trying to create an EXT table but is constantly having the following problem, not sure why. I have done a few checks and used the scripts used as a standard but still is experiencing an error.
Can you please help?
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: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 9 column 1



The Table creation script is
 

CREATE TABLE TABLE1_EXT
(
  BUSINESS_DATE     VARCHAR2(50 BYTE),
  CIN_CODE          VARCHAR2(50 BYTE),
  CIS_CODE          VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY CORPREPRECON_DIR
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 
                 BADFILE CORPREPRECON_DIR:'test.bad' 
                 LOGFILE CORPREPRECON_DIR:'test.log' 
                SKIP 1 
                 FIELDS 
                 TERMINATED BY '|' 
                 MISSING FIELD VALUES ARE NULL 
                ( 
--                  BUSINESS_DATE position (1:8),
--                  CIN_CODE POSITION (10:19),
--                  CIS_CODE POSITION (21:27)
                  BusinessDate varchar2(50) 
                  ,CIN varchar2(50)  
                  ,CISCode varchar2(50) 
                ) 
                              )
     LOCATION (CORPREPRECON_DIR:'test.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


And the data used as a sample is
BusinessDate|CIN|CISCode
19062012|1000026697|CTTGLIE
19062012|1000079416|Z29MBGB


Thanks in advance.
S
Re: Error while creating EXT Tables [message #558670 is a reply to message #558669] Mon, 25 June 2012 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 26397
Registered: January 2009
Location: SoCal
Senior Member
>KUP-01005: syntax error: found "minussign": expecting one of: "double-quoted-string, identifier, single-quoted-string"
Why does "minussign" *NOT* appear anywhere else in what you posted?

BTW, 11g is merely a Marketing label & not a real Oracle Version number to 4 decimal places
Re: Error while creating EXT Tables [message #558671 is a reply to message #558670] Mon, 25 June 2012 12:57 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
that is what is killing me, I believe it is a generic message than anything else?
Re: Error while creating EXT Tables [message #558672 is a reply to message #558671] Mon, 25 June 2012 12:59 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Ok, I removed the commented code, and am not getting this error

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 "identifier": expecting one of: "binary_double, 
binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, 
nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, 
varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: varchar2
KUP-01007: at line 9 column 32

[Updated on: Mon, 25 June 2012 13:12] by Moderator

Report message to a moderator

Re: Error while creating EXT Tables [message #558673 is a reply to message #558672] Mon, 25 June 2012 13:04 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
OK, a little strange, but now I changed the script of the table to below and it worked.

Many thanks for your help here.

S

CREATE TABLE TABLE1_EXT
(
  BusinessDate     VARCHAR2(50 BYTE),
  CIN          VARCHAR2(50 BYTE),
  CISCODE          VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY CORPREPRECON_DIR
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 
                 BADFILE CORPREPRECON_DIR:'test.bad' 
                 LOGFILE CORPREPRECON_DIR:'test.log' 
                SKIP 1 
                 FIELDS 
                 TERMINATED BY '|' 
                 MISSING FIELD VALUES ARE NULL 
                ( 
                 BusinessDate position (1:8),
                  CIN position (10:19),
                 CISCode position (21:27) 
                ) 
                              )
     LOCATION (CORPREPRECON_DIR:'test.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
Re: Error while creating EXT Tables [message #558674 is a reply to message #558672] Mon, 25 June 2012 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 26397
Registered: January 2009
Location: SoCal
Senior Member
what changes result if/when two lines below are removed & eliminated
> BADFILE CORPREPRECON_DIR:'test.bad'
> LOGFILE CORPREPRECON_DIR:'test.log'
Re: Error while creating EXT Tables [message #558676 is a reply to message #558669] Mon, 25 June 2012 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66170
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works for me:
SQL> CREATE TABLE TABLE1_EXT
  2  (
  3    BUSINESS_DATE     VARCHAR2(50 BYTE),
  4    CIN_CODE          VARCHAR2(50 BYTE),
  5    CIS_CODE          VARCHAR2(50 BYTE)
  6  )
  7  ORGANIZATION EXTERNAL
  8    (  TYPE ORACLE_LOADER
  9       DEFAULT DIRECTORY CORPREPRECON_DIR
 10       ACCESS PARAMETERS 
 11         ( RECORDS DELIMITED BY NEWLINE 
 12                   BADFILE CORPREPRECON_DIR:'test.bad' 
 13                   LOGFILE CORPREPRECON_DIR:'test.log' 
 14                  SKIP 1 
 15                   FIELDS 
 16                   TERMINATED BY '|' 
 17                   MISSING FIELD VALUES ARE NULL 
 18                  ( 
 19  --                  BUSINESS_DATE position (1:8),
 20  --                  CIN_CODE POSITION (10:19),
 21  --                  CIS_CODE POSITION (21:27)
 22                    BusinessDate varchar2(50) 
 23                    ,CIN varchar2(50)  
 24                    ,CISCode varchar2(50) 
 25                  ) 
 26                                )
 27       LOCATION (CORPREPRECON_DIR:'test.txt')
 28    )
 29  REJECT LIMIT UNLIMITED
 30  NOPARALLEL
 31  NOMONITORING;

Table created.

I think you have a hidden or wrong (not US7ASCII) character somewhere.

Regards
Michel
Re: Error while creating EXT Tables [message #558678 is a reply to message #558674] Mon, 25 June 2012 13:26 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
I am not sure if this is to do with the setting somewhere or what .. cos the below code throws this error which I find quiet strange
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04043: table column not found in external source: CIN_CODE



CREATE TABLE TABLE_EXT
(
  BusinessDate     VARCHAR2(50 BYTE),
  CIN_CODE          VARCHAR2(50 BYTE),
  CIS_CODE          VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY CORPREPRECON_DIR
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 

                SKIP 1 
                 FIELDS 
                 TERMINATED BY '|' 
                 MISSING FIELD VALUES ARE NULL 
                ( 
                 BusinessDate position (1:8),
                  CIN position (10:19),
                 CISCode position (21:27) 
                ) 
                              )
     LOCATION (CORPREPRECON_DIR:'test.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


where as when I use the following script with the cols matching the file name it works fine.


CREATE TABLE TABLE1_EXT
(
  BusinessDate     VARCHAR2(50 BYTE),
  CIN          VARCHAR2(50 BYTE),
  CISCODE          VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY CORPREPRECON_DIR
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 
                 BADFILE CORPREPRECON_DIR:'CISmap.bad' 
                 LOGFILE CORPREPRECON_DIR:'CISmap.log' 
                SKIP 1 
                 FIELDS 
                 TERMINATED BY '|' 
                 MISSING FIELD VALUES ARE NULL 
                ( 
                 BusinessDate position (1:8),
                  CIN position (10:19),
                 CISCode position (21:27) 
                ) 
                              )
     LOCATION (CORPREPRECON_DIR:'test.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
Re: Error while creating EXT Tables [message #558679 is a reply to message #558678] Mon, 25 June 2012 13:28 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
so it can't be some spl char I beleive, its just the settings somewhere.

S
Re: Error while creating EXT Tables [message #558681 is a reply to message #558679] Mon, 25 June 2012 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 66170
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"spl"? What is "spl"?
There is NO settings for this anywhere.

BlackSwan:
BTW, 11g is merely a Marketing label & not a real Oracle Version number to 4 decimal places


Post yours.

Regards
Michel

[Updated on: Mon, 25 June 2012 13:33]

Report message to a moderator

Re: Error while creating EXT Tables [message #558682 is a reply to message #558681] Mon, 25 June 2012 13:46 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Not sure what the problem is then, as trust me that is all I modifiedi n the script and it is working fine. And apologies for my lingos, with 'spl' I meant 'Special'

Thanks,
S
Re: Error while creating EXT Tables [message #558683 is a reply to message #558682] Mon, 25 June 2012 13:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8910
Registered: November 2002
Location: California, USA
Senior Member
Of course it throws an error when the columns don't match. In your bottom section, you tell it that the second column of data goes in the cin column of the table, so it looks for a cin column in the upper table creation section, and finds that there isn't one. Requiring the columns to match is just part of the general syntax.
Re: Error while creating EXT Tables [message #558685 is a reply to message #558683] Mon, 25 June 2012 14:13 Go to previous message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks a lot Barbara, I was not aware of it. I was always under the impression that the cols mentioned as fields of the source file. Will keep a note of it.

I just noticed that the Oracle version I am using is 10.2.0.3.0

Thanks a lot.
S
Previous Topic: select with order in self-reference table
Next Topic: excel data import oracle
Goto Forum:
  


Current Time: Sun Feb 17 00:07:33 CST 2019