Home » RDBMS Server » Server Utilities » ORA-01400: cannot insert NULL (11.2.0.3)
ORA-01400: cannot insert NULL [message #634954] Wed, 18 March 2015 06:30 Go to next message
kuaeg
Messages: 5
Registered: August 2011
Junior Member
Here the crl file

LOAD DATA
INFILE *
BADFILE 'E:\project\Scripts\langkah_test.bad'
DISCARDFILE 'E:\project\Scripts\langkah_test.dsc'
APPEND
INTO TABLE IND_LANGKAH
FIELDS TERMINATED BY ","OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(LANGKAH_ID "SEQ_LANGKAH.nextval", CREATED_BY CONSTANT "SYSTEM", CREATED_DATE "CAST(SYSDATE AS TIMESTAMP)",
LAST_MODIFIED_BY CONSTANT "SYSTEM", LAST_MODIFIED_DATE "CAST(SYSDATE AS TIMESTAMP)",
VERSION CONSTANT "0", AKTIF CONSTANT "Y",
SKRIN_ID "get_skrinkod(:SKRIN_ID)",
TUGASAN_ID CONSTANT "1",
KOD CONSTANT "111", NAMA CONSTANT "test", PERIHAL CONSTANT "test",
KELENGKAPAN_MKLMT CONSTANT "N", TURUTAN CONSTANT "1")
BEGINDATA
"ABKMH"

**********************************************
create or replace function get_skrinkod(p_num in varchar2) return number
is
l_num number(19);
begin
select skrin_id into l_num FROM ind_skrin WHERE kod_skrin = p_num;
return l_num;
end;
***********************************************


SQL> select skrin_id FROM ind_skrin WHERE kod_skrin = 'ABKMH';

SKRIN_ID
----------
1

***********************************************

Error is
Record 1: Rejected - Error on table IND_LANGKAH, column SKRIN_ID.
ORA-01400: cannot insert NULL into ("ET_STAGING"."IND_LANGKAH"."SKRIN_ID")


***********************************************

Please help and advise me on this.

Thanks,
eg
sqlloader - how to load a field from database [message #634958 is a reply to message #634954] Wed, 18 March 2015 06:44 Go to previous messageGo to next message
kuaeg
Messages: 5
Registered: August 2011
Junior Member
Hi,

field1 "(select skrin_id FROM ind_skrin WHERE kod_skrin = :field1)"
BEGINDATA
123

I am trying to load skrin_id derive from data field :field1 which is 123
and I got error ORA-01400: cannot insert NULL into.

Anyone please help.

Regards,
Eye Gee
Re: ORA-01400: cannot insert NULL [message #634976 is a reply to message #634954] Wed, 18 March 2015 09:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Your columns in your control file need to be in the same order as those in your data file, with any columns that are obtained from sequences or constants after that. So, your skrn_id column needs to be first. Otherwise, it tries to read the value "ABKMH" into the first column and replaces that with the sequence. Then there are no other values for any other columns, including the skrn_id column.
Re: ORA-01400: cannot insert NULL [message #635006 is a reply to message #634976] Wed, 18 March 2015 19:41 Go to previous message
kuaeg
Messages: 5
Registered: August 2011
Junior Member
Barbara ,

Thank you. It works .

Regards,
Eye Gee
Previous Topic: Problem faced during export using parfile
Next Topic: impdp to different version
Goto Forum:
  


Current Time: Thu Mar 28 18:26:51 CDT 2024