Home » RDBMS Server » Server Utilities » External table selection problem (ORACLE 10.2.0.1 RHEL 5)
icon5.gif  External table selection problem [message #327028] Fri, 13 June 2008 08:33 Go to next message
subrata.dass
Messages: 4
Registered: June 2008
Junior Member
Hi

When selecting from an external table I get the following 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 20 column 21
ORA-06512: at


The table in question is as follows

/* Formatted on 2008/06/13 12:33 (Formatter Plus v4.8.8) */
CREATE TABLE load_tablec (branch VARCHAR2(50),country VARCHAR2(50),
current_support_user_id VARCHAR2(50),
current_support_user_name VARCHAR2(50),
customer_group VARCHAR2(50),department VARCHAR2(50),effort_minutes   varchar2(50),
LOCATION  VARCHAR2(50),
priority VARCHAR2(50),
 problem_category VARCHAR2(50),
  problem_description CLOB,
  problem_item VARCHAR2(400),
  problem_summary    VARCHAR2(400),
  relog_date DATE,
  ROLE VARCHAR2(50),
  severity    VARCHAR2(50),
  solution CLOB,
  solved_date DATE,    state VARCHAR2(50)    ,
  status    VARCHAR2(50),
  ticket_logged_by_user_id VARCHAR2(50),
   ticket_logged_by_user_name VARCHAR2(50),
   ticket_logged_datetime DATE,
      ticket_no INTEGER,
       wonswon INTEGER,
       within_sla_response INTEGER,
        out_of_sla_response INTEGER,
        within_sla_resolve INTEGER,
        outof_sla_resolve INTEGER)
        ORGANIZATION external
   (
     TYPE oracle_loader
     DEFAULT DIRECTORY EXT_DIR
     ACCESS PARAMETERS
     (
       RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P9
        BADFILE 'EXT_DIR':'emp.bad' 
              LOGFILE 'EXT_DIR':'emp.log' 
       READSIZE 1048576 
       skip 1
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LRTRIM
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS 
(branch CHAR(50),
country varchar(50),
current_support_user_id varchar(50),
current_support_user_name varchar(50),
customer_group varchar(50),
department varchar(50),
effort_minutes   varchar(50),
LOCATION  varchar(50),
priority varchar(50),
problem_category varchar(50),
problem_description varchar2(4000),
problem_item varchar(50),
problem_summary    varchar(50),
relog_date  DATE(20) 'DD-MON-YYYY HH24:MI:SS',
ROLE varchar(50),
severity    varchar(50),
solution varchar2(4000),
solved_date  DATE(20) 'DD-MON-YYYY HH24:MI:SS',   
state varchar(50)    ,
status    varchar(50),
ticket_logged_by_user_id varchar(50),
ticket_logged_by_user_name varchar(50),
ticket_logged_datetime DATE(20) 'DD-MON-YYYY HH24:MI:SS',
ticket_no INTEGER,
wonswon INTEGER,
within_sla_response INTEGER,
out_of_sla_response INTEGER,
within_sla_resolve INTEGER,
outof_sla_resolve INTEGER)
)
location
     (
       'reportc.csv'
     )
   )REJECT LIMIT UNLIMITED

Could you please tell me whats wrong here . The CSV containing the file is attached

Regards

Subrata Dass
  • Attachment: report1.csv
    (Size: 2.63KB, Downloaded 189 times)

[Updated on: Fri, 13 June 2008 08:39] by Moderator

Report message to a moderator

Re: External table selection problem [message #327029 is a reply to message #327028] Fri, 13 June 2008 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ How you sure you format something, even after I added code tags I don't think it is really formatted

2/ Which one is line 20?

3/ Use SQL*Plus and copy and paste your session

Regards
Michel
Re: External table selection problem [message #327030 is a reply to message #327028] Fri, 13 June 2008 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, after fixing blank line, I get:
SQL> l
  1  CREATE TABLE load_tablec (branch VARCHAR2(50),country VARCHAR2(50),
  2  current_support_user_id VARCHAR2(50),
  3  current_support_user_name VARCHAR2(50),
  4  customer_group VARCHAR2(50),department VARCHAR2(50),effort_minutes   varchar2(50),
  5  LOCATION  VARCHAR2(50),
  6  priority VARCHAR2(50),
  7   problem_category VARCHAR2(50),
  8    problem_description CLOB,
  9    problem_item VARCHAR2(400),
 10    problem_summary    VARCHAR2(400),
 11    relog_date DATE,
 12    ROLE VARCHAR2(50),
 13    severity    VARCHAR2(50),
 14    solution CLOB,
 15    solved_date DATE,    state VARCHAR2(50)    ,
 16    status    VARCHAR2(50),
 17    ticket_logged_by_user_id VARCHAR2(50),
 18     ticket_logged_by_user_name VARCHAR2(50),
 19     ticket_logged_datetime DATE,
 20        ticket_no INTEGER,
 21         wonswon INTEGER,
 22         within_sla_response INTEGER,
 23          out_of_sla_response INTEGER,
 24          within_sla_resolve INTEGER,
 25          outof_sla_resolve INTEGER)
 26          ORGANIZATION external
 27     (
 28       TYPE oracle_loader
 29       DEFAULT DIRECTORY WORK_DIR
 30       ACCESS PARAMETERS
 31       (
 32         RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P9
 33          BADFILE 'EXT_DIR':'emp.bad'
 34                LOGFILE 'EXT_DIR':'emp.log'
 35         READSIZE 1048576
 36         skip 1
 37         FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LRTRIM
 38         MISSING FIELD VALUES ARE NULL
 39         REJECT ROWS WITH ALL NULL FIELDS
 40  (branch CHAR(50),
 41  country varchar(50),
 42  current_support_user_id varchar(50),
 43  current_support_user_name varchar(50),
 44  customer_group varchar(50),
 45  department varchar(50),
 46  effort_minutes   varchar(50),
 47  LOCATION  varchar(50),
 48  priority varchar(50),
 49  problem_category varchar(50),
 50  problem_description varchar2(4000),
 51  problem_item varchar(50),
 52  problem_summary    varchar(50),
 53  relog_date  DATE(20) 'DD-MON-YYYY HH24:MI:SS',
 54  ROLE varchar(50),
 55  severity    varchar(50),
 56  solution varchar2(4000),
 57  solved_date  DATE(20) 'DD-MON-YYYY HH24:MI:SS',
 58  state varchar(50)    ,
 59  status    varchar(50),
 60  ticket_logged_by_user_id varchar(50),
 61  ticket_logged_by_user_name varchar(50),
 62  ticket_logged_datetime DATE(20) 'DD-MON-YYYY HH24:MI:SS',
 63  ticket_no INTEGER,
 64  wonswon INTEGER,
 65  within_sla_response INTEGER,
 66  out_of_sla_response INTEGER,
 67  within_sla_resolve INTEGER,
 68  outof_sla_resolve INTEGER)
 69  )
 70  location
 71       (
 72         'reportc.csv'
 73       )
 74*    )REJECT LIMIT UNLIMITED
SQL> /

Table created.

So it is correct.

Regards
Michel
Re: External table selection problem [message #327032 is a reply to message #327029] Fri, 13 June 2008 08:49 Go to previous messageGo to next message
subrata.dass
Messages: 4
Registered: June 2008
Junior Member
Hi Michel

The code was created and formatted in Toad

the table gets created properly , however only when selecting from the table do I get the error .

I am not using sql plus here .

Thanks and Regards
Subrata
Re: External table selection problem [message #327039 is a reply to message #327028] Fri, 13 June 2008 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
I have a couple of observations which may or may not be relevant.
In the top part the variables are mostly VARCHAR2 with the inclusion of 2 CLOB

In the bottom part the variables are a collection of CHAR & VARCHAR (mostly not VARCHAR2) & both CLOB have morphed into VARCHAR2(4000)

Can a table have multiple CLOB?

Since I am still working on my 1st cup of coffee, I could be still (half?) asleep.
Re: External table selection problem [message #327046 is a reply to message #327039] Fri, 13 June 2008 09:36 Go to previous messageGo to next message
subrata.dass
Messages: 4
Registered: June 2008
Junior Member
Hi

Changed both the varchar to clob

Now also the problem is the same

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 "clob": 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-01007: at line 19 column 24
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Regards

Subrata



Re: External table selection problem [message #327054 is a reply to message #327046] Fri, 13 June 2008 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste what you did just like I did WITH CODE TAGS.

Regards
Michel
Re: External table selection problem [message #327056 is a reply to message #327028] Fri, 13 June 2008 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
subrata.dass,
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Use SQL*PLUS and CUT & PASTE the whole session including the actual SELECT which is failing.

If you do not cooperate with us then, You're On Your Own (YOYO)!
Re: External table selection problem [message #327057 is a reply to message #327054] Fri, 13 June 2008 10:08 Go to previous messageGo to next message
subrata.dass
Messages: 4
Registered: June 2008
Junior Member
Hi

I did the following


SQL> CREATE TABLE load_tablec1 (branch VARCHAR2(50),country VARCHAR2(50),
2 current_support_user_id VARCHAR2(50),
3 current_support_user_name VARCHAR2(50),
4 customer_group VARCHAR2(50),department VARCHAR2(50),effort_minutes varchar2(50),
5 LOCATION VARCHAR2(50),
6 priority VARCHAR2(50),
7 problem_category VARCHAR2(50),
8 problem_description CLOB,
9 problem_item VARCHAR2(400),
10 problem_summary VARCHAR2(400),
11 relog_date DATE,
12 ROLE VARCHAR2(50),
13 severity VARCHAR2(50),
14 solution CLOB,
15 solved_date DATE, state VARCHAR2(50) ,
16 status VARCHAR2(50),
17 ticket_logged_by_user_id VARCHAR2(50),
18 ticket_logged_by_user_name VARCHAR2(50),
19 ticket_logged_datetime DATE,
20 ticket_no INTEGER,
21 wonswon INTEGER,
22 within_sla_response INTEGER,
23 out_of_sla_response INTEGER,
24 within_sla_resolve INTEGER,
25 outof_sla_resolve INTEGER)
26 ORGANIZATION external
27 (
28 TYPE oracle_loader
29 DEFAULT DIRECTORY WORK_DIR
30 ACCESS PARAMETERS
31 (
32 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P9
33 BADFILE 'EXT_DIR':'emp.bad'
34 LOGFILE 'EXT_DIR':'emp.log'
35 READSIZE 1048576
36 skip 1
37 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LRTRIM
38 MISSING FIELD VALUES ARE NULL
39 REJECT ROWS WITH ALL NULL FIELDS
40 (branch CHAR(50),
41 country varchar(50),
42 current_support_user_id varchar(50),
43 current_support_user_name varchar(50),
44 customer_group varchar(50),
45 department varchar(50),
46 effort_minutes varchar(50),
47 LOCATION varchar(50),
48 priority varchar(50),
49 problem_category varchar(50),
50 problem_description clob,
51 problem_item varchar(50),
52 problem_summary varchar(50),
53 relog_date DATE(20) 'DD-MON-YYYY HH24:MI:SS',
54 ROLE varchar(50),
55 severity varchar(50),
56 solution clob,
57 solved_date DATE(20) 'DD-MON-YYYY HH24:MI:SS',
58 state varchar(50) ,
59 status varchar(50),
60 ticket_logged_by_user_id varchar(50),
61 ticket_logged_by_user_name varchar(50),
62 ticket_logged_datetime DATE(20) 'DD-MON-YYYY HH24:MI:SS',
63 ticket_no INTEGER,
64 wonswon INTEGER,
65 within_sla_response INTEGER,
66 out_of_sla_response INTEGER,
67 within_sla_resolve INTEGER,
68 outof_sla_resolve INTEGER)
69 )
70 location
71 (
72 'reportc.csv'
73 )
74 )REJECT LIMIT UNLIMITED
75 /

Table created.

SQL> select * from load_tablec1;
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 "clob": 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-01007: at line 19 column 24
ORA-06512: at "SYS.ORACLE_LOADER", line 19



no rows selected

But am still getting the error . This time I got it pasted from sql*plus

Regards
Subrata Dass

Re: External table selection problem [message #327064 is a reply to message #327028] Fri, 13 June 2008 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
While I can find nothing in any manual, I strongly suspect that CLOB datatype is not supported in external table.

CREATE TABLE load_tablec (
branch VARCHAR2(50),
country VARCHAR2(50),
current_support_user_id VARCHAR2(50),
current_support_user_name VARCHAR2(50),
customer_group VARCHAR2(50),
department VARCHAR2(50),
effort_minutes varchar2(50),

The error message implicates the CLOB, but the line number is not the same as SQL*plus displays.
If you start the code with the lines above does the reported ERROR line get closer to the actual line with CLOB?

What happens if you change CLOB to VARCHAR2(4000) in all places?
Re: External table selection problem [message #327067 is a reply to message #327057] Fri, 13 June 2008 10:50 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You have many errors and problems in your code. In the lower half, you need to use CHAR, not VARCHAR or VARCHAR2. Your data is too much of a mess to do anything with. It is wrapped around multiple lines and some records have more fields than others or they are not in the same order. Such garbage data cannot be loaded. Your code does not match your data. You need to first get a good data set. Then create a script to load just the first column, then once that works add the next column and so forth, so that you can see where the problems are.

Previous Topic: running multiple sql loader files at once in windows (merged)
Next Topic: Import Successful with warnings....ORA-01658 error..
Goto Forum:
  


Current Time: Wed Dec 07 07:07:50 CST 2016

Total time taken to generate the page: 0.26392 seconds