External table does not allow nulls by default [message #340417] |
Tue, 12 August 2008 14:03  |
ckb_ORACLE
Messages: 5 Registered: August 2008
|
Junior Member |
|
|
Hi all,
My problem is that I am picking up data from an .csv file and have a row that has all its fields null.whether I omit REJECT ROWS WITH ALL NULL or write it the external table is not populated with the null row.My code is
drop table test_table1;
CREATE TABLE test_table1
(
empno number(2),
ename char(30)
)
ORGANIzATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext
--abcd is a comment
ACCESS PARAMETERs(records delimited by newline
badfile 'bad_emp'
logfile 'log_emp'
FIELDS TERMINATED BY ','
reject rows with all null fields
(empno integer external(2),
ename char(30))
)
location ('ext.csv'))
parallel 5
reject limit 100
Even if I omit the Reject rows with .. clause I am not able to get the row with all nulls.
Please help.
Thankyou.
|
|
|
|
Re: External table does not allow nulls by default [message #340430 is a reply to message #340417] |
Tue, 12 August 2008 17:19  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The external table does allow nulls by default, but it still expects commas between those null values. If you do not have the commas, then you need to add MISSING FIELD VALUES ARE NULL. Please see the demonstration below.
-- c:\oracle11g\ext.csv:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY ext AS 'c:\oracle11g'
2 /
Directory created.
SCOTT@orcl_11g> CREATE TABLE test_table1
2 (
3 empno number(2),
4 ename char(30)
5 )
6 ORGANIzATION EXTERNAL
7 (TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY ext
9 --abcd is a comment
10 ACCESS PARAMETERs(records delimited by newline
11 badfile 'bad_emp'
12 logfile 'log_emp'
13 FIELDS TERMINATED BY ','
14 MISSING FIELD VALUES ARE NULL
15 (empno integer external(2),
16 ename char(30))
17 )
18 location ('ext.csv'))
19 parallel 5
20 reject limit 100
21 /
Table created.
SCOTT@orcl_11g> SELECT * FROM test_table1
2 /
EMPNO ENAME
---------- ------------------------------
1 name1
2 name2
4 rows selected.
SCOTT@orcl_11g>
|
|
|