OraFAQ Forum: Server Utilities » "Message 2100 not found" Errors

Home » RDBMS Server » Server Utilities » "Message 2100 not found" Errors

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

"Message 2100 not found" Errors [message #590762]

Mon, 22 July 2013 15:11

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

My environmental variables are set correctly.Please suggest why am I getting this error:

"Message 2100 not found; No message file for product=RDBMS, facility=UL"

ORACLE_HOME=C:\app\bhatiama\product\11.2.0\client_1\bin
PATH=C:\app\bhatiama\product\11.2.0\client_1\bin;C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Program Files\Dell\DW WLAN Card;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files\Dell\Dell Data Protection\Access\Advanced\Wave\Gemalto\Access Client\v5\;C:\Program Files (x86)\Security Innovation\SI TSS\bin\;C:\Program Files (x86)\Windows Live\Shared;C:\Program Files (x86)\Intel\OpenCL SDK\2.0\bin\x86;C:\Users\bhatiama\AppData\Local\Smartbar\Application\;C:\product\11.2.0\dbhome_2\opatch;C:\Program Files (x86)\Java\

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590763 is a reply to message #590762]

Mon, 22 July 2013 15:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>My environmental variables are set correctly
WRONG!
see below for correct value

SET ORACLE_HOME=C:\app\bhatiama\product\11.2.0\client_1

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590764 is a reply to message #590763]

Mon, 22 July 2013 15:19

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

I have changed it to what you suggested.But still same error message when I run my sql loader command
/usr2/home2/oracle/product/11.2.0.3/db/bin/sqlldr owbrep/*****@PLSExtProc control=/usr2/home2/adistest/h91ftp/temp/owb_test/loader.ctl log =/usr2/home2/adistest/h91ftp/temp/owb_test/loader.log

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590767 is a reply to message #590764]

Mon, 22 July 2013 15:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Mandeep2910 wrote on Mon, 22 July 2013 13:19

I have changed it to what you suggested.But still same error message when I run my sql loader command
/usr2/home2/oracle/product/11.2.0.3/db/bin/sqlldr owbrep/*****@PLSExtProc control=/usr2/home2/adistest/h91ftp/temp/owb_test/loader.ctl log =/usr2/home2/adistest/h91ftp/temp/owb_test/loader.log

how is it that above suggests some sort of *NIX file system, but previously PATH contained "C:\" drive?
The simple question that needs an answer is what is actually the ORACLE_HOME folder/directory?

You need to tell us!

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590769 is a reply to message #590767]

Mon, 22 July 2013 15:46

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Do I need to use sqlldr present in the client(my local drive) or the one present on server where the control file and data file is present?
I have set oracle_home to my client oracle home and using sqlldr from server directory(unix).Is it correct?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590771 is a reply to message #590769]

Mon, 22 July 2013 15:55

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

oracle_home=/usr2/home2/oracle/product/11.2.0.3/db

sqlldr command:
/usr2/home2/oracle/product/11.2.0.3/db/bin/sqlldr owbrep/*******@PLSExtProc control=/usr2/home2/adistest/h91ftp/temp/owb_test/loader.ctl log =/usr2/home2/adistest/h91ftp/temp/owb_test/loader.log

error:
Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL

[Updated on: Mon, 22 July 2013 15:56]

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590772 is a reply to message #590769]

Mon, 22 July 2013 15:56

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You need to set your ORACLE_HOME to whatever environment from which you are calling.
If you are calling sqlldr from local client machine, use local ORACLE_HOME.
\

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590775 is a reply to message #590772]

Mon, 22 July 2013 16:01

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

I am calling from server

sqlldr command:
/usr2/home2/oracle/product/11.2.0.3/db/bin/sqlldr owbrep/*******@PLSExtProc control=/usr2/home2/adistest/h91ftp/temp/owb_test/loader.ctl log =/usr2/home2/adistest/h91ftp/temp/owb_test/loader.log

But when I do echo $oracle_home
it displays blank.
Can I set this server oracle_home from my putty without admin rights?
Or do you suggest me to use my local sqlldr?

error:
Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590776 is a reply to message #590775]

Mon, 22 July 2013 16:05

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

To start with
>>But when I do echo $oracle_home
>>it displays blank.
is not the same as $ORACLE_HOME
and in unix, you need to export the variable.

export ORACLE_HOME=/your/path
export PATH=$PATH:$ORACLE_HOME/bin

[Updated on: Mon, 22 July 2013 16:06]

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590777 is a reply to message #590776]

Mon, 22 July 2013 16:11

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

export oracle_home='/usr2/home2/oracle/product/11.2.0.3/db'
echo $oracle_home
/usr2/home2/oracle/product/11.2.0.3/db
export PATH=$PATH:$ORACLE_HOME/bin
/usr2/home2/oracle/product/11.2.0.3/db/bin/sqlldr owbrep/*****@PLSExtProc control=/usr2/home2/adistest/h91ftp/temp/owb_test/loader.ctl log =/usr2/home2/adistest/h91ftp/temp/owb_test/loader.log
Message 2100 not found; No message file for product=RDBMS, facility=UL

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590779 is a reply to message #590777]

Mon, 22 July 2013 16:18

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>echo $oracle_home
>>/usr2/home2/oracle/product/11.2.0.3/db
>>export PATH=$PATH:$ORACLE_HOME/bin

Once again,
$ORACLE_HOME is not same as $oracle_home.

[Updated on: Mon, 22 July 2013 16:20]

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590780 is a reply to message #590779]

Mon, 22 July 2013 16:22

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

It succeeded.Thanks so much Mahesh.
Really appreciate

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590863 is a reply to message #590779]

Tue, 23 July 2013 11:34

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

I have one more question.I have 200 tables .Can I create just one control file to load these multiple data files to multiple tables?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #590867 is a reply to message #590767]

Tue, 23 July 2013 11:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Mandeep2910 wrote on Tue, 23 July 2013 09:35

I have one more question.I have 200 tables .Can I create just one control file to load these multiple data files to multiple tables?

I give up.
Can you?

How to know which file goes into which table?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591089 is a reply to message #590867]

Wed, 24 July 2013 13:26

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Can we access control files on a nfs mounted drive?
I mean my control files and data files are on nfs mounted drive(as the drive is readable only by oracle instance).So Can I use sql loader to move that into the database?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591090 is a reply to message #591089]

Wed, 24 July 2013 13:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What does Oracle know about nfs?
Either the files are accessible or they are not accessible.
You need to tell us which is true.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591094 is a reply to message #591090]

Wed, 24 July 2013 14:08

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

It is a nfs mount directory(/mnt/oraclefra/).My dba told me that
"The way a database directories work is that it is oracle server process that writes on your behalf.
You will be creating files there using oracle API and reading them using Oracle API.
"

But since I want to run sqldr on this directory I am not able to read these files even .
It says
"
SQL*Loader-522: lfiopn failed for file (/mnt/oraclefra/adis_etl_dir/MEMB_ENTITY.bad)
SQL*Loader-552: insufficient privilege to open file
SQL*Loader-509: System error: Permission denied
"

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591095 is a reply to message #590867]

Wed, 24 July 2013 14:10

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

ok.So I have created almost 200 control files.Now do i need to run my sqlldr command 200 times???can I just run it in loop?or in one statement?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591097 is a reply to message #591095]

Wed, 24 July 2013 14:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Now do i need to run my sqlldr command 200 times???can I just run it in loop?or in one statement?
yes

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591100 is a reply to message #591097]

Wed, 24 July 2013 15:11

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Ok. So I am getting this error when I see my log file
"column ASSOC_AMT.ORA-01722: invalid number"..And this is occurying because it is a decimal number like 245.56.
What should I do to resolve this?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591102 is a reply to message #591100]

Wed, 24 July 2013 15:21

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Please help.. Sad

Sad

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591104 is a reply to message #591100]

Wed, 24 July 2013 15:26

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What are your current numeric characters? Check with

select value from v$nls_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';

I guess that decimal character isn't a dot, but comma. Therefore, convert dot to comma either in input (CSV) file or use REPLACE function in control file or even TO_CHAR with applied NLS_NUMERIC_CHARACTERS option.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591105 is a reply to message #591104]

Wed, 24 July 2013 15:48

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Query results in .,
So it seems this is correct

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591107 is a reply to message #591105]

Wed, 24 July 2013 16:18

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Please help if someone knows the solution.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591127 is a reply to message #591107]

Thu, 25 July 2013 00:12

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Then it is something else that causes the error:Oracle

ORA-01722: invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591202 is a reply to message #591127]

Thu, 25 July 2013 08:25

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

My Bad file has first 2 records like this:

MEMB_NUMBER,ID_NUMBER,ASSIGNED_MEMB_NUMBER,ASSOC_AMT,ASSOC_TYPE,DATE_ADDED,DATE_MODIFIED,OPERATOR_NAME,USER_GROUP,LOCATION_ID,
0000000107,0000828633, ,1.5,J,22-FEB-02,12-JUN-02,MSUM080_MEMB_CONV,00,,
0000002301,0000800007, ,297.5,J,03-AUG-00,12-JUN-02,MSUM080_MEMB_CONV,00,,

My Log file says:

Record 1: Rejected - Error on table OWBREP.MEMB_ENTITY, column ID_NUMBER.
ORA-12899: value too large for column "OWBREP"."MEMB_ENTITY"."ID_NUMBER" (actual: 20, maximum: 10)

Record 2: Rejected - Error on table OWBREP.MEMB_ENTITY, column ASSOC_AMT.
ORA-01722: invalid number

Decription of target table:

memb_number varchar2(10 byte) y
id_number varchar2(10 byte) y
assigned_memb_number varchar2(15 byte) y
assoc_amt number(14,2) y
assoc_type char(1 byte) y
date_added date y
date_modified date y
operator_name varchar2(32 byte) y
user_group varchar2(2 byte) y
location_id number y

Can you please tell me why the sqlldr is throwing error?The data seems correct to me.

[Updated on: Thu, 25 July 2013 08:28]

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591203 is a reply to message #591202]

Thu, 25 July 2013 08:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Record 2: Rejected - Error on table OWBREP.MEMB_ENTITY, column ASSOC_AMT.
>ORA-01722: invalid number

MEMB_ENTITY does not seem to exist within target table?
Why this discrepancy?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591204 is a reply to message #591203]

Thu, 25 July 2013 08:36

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

The table name is memb_entity.And I have also specified the desciption of this table.
The columns which are throwing error are id_number(record 1) and ASSOC_AMT(record 2).

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591206 is a reply to message #591202]

Thu, 25 July 2013 08:44

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

You need to show us the ctl file as well.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591207 is a reply to message #591206]

Thu, 25 July 2013 08:45

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

The ctl file details are:

load data
infile '/mnt/oraclefra/adis_etl_dir/MEMB_ENTITY.csv'
INSERT INTO TABLE owbrep.MEMB_ENTITY

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

LOCATION_ID,
MEMB_NUMBER,
ID_NUMBER,
ASSIGNED_MEMB_NUMBER,
ASSOC_AMT,
ASSOC_TYPE,
DATE_ADDED,
DATE_MODIFIED,
OPERATOR_NAME,
USER_GROUP
)

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591213 is a reply to message #591207]

Thu, 25 July 2013 09:03

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Please suggest what needs to be corrected.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591214 is a reply to message #591213]

Thu, 25 July 2013 09:37

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

It worked. Smile

Smile

I rearranged the control file columns according to the table.
Thanks all for the help.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591345 is a reply to message #591214]

Fri, 26 July 2013 09:08

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Hi,

Now again I am facing problem..

I mean my log file throws error

"Record 2: Rejected - Error on table OWBREP.ADDRESS, column USER_GROUP.
ORA-12899: value too large for column "OWBREP"."ADDRESS"."USER_GROUP" (actual: 27, maximum: 2)"

and my record when I see in bad file is:

"0000810722,3,00000000,00000000,H,A,Y, , , , , ,1777 Hull Road, , , ,Mason,MI,48854, , , , ,12-MAR-02,N,0,,, ,00000000,0,FAC, , ,N,N, ,1777 Hull Road,Mason, MI 48854, , , , , , , ,12-MAR-02,10-FEB-05,FIX075_ADDR_VERSION_UPGRADE,00,,"

I have checked my control file and the table structure .They are same.

The problem is that I have an address field before this user_group column which has value like "Mason,MI,48854" .So sql loader is reading these as separate columns because of comma.That is why the order is getting messed up and I am getting this error.Can you suggest what should I do resolve this kind of error?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591346 is a reply to message #591345]

Fri, 26 July 2013 09:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can you suggest what should I do resolve this kind of error?
a) ensure input data does not contain any comma
b) use a different separator character other than a comma; like the pipe ("|") character to separate columns

Report message to a moderator

Re: "Message 2100 not found" Errors [message #591347 is a reply to message #591346]

Fri, 26 July 2013 09:45

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Thanks for the perfect solution.It worked.I changed the delimiter in my files to |.And now I am able to load the data in the tables even if my input data contains a comma.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #592464 is a reply to message #591347]

Thu, 08 August 2013 11:42

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

Hi,
I have one situation again.
My data is like
A90402|I|31-1060|Endowed Chair/Telecom Market|Endowed Chair in Telecommunication Marketing| |EO
And my control file says

load data
infile '/mnt/oraclefra/adis_etl_dir/ALLOCATION.csv'
INSERT INTO TABLE owbrep.ALLOCATION

FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

ALLOCATION_CODE,
STATUS_CODE,
ACCOUNT,
SHORT_NAME,
LONG_NAME,
LONG_NAME2,
CFAE_PURPOSE_CODE)
---
The problem is with this column "LONG_NAME2".It is not null.It has a space character.But still my sqlldr throws this error

Record 2: Rejected - Error on table OWBREP.ALLOCATION, column LONG_NAME2.
ORA-01400: cannot insert NULL into ("OWBREP"."ALLOCATION"."LONG_NAME2")

Please suggest what needs to be done.How to get rid of this null error as my column has a space character and this is to be loaded into the target table as it is.

Report message to a moderator

Re: "Message 2100 not found" Errors [message #592465 is a reply to message #592464]

Thu, 08 August 2013 12:02

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-01400: cannot insert NULL into (%s)
 *Cause: An attempt was made to insert NULL into previously listed objects.
 *Action: These objects cannot accept NULL values.

So, either give a value either remove the NOT NULL constraint/column property.

Regards
Michel

Report message to a moderator

Re: "Message 2100 not found" Errors [message #592467 is a reply to message #592465]

Thu, 08 August 2013 12:43

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

I am not passing null in that column.I want to write a space in that column.How to preserve those spaces?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #592470 is a reply to message #592467]

Thu, 08 August 2013 12:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am not passing null in that column
Oracle disagrees & I believe that Oracle reports reality better than you.

what gets loaded where after removing the NOT NULL column constraint?

Report message to a moderator

Re: "Message 2100 not found" Errors [message #592472 is a reply to message #592470]

Thu, 08 August 2013 13:01

DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan

Member

I resolved it by using the clause "preserve blanks" in the control file.So I am able to load the space character in the null column now.

Report message to a moderator

Previous Topic:	INCLUDE=TABLE:"IN (select name from maintenance.tt)" does not work
Next Topic:	expdp VS RMAN backup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:29:46 CST 2025