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
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.