Home » RDBMS Server » Server Utilities » "Message 2100 not found" Errors
"Message 2100 not found" Errors [message #590762] Mon, 22 July 2013 15:11 Go to next message
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\
Re: "Message 2100 not found" Errors [message #590763 is a reply to message #590762] Mon, 22 July 2013 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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
Re: "Message 2100 not found" Errors [message #590764 is a reply to message #590763] Mon, 22 July 2013 15:19 Go to previous messageGo to next message
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
Re: "Message 2100 not found" Errors [message #590767 is a reply to message #590764] Mon, 22 July 2013 15:38 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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!
Re: "Message 2100 not found" Errors [message #590769 is a reply to message #590767] Mon, 22 July 2013 15:46 Go to previous messageGo to next message
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?
Re: "Message 2100 not found" Errors [message #590771 is a reply to message #590769] Mon, 22 July 2013 15:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
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.
\
Re: "Message 2100 not found" Errors [message #590775 is a reply to message #590772] Mon, 22 July 2013 16:01 Go to previous messageGo to next message
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
Re: "Message 2100 not found" Errors [message #590776 is a reply to message #590775] Mon, 22 July 2013 16:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
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 Go to previous messageGo to next message
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
Re: "Message 2100 not found" Errors [message #590779 is a reply to message #590777] Mon, 22 July 2013 16:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
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 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
It succeeded.Thanks so much Mahesh.
Really appreciate
Re: "Message 2100 not found" Errors [message #590863 is a reply to message #590779] Tue, 23 July 2013 11:34 Go to previous messageGo to next message
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?
Re: "Message 2100 not found" Errors [message #590867 is a reply to message #590767] Tue, 23 July 2013 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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?
Re: "Message 2100 not found" Errors [message #591089 is a reply to message #590867] Wed, 24 July 2013 13:26 Go to previous messageGo to next message
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?
Re: "Message 2100 not found" Errors [message #591090 is a reply to message #591089] Wed, 24 July 2013 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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.
Re: "Message 2100 not found" Errors [message #591094 is a reply to message #591090] Wed, 24 July 2013 14:08 Go to previous messageGo to next message
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
"
Re: "Message 2100 not found" Errors [message #591095 is a reply to message #590867] Wed, 24 July 2013 14:10 Go to previous messageGo to next message
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?
Re: "Message 2100 not found" Errors [message #591097 is a reply to message #591095] Wed, 24 July 2013 14:30 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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
Re: "Message 2100 not found" Errors [message #591100 is a reply to message #591097] Wed, 24 July 2013 15:11 Go to previous messageGo to next message
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?
Re: "Message 2100 not found" Errors [message #591102 is a reply to message #591100] Wed, 24 July 2013 15:21 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Please help..Sad
Re: "Message 2100 not found" Errors [message #591104 is a reply to message #591100] Wed, 24 July 2013 15:26 Go to previous messageGo to next message
Littlefoot
Messages: 19537
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.
Re: "Message 2100 not found" Errors [message #591105 is a reply to message #591104] Wed, 24 July 2013 15:48 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Query results in .,
So it seems this is correct
Re: "Message 2100 not found" Errors [message #591107 is a reply to message #591105] Wed, 24 July 2013 16:18 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Please help if someone knows the solution.
Re: "Message 2100 not found" Errors [message #591127 is a reply to message #591107] Thu, 25 July 2013 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 19537
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.
Re: "Message 2100 not found" Errors [message #591202 is a reply to message #591127] Thu, 25 July 2013 08:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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?
Re: "Message 2100 not found" Errors [message #591204 is a reply to message #591203] Thu, 25 July 2013 08:36 Go to previous messageGo to next message
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).
Re: "Message 2100 not found" Errors [message #591206 is a reply to message #591202] Thu, 25 July 2013 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to show us the ctl file as well.
Re: "Message 2100 not found" Errors [message #591207 is a reply to message #591206] Thu, 25 July 2013 08:45 Go to previous messageGo to next message
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
)
Re: "Message 2100 not found" Errors [message #591213 is a reply to message #591207] Thu, 25 July 2013 09:03 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Please suggest what needs to be corrected.
Re: "Message 2100 not found" Errors [message #591214 is a reply to message #591213] Thu, 25 July 2013 09:37 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
It worked.Smile
I rearranged the control file columns according to the table.
Thanks all for the help.
Re: "Message 2100 not found" Errors [message #591345 is a reply to message #591214] Fri, 26 July 2013 09:08 Go to previous messageGo to next message
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?
Re: "Message 2100 not found" Errors [message #591346 is a reply to message #591345] Fri, 26 July 2013 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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
Re: "Message 2100 not found" Errors [message #591347 is a reply to message #591346] Fri, 26 July 2013 09:45 Go to previous messageGo to next message
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.
Re: "Message 2100 not found" Errors [message #592464 is a reply to message #591347] Thu, 08 August 2013 11:42 Go to previous messageGo to next message
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.
Re: "Message 2100 not found" Errors [message #592465 is a reply to message #592464] Thu, 08 August 2013 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58957
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: "Message 2100 not found" Errors [message #592467 is a reply to message #592465] Thu, 08 August 2013 12:43 Go to previous messageGo to next message
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?
Re: "Message 2100 not found" Errors [message #592470 is a reply to message #592467] Thu, 08 August 2013 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
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?
Re: "Message 2100 not found" Errors [message #592472 is a reply to message #592470] Thu, 08 August 2013 13:01 Go to previous message
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.
Previous Topic: INCLUDE=TABLE:"IN (select name from maintenance.tt)" does not work
Next Topic: expdp VS RMAN backup
Goto Forum:
  


Current Time: Tue Sep 02 07:53:29 CDT 2014

Total time taken to generate the page: 0.10091 seconds