Home » SQL & PL/SQL » SQL & PL/SQL » External table does not allow nulls by default (Oracle9i Enterprise Edition Release 9.2.0.1.0)
icon4.gif  External table does not allow nulls by default [message #340417] Tue, 12 August 2008 14:03 Go to next message
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 #340420 is a reply to message #340417] Tue, 12 August 2008 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is in log file?

Regards
Michel

[Updated on: Tue, 12 August 2008 14:24]

Report message to a moderator

Re: External table does not allow nulls by default [message #340430 is a reply to message #340417] Tue, 12 August 2008 17:19 Go to previous message
Barbara Boehmer
Messages: 8635
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:
1,name1,
,,

2,name2,


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> 

Previous Topic: Accessing a table from a different database
Next Topic: how to find memory allocation of table
Goto Forum:
  


Current Time: Wed Dec 07 02:59:53 CST 2016

Total time taken to generate the page: 0.11545 seconds