Thanks for the question, Bill .

Asked: March 01, 2003 - 12:33 pm UTC

Last updated: February 23, 2012 - 12:55 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
What i am trying to do is load in bank transactions ( downloaded in a comma delimited format from the bank ) into my database. My approach is to create an external table from the file and then create a regular table from the external one. then the data can be manipulated etc.
the problem i am having is that the .csv file has a date format that the insert ( from external_table to table t ) is failing on.

an example line from my .csv file looks like:
2/24/2003,-40,*,,"ATM WITHDRAWAL "

my external table definition looks like:
create table external_table (
trandate date,
amount number(10,2),
ignore1 char(1),
ignore2 char(1) null,
descr varchar2(4000)
)
organization external (
type oracle_loader
default directory filebackup
access parameters
( fields terminated by ','
optionally enclosed by '"'
missing field values are null )
location ('Checking.csv')
) ;

When i load i get this error:
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

which when i view the log i see:
LOG file opened at 03/01/03 09:29:10

Field Definitions for table EXTERNAL_TABLE
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

TRANDATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
AMOUNT CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
IGNORE1 CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
IGNORE2 CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
DESCR CHAR (4000)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
error processing column TRANDATE in row 1 for datafile D:\sql\Checking.csv
ORA-01843: not a valid month

So it appears it is chocking on the date format. I tried to_date on the insert but that didn't work. That date in the .csv file can either be in M:DD:YYYY or MM:DD:YYYY format.
How best is this solved ?

Thanks -
Bill

and Tom said...

Use a date format in the CREATE TABLE. Here is one I've used:

create table big_table_external
( OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(18),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
(
records delimited by newline skip 21
fields terminated by '|'
missing field values are null
( owner ,object_name ,subobject_name ,object_id ,data_object_id ,object_type
,created date 'dd-mon-yy' ,last_ddl_time date 'dd-mon-yy'
,"TIMESTAMP" ,status ,temporary ,generated ,secondary
)
)
location ('big_table.dat')
)
/

for example -- your date format mask would be mm:dd:yyyy which would handle both.

ALTERNATIVELY

you could have trandate be a VARCHAR -- just a string. And then you can use to_date() on the string in your SQL.

I would use the create table approach however, it'll put bad records to the .bad file and not fail your SQL statement when you encounter a bad date (if you set rejects on the create table that is...)

Rating

(23 ratings)

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

Comments

thanks - that did it

Bill, March 01, 2003 - 10:37 pm UTC

thanks again Tom

Handling midnight

Craig, January 12, 2004 - 2:41 pm UTC

Tom -
You have been an huge help to us in getting our external tables working well. However, we do have (at least) one more question.

The source system is handing us a date/time field in 24 hour format. But when the source is midnight, it is passed to us as 24:00:00, rather than 00:00:00. Is there any mask we can apply to handle this numbering convention?

Thanks again!!

January 13, 2004 - 1:10 am UTC

map it as a string in the external table definition and then use SQL to convert it:

decode( substr( dt_field,n,2),
24, to_date( ....., substr( dt_field, 1, n-1 ) || '00' ||
substr( dt_field,n+2 ),
to_date( ....., dt_field )

(where n is the offset of the hour field

Date format

A reader, January 13, 2004 - 8:42 am UTC

I initially tried to define the date columns as DATE, however I was having difficulties loading the data; some of the date columns, in the file, where blank. I used your suggestion to define as varchar and use to_date. Works great!

I read in the Database Utilities manual that the SEQUENCE parameter can be used. I've tried, but failed to succesfully use this parameter. Would it be possible for you to provide an example?

January 13, 2004 - 5:46 pm UTC

just use ROWNUM in the query. easier than reading the docs :)

Nice

Ram, March 02, 2004 - 12:55 am UTC

Hi Tom,
When we load data from flat file into an external table and
if the flat file has some null values,How to discard the
null values?
Please do reply.

March 02, 2004 - 7:33 am UTC

well, you don't load a flat file into an external table -- you use an external table to query a flat file.

and all you would need to do to skip records where some fields are null would be:

select * from external_table where field1 is not null and field2 is not null and so on...

just use SQL to filter the data.

Thanks

Ram, March 02, 2004 - 8:27 am UTC

Dear Tom,
Thanks for your response.If the flat file resides in a remote database,How to query that?Is it possible to use a
dbLink that can link us to the remote flat file?
Please do reply.
Thanks in advance.

March 02, 2004 - 9:06 am UTC

a dblink can access a remote table, the remote table can be an external table so that is how you could do that, you would create an external table in the remote database.

External Tables Nulls in WHERE clause

Richard Smith, March 02, 2004 - 12:32 pm UTC

FYI, when querying an external table, if any column referenced in the WHERE clause has null values, those records will be implicitly filtered from the result set. To test it, for a know set of data having some null values in column B for example:
SELECT * FROM EXT_TABLE WHERE B IS NULL;
Will return no rows even if half the rows have values in column B and half are nulls.

March 02, 2004 - 6:58 pm UTC

umm, no:


ops$tkyte@ORA9IR2.US.ORACLE.COM> create table external_table
  2  (EMPNO NUMBER(4) ,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2),
  9   DEPTNO NUMBER(2)
 10  )
 11  ORGANIZATION EXTERNAL
 12  ( type oracle_loader
 13    default directory data_dir
 14    access parameters
 15    ( fields terminated by ',' )
 16    location ('emp.dat')
 17  )
 18  /
 
Table created.
 
ops$tkyte@ORA9IR2.US.ORACLE.COM> host flat scott/tiger emp > /tmp/emp.dat
 
 
 
 
ops$tkyte@ORA9IR2.US.ORACLE.COM> select * from external_table where comm is null;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ------
      7369 SMITH      CLERK           7902 17-DEC-80        800                20
      7566 JONES      MANAGER         7839 02-APR-81       2975                20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                20
      7839 KING       PRESIDENT            17-NOV-81       5000                10
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                20
      7900 JAMES      CLERK           7698 03-DEC-81        950                30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                10
 
10 rows selected.



<b>they work normally with nulls</b>

Problem with external table

Kumar, April 19, 2004 - 2:15 am UTC

Hi Tom,
I created an external table but it throws errors when accessed.How to
correct this?

SQL>  create table emp_ext(empno number,ename varchar2(30),sal 

number,deptno number)
  2   organization external(
  3     type ORACLE_LOADER
  4     default directory d
  5     access parameters(records delimited by newline
  6                       fields terminated by ':')
  7     location('emp.lst')
  8  );

Table created.

SQL> select * from emp_ext;
select * from emp_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout 
ORA-30653: reject limit reached 
ORA-06512: at "SYS.ORACLE_LOADER", line 14 
ORA-06512: at line 1 

Please do reply.
Bye!

April 19, 2004 - 6:44 am UTC

look at the bad file in director 'd'

"external table and date format", version 9.2.0

Rob, April 29, 2004 - 3:45 pm UTC

It was very helpful in solving the problem with dates using external tables. I had two problems, a bad date format and blank date fields in my text file. Your solution helped me with the first issue, but the secound was not covered as well as I would have liked. I found the solution by looking at SQL Loader date issues and ended up with the below code to solve both problems in my external table create code. Thanks for the many times I have used your site.

CREATION_DATE DATE 'YYYYMMDD' NULLIF CREATION_DATE=BLANKS,

Can you put not null constraint on a column in an external table.

Rob Balter, June 11, 2004 - 10:15 am UTC

Tom:

Is there a way to specify "not null" for a column in an external table.

Thanks,

Rob

June 11, 2004 - 4:24 pm UTC

no, not really. why would you need to?

Addl info

Rob, June 11, 2004 - 4:58 pm UTC

As a validation. I know I can select from the external table with a "where column is not null", I was thinking it would be nice as a form of validation to get rows that had a null in a column that should not be null in the bad file.
It would be a way to validate the underlying file w/o writing a bunch of sql queries to validate.

June 11, 2004 - 5:16 pm UTC

the constraint would have to be down in the controlfile section -- you could use a discard file.

external table error

E, December 03, 2004 - 12:19 am UTC

Hi Tom,
I have a problem selecting from external table. Could you help?

create table transactions
(
call_id varchar2(30),
start_date_time date,
end_date_time date,
first_account_number varchar2(21),
second_account_number varchar2(21),
account_type varchar2(15),
amount number(20,2),
call_mode varchar2(15)
)
organization external
(
type oracle_loader
default directory ext_data_files
access parameters
(
fields terminated by ','
missing field values are null
)
location ('transactions.txt')
)
reject limit unlimited;

Here is the example of data:
--------------------------------
ACCB-000505120041105224833,11/5/2004 2:50:35 PM,11/5/2004 2:50:35 PM,5011410078,,,0,
ACCB-000505120041105224833,11/5/2004 2:50:38 PM,11/5/2004 2:50:50 PM,5011410078,,,1111,
ACCB-000505120041105224833,11/5/2004 2:50:50 PM,11/5/2004 2:50:55 PM,5011410078,,,0,
ACCB-000505120041105224833,11/5/2004 2:50:58 PM,11/5/2004 2:50:58 PM,5011410078,,,0,

It fails with ORA-01843: not a valid month. How can I provide date mask for it?

Thanks,
E

December 03, 2004 - 7:48 am UTC

Look at the original answer, I loaded dates with formats right there and then.

(seems to have been the point of the question in the first place?)

Eugene, December 03, 2004 - 3:59 pm UTC

Tom,
I did read from beginning and I understand that I can provide date mask for the date field.
I was actually asking you to show me how to write that mask for "11/5/2004 2:50:35 PM"

E

December 04, 2004 - 10:42 am UTC

you asked "How can I provide date mask for it?", which is very different from "what is the format"

the format is rather straightforward

dd/mm/yyyy hh:mi:ss am

(or

mm/dd/yyyy......

external table questions

Parag J Patankar, April 14, 2005 - 8:00 am UTC

Hi Tom,

I am having following simple table for external table

create table ext_t1
(
a char(5),
b char(25),
c date
)
organization external
(
type oracle_loader
default directory data_dir
access parameters
(
records delimited by newline
logfile data_dir:'t.log'
fields terminated by '|'
ltrim
( a, b, c date 'yyyymmdd' )
)
location ('test.txt')
)
/
select * from ext_t1
/

My data is as follows

09009|a234567890123456789012345|20050413
09008|b234567890123456789012345|20050413
09065|c234567890123456789012345|20050413
09066|d234567890123456789012345|20050413
09067| eeeeeeeeeeeeeeeeeeee |20050413
9069| 1234567 |20050413

My questions regarding external table

Q1. Is it possible to remove blank spaces only from column b not from all columns for e.g. I want to keep ' 9069' as it is?

Q2. Can I use case/decode statement in external table defination ( not in select statment from external table ) ? for e.g. 09008 : 'MU',
09009 : 'NY' 09065 : 'PA' all other values 'OT'

Kindly suggest.

regards & thanks
pjp

April 14, 2005 - 8:38 am UTC

1) select a, trim(b), c from ext_t1;

2) create a view, use view. create ext_t1_table. create view ext_t1 as select... from ext_t1_table.

external table trim clause ?

Parag J Patankar, April 14, 2005 - 8:54 am UTC

Hi Tom,

Thanks for your answers. Sorry I was not very specific to my questions. To be more specific to my questions

1. Can I use any trimming clause in external table applied to only specific column ? ( I know lrtrim,trim ... trim_spec in external table defination applied to all coulmns )

best regards
pjp

April 14, 2005 - 8:59 am UTC

i'm suggesting to use a view, it'll accomplish everything you need very simply.

what about milliseconds?

Denis, July 21, 2005 - 5:12 pm UTC

Tom, what if I need TIMESTAMP field with milliseconds in extrnal table?

if I do something like

create table rating.ext_userAlbumTrackrating_0
(
USERID NUMBER(10) ,
AlbumTrackID NUMBER(10) ,
RATING NUMBER(3) ,
RATINGSOURCE NUMBER(20) ,
DATECREATED TIMESTAMP ,
DATEMODIFIED TIMESTAMP
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory AlbumTrackrating_dir
access parameters
(
records delimited by newline
nologfile
fields terminated by '\t'
LRTRIM
(USERID, AlbumTrackID, RATING, RATINGSOURCE, DATECREATED DATE 'YYYY-MM-DD HH24:MI:SSXFF', DATEMODIFIED DATE 'YYYY-MM-DD HH24:MI:SSXFF')
)
location ('1.txt')
)

I get Invalid date Mask error.

if I do

create table rating.ext_userAlbumTrackrating_0
(
USERID NUMBER(10) ,
AlbumTrackID NUMBER(10) ,
RATING NUMBER(3) ,
RATINGSOURCE NUMBER(20) ,
DATECREATED TIMESTAMP ,
DATEMODIFIED TIMESTAMP
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory AlbumTrackrating_dir
access parameters
(
records delimited by newline
nologfile
fields terminated by '\t'
LRTRIM
(USERID, AlbumTrackID, RATING, RATINGSOURCE, DATECREATED TimeStAMP 'YYYY-MM-DD HH24:MI:SSXFF', DATEMODIFIED TimeStAMP 'YYYY-MM-DD HH24:MI:SSXFF')
)
location ('1.txt')
)

I get

KUP-01005: syntax error: found "timestamp": 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"

Please, advice.

July 21, 2005 - 6:06 pm UTC

I'll be totally lazy

map it as varchar2, to_timestamp it in the select.


ahh, that didn't feel good, never mind (using commas):

ops$tkyte@ORA9IR2> create table ext_userAlbumTrackrating_0
  2   (
  3       USERID NUMBER(10) ,
  4       AlbumTrackID NUMBER(10) ,
  5       RATING NUMBER(3) ,
  6       RATINGSOURCE NUMBER(20) ,
  7       DATECREATED TIMESTAMP ,
  8       DATEMODIFIED TIMESTAMP
  9   )
 10   ORGANIZATION EXTERNAL
 11   ( type oracle_loader
 12     default directory AlbumTrackrating_dir
 13     access parameters
 14     (
 15       records delimited by newline
 16       fields terminated by ','
 17       LRTRIM
 18       (USERID,
 19            AlbumTrackID,
 20            RATING,
 21            RATINGSOURCE,
 22            DATECREATED char(25) DATE_FORMAT timestamp mask "YYYY-MM-DD HH24:MI:SSXFF",
 23            DATEMODIFIED char(25) DATE_FORMAT timestamp mask "YYYY-MM-DD HH24:MI:SSXFF"
 24     )
 25     )
 26     location ('1.txt')
 27   )
 28  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !echo 1,2,3,4,2005-01-02 01:02:03.123,2005-01-02 01:02:03.123 > /tmp/1.txt
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from ext_userAlbumTrackrating_0;
 
    USERID ALBUMTRACKID     RATING RATINGSOURCE
---------- ------------ ---------- ------------
DATECREATED
---------------------------------------------------------------------------
DATEMODIFIED
---------------------------------------------------------------------------
         1            2          3            4
02-JAN-05 01.02.03.123000 AM
02-JAN-05 01.02.03.123000 AM

milliseconds work

Denis, July 21, 2005 - 7:55 pm UTC

Thank you Tom, it works. I checked manual, online documentation, forums and did not find the solution you provided. CHAR did not work for me because I need to do some aggreagation on timestamp. I tested and found to is more expensive to convert it on the fly then to define type while creating table.

July 22, 2005 - 8:39 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96652/ch12.htm#1008535 http://docs.oracle.com/docs/cd/B10501_01/server.920/a96652/ch12.htm#1011184 <code>

Perfect - just what I needed!

Peter, August 03, 2006 - 2:22 pm UTC

Dear Tom,

I was trying to load 100,000 very wide records from MS Access to an attached Oracle table using "insert into ... select ... from ...". The job ran all over last night and still didn't finish.

My DBA had a look and saw that Access was "lying" - it was really doing 100,000 individual inserts. He showed me how to create an external table but then all my records went into the bad file because of problems with dates.

The info you gave on this page was perfect, and my data all loaded in three minutes!

Thank you so much for all the help that I am constantly finding on AskTOM.

Regards,
Peter

External Table

Steve, January 18, 2011 - 10:04 am UTC

Hi,

I'm unable to run the below as I need the .csv file column to be a number, but it obviously loses formatting when converted to CSV.

Creating the external and internal table with NUMBER on the column does not resolve the issue. I've seen a previous article where you've put the same as me, but used .dat file and it's worked. Please can you provide the solution that works with .csv?

External Table:

--CREATES EXTERNAL TABLE TO HOLD DATA FOR THE .CSV
CREATE TABLE XXCPP.XXCPP_SBI_UPLOAD_DATA_EXT
(
person_id number
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY SBI
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(
person_id
)
)
LOCATION ('SBI.csv')
);

Internal Table:

CREATE TABLE XXCPP.XXCPP_SBI_UPLOAD_DATA
(
person_id number
);

Package:

CREATE OR REPLACE package body APPS.XXCPP_SBI_UPLOAD as

PROCEDURE upload
(
p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER
)

IS

CURSOR GET_OUTPUT_EXCEL
IS
select
person_id
from
XXCPP.XXCPP_SBI_UPLOAD_DATA_EXT;

BEGIN

-- INSERT HTML INIT PAGE CODE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//IT"><html><head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title> '||' </title></head><body> <table width="100%" border="1" cellpadding="1">');

--CREATE HEADERS FOR OUTPUT FILE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
'<thead><tr bgcolor=CCCCCC>'||
'<th>Person id</th>'||
'</tr></thead>');

--ADD VALUES FROM CURSOR IN TO EXCEL OUTPUT
FOR c_get_output_rec IN get_output_excel LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<tr><td>'
||to_char(c_get_output_rec.person_id)||'</td><td>'
||'</td></tr>');
END LOOP;

--INSERT ALL VALUES FROM .CSV FILE IN TO INTERNAL TABLE
BEGIN
INSERT INTO XXCPP.XXCPP_SBI_UPLOAD_DATA SELECT person_id FROM XXCPP.XXCPP_SBI_UPLOAD_DATA_EXT;
END;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
BEGIN
p_retcode := 1;
p_errbuf := SQLERRM;
END;

END upload;

END XXCPP_SBI_UPLOAD;

Error:

**Starts**18-JAN-2011 15:49:49
**Ends**18-JAN-2011 15:49:49
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

January 23, 2011 - 3:37 pm UTC

..as I need the .csv file column to be a number, but
it obviously loses formatting when converted to CSV.
...

you lost why. why is it obvious? It is not obvious to me.

I'm not following this example at all, I haven't any idea what you are trying to do.

we cannot see what sbi.csv is or looks like.

we cannot run your code - fnd_file - I don't have apps installed, nor will I on any machine I use.

I cannot tell you how much I hate this code:

EXCEPTION 
WHEN OTHERS THEN 
BEGIN 
p_retcode := 1; 
p_errbuf := SQLERRM; 
END;

so, give us a working example (i have a feeling your plsql routine doesn't even need to be involved here) that reproduces the issue on OUR machines.

again : Not a valid month

Abdul-Razzaq, February 19, 2012 - 8:04 am UTC

Dears,
I use sql loader to upload data from txt; one of fields is a date.
a formated the control file to handle this field.

while i'm running sqlldr from server1; every thing is going correctly.
but while i'm running sqlldr from server2 all records goes to bad file with not a valid month.

the format of the date is "dd-MON-yyyy"; this format specified in the control file.

thanks

February 19, 2012 - 6:06 pm UTC

give example
specify versions
tell us operating systems.
tell us of the environment (any NLS settings set on the client?)

ITs solved

A reader, February 20, 2012 - 1:30 am UTC

Thanks everybody

I update regedit \ oracle by adding new string
nls_date_format = 'dd-MON-yyyy'

oracle regedit folder contains plenty of home folders
so I put the above string to all home folders.

EXTERNAL TABLE ERROR

NAGU, February 22, 2012 - 4:09 am UTC

  1   create table emp111_load11_data1(
  2   empno number,
  3   ename varchar2(20),
  4   job varchar2(20),
  5   mgr number,
  6   hiredate date,
  7   sal number,
  8   comm number,
  9   deptno number)
 10   organization external
 11   (type oracle_loader default directory dir_emp_load
 12   access parameters(records delimited by new line
 13   fields terminated by ','
 14   optionally enclosed by '"'
 15   missing field values are null
 16   (empno char(32),
 17   ename char(32),
 18   job char(32),
 19   mgr char(32),
 20   hiredate char(32) 'DD-MM-YY',
 21   sal char(32),
 22   comm char(32),
 23   deptno char(32)))
 24   location ('external.txt'))
 25*  reject limit 1000
SQL> /

Table created.

SQL> SELECT * FROM EMP111_LOAD11_DATA1;
SELECT * FROM EMP111_LOAD11_DATA1
*
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 "identifier": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01008: the bad identifier was: new
KUP-01007: at line 1 column 22
ORA-06512: at "SYS.ORACLE_LOADER", line 19

EXTERNAL TABLE ERROR

NAGU, February 22, 2012 - 4:12 am UTC

i have text file and load to external table .so table was created but when i was to fetch the data from external it gives the following error.so please help me it is very argent.
my code and  errors are mention below

  1   create table emp111_load11_data1(
  2   empno number,
  3   ename varchar2(20),
  4   job varchar2(20),
  5   mgr number,
  6   hiredate date,
  7   sal number,
  8   comm number,
  9   deptno number)
 10   organization external
 11   (type oracle_loader default directory dir_emp_load
 12   access parameters(records delimited by new line
 13   fields terminated by ','
 14   optionally enclosed by '"'
 15   missing field values are null
 16   (empno char(32),
 17   ename char(32),
 18   job char(32),
 19   mgr char(32),
 20   hiredate char(32) 'DD-MM-YY',
 21   sal char(32),
 22   comm char(32),
 23   deptno char(32)))
 24   location ('external.txt'))
 25*  reject limit 1000
SQL> /

Table created.

SQL> SELECT * FROM EMP111_LOAD11_DATA1;
SELECT * FROM EMP111_LOAD11_DATA1
*
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 "identifier": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01008: the bad identifier was: new
KUP-01007: at line 1 column 22
ORA-06512: at "SYS.ORACLE_LOADER", line 19

February 23, 2012 - 12:55 am UTC

ops$tkyte%ORA11GR2> CREATE TABLE "ET"
  2  (
  3    "EMPNO" NUMBER(4),
  4    "ENAME" VARCHAR2(10),
  5    "JOB" VARCHAR2(9),
  6    "MGR" NUMBER(4),
  7    "HIREDATE" DATE,
  8    "SAL" NUMBER(7,2),
  9    "COMM" NUMBER(7,2),
 10    "DEPTNO" NUMBER(2)
 11  )
 12  ORGANIZATION external
 13  (
 14    TYPE oracle_loader
 15    DEFAULT DIRECTORY MY_DIR
 16    ACCESS PARAMETERS
 17    (
 18      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 19      BADFILE 'LOAD_DIR':'emp.bad'
 20      LOGFILE 'emp.log_xt'
 21      READSIZE 1048576
 22      FIELDS TERMINATED BY "," LDRTRIM
 23      REJECT ROWS WITH ALL NULL FIELDS
 24      (
 25        "EMPNO" CHAR(255)
 26          TERMINATED BY ",",
 27        "ENAME" CHAR(255)
 28          TERMINATED BY ",",
 29        "JOB" CHAR(255)
 30          TERMINATED BY ",",
 31        "MGR" CHAR(255)
 32          TERMINATED BY ",",
 33        "HIREDATE" date 'DD-MON-RR'
 34          TERMINATED BY ",",
 35        "SAL" CHAR(255)
 36          TERMINATED BY ",",
 37        "COMM" CHAR(255)
 38          TERMINATED BY ",",
 39        "DEPTNO" CHAR(255)
 40          TERMINATED BY ","
 41      )
 42    )
 43    location
 44    (
 45      'emp.dat'
 46    )
 47  )REJECT LIMIT UNLIMITED
 48  /

Table created.

HOW TO

DURGA KRSIHNA, February 27, 2014 - 4:49 pm UTC

EXTERNAL TABLE CREATED BUT I AM NOT ABLE TO VIEW THE DATA BECOZ MY DATE IN THROWING AN EXCEPTION I THINK.
PLEASE LET ME KNOW WHAT HAVE I MISSED

create table xtern_prenet_all_5
(
YEAR number,
MONTH number,
REPORT_DATE date,
AMR_DEALER_CODE varchar2(50),
DEALER_NAME varchar2(50),
STATE varchar2(50),
REPORT_REGION varchar2(50),
STATUS varchar2(50),
SALES_CHANNEL varchar2(50),
RESELLER_NAME varchar2(50),
DEALER_CLASS varchar2(50) ,
POSTAL_CODE number ,
DISTRIBUTOR varchar2(50),
DISTRIBUTOR_BRANCH varchar2(50),
RATE_PLAN varchar2(20),
Area_Territory_id varchar2(50),
Area_Territory_name varchar2(50),
Micro_Market_ID varchar2(50),
Micro_Market_Name varchar2(50),
ACT number,
CHURN number ,
NET number
)
organization external
(
TYPE ORACLE_LOADER
default directory xtern_prenet_dir1
access parameters(
RECORDS DELIMITED BY newline
CHARACTERSET WE8MSWIN1252
STRING SIZES ARE IN BYTES
BADFILE 'pre.bad'
DISCARDFILE 'pre.dsc'
LOGFILE 'pre.log'
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"'
(
YEAR number,
MONTH number,
REPORT_DATE CHAR(20),-- date_format DATE mask "yyyy/mm/dd",
AMR_DEALER_CODE varchar2(50) ,
DEALER_NAME varchar2(50) ,
STATE varchar2(50),
REPORT_REGION varchar2(50),
STATUS varchar2(50),
SALES_CHANNEL varchar2(50),
RESELLER_NAME varchar2(50),
DEALER_CLASS varchar2(50) ,
POSTAL_CODE number ,
DISTRIBUTOR varchar2(50),
DISTRIBUTOR_BRANCH varchar2(50),
RATE_PLAN varchar2(20),
Area_Territory_id varchar2(50),
Area_Territory_name varchar2(50),
Micro_Market_ID varchar2(50),
Micro_Market_Name varchar2(50),
ACT number,
CHURN number ,
NET number
)
)
location('pre_net_all.txt')
)
REJECT LIMIT UNLIMITED ;

A-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "year": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 10 column 25
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.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

thanks - that did it

Handling midnight

Date format

Nice

Thanks

External Tables Nulls in WHERE clause

Problem with external table

"external table and date format", version 9.2.0

Can you put not null constraint on a column in an external table.

Addl info

external table error

external table questions

external table trim clause ?

what about milliseconds?

milliseconds work

Perfect - just what I needed!

External Table

again : Not a valid month

ITs solved

EXTERNAL TABLE ERROR

EXTERNAL TABLE ERROR

HOW TO

More to Explore

Live SQL

Dev Gym