OraFAQ Forum: Server Utilities » ORA-01400: cannot insert NULL

Home » RDBMS Server » Server Utilities » ORA-01400: cannot insert NULL (11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01400: cannot insert NULL [message #634954]

Wed, 18 March 2015 06:30

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

Report message to a moderator

sqlloader - how to load a field from database [message #634958 is a reply to message #634954]

Wed, 18 March 2015 06:44

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

Report message to a moderator

Re: ORA-01400: cannot insert NULL [message #634976 is a reply to message #634954]

Wed, 18 March 2015 09:19

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: ORA-01400: cannot insert NULL [message #635006 is a reply to message #634976]

Wed, 18 March 2015 19:41

kuaeg
Messages: 5
Registered: August 2011

Junior Member

Barbara ,

Thank you. It works .

Regards,
Eye Gee

Report message to a moderator

Previous Topic:	Problem faced during export using parfile
Next Topic:	impdp to different version

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:47:09 CST 2025