OraFAQ Forum: SQL & PL/SQL » Error while creating EXT Tables

Home » SQL & PL/SQL » SQL & PL/SQL » Error while creating EXT Tables (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error while creating EXT Tables [message #558669]

Mon, 25 June 2012 12:49

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

Report message to a moderator

Re: Error while creating EXT Tables [message #558670 is a reply to message #558669]

Mon, 25 June 2012 12:56

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Error while creating EXT Tables [message #558671 is a reply to message #558670]

Mon, 25 June 2012 12:57

sulabhagra
Messages: 57
Registered: April 2012

Member

that is what is killing me, I believe it is a generic message than anything else?

Report message to a moderator

Re: Error while creating EXT Tables [message #558672 is a reply to message #558671]

Mon, 25 June 2012 12:59

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

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;

Report message to a moderator

Re: Error while creating EXT Tables [message #558674 is a reply to message #558672]

Mon, 25 June 2012 13:07

BlackSwan
Messages: 26766
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'

Report message to a moderator

Re: Error while creating EXT Tables [message #558676 is a reply to message #558669]

Mon, 25 June 2012 13:13

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: Error while creating EXT Tables [message #558678 is a reply to message #558674]

Mon, 25 June 2012 13:26

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;

Report message to a moderator

Re: Error while creating EXT Tables [message #558679 is a reply to message #558678]

Mon, 25 June 2012 13:28

sulabhagra
Messages: 57
Registered: April 2012

Member

so it can't be some spl char I beleive, its just the settings somewhere.

S

Report message to a moderator

Re: Error while creating EXT Tables [message #558681 is a reply to message #558679]

Mon, 25 June 2012 13:33

Michel Cadot
Messages: 68641
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

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

Report message to a moderator

Re: Error while creating EXT Tables [message #558683 is a reply to message #558682]

Mon, 25 June 2012 13:58

Barbara Boehmer
Messages: 9088
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.

Report message to a moderator

Re: Error while creating EXT Tables [message #558685 is a reply to message #558683]

Mon, 25 June 2012 14:13

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

Report message to a moderator

Previous Topic:	select with order in self-reference table
Next Topic:	excel data import oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:32:34 CDT 2024