ORA-01722 during SQL Loader load - Help!

ORA-01722 during SQL Loader load - Help!

Post by Dodge » Mon, 22 Oct 2001 05:59:24



After a fresh install of Personal Oracle 7.3.4 on a Win95 machine, and fresh
creation of the database, upon attempting to load tables with SQL Loader,
every record in every table which contains a decimal number is rejected with
ORA-01722. Records without decimal numbers load correctly.

Many other previously installed V7.3.4 PCs with the same database load the
same CTL files with no problems. It should be noted that we are installing a
completely new version of our associated application on a clean PC, and I
suspect I may have missed setting up something in either INITORCL.ORA or the
registry?

On the failing system, NLS_LANG = "AMERICAN"

Any assistance would be gratefully accepted.

Scott Morrow

 
 
 

ORA-01722 during SQL Loader load - Help!

Post by Ron Reid » Mon, 22 Oct 2001 06:53:48



> After a fresh install of Personal Oracle 7.3.4 on a Win95 machine, and fresh
> creation of the database, upon attempting to load tables with SQL Loader,
> every record in every table which contains a decimal number is rejected with
> ORA-01722. Records without decimal numbers load correctly.

> Many other previously installed V7.3.4 PCs with the same database load the
> same CTL files with no problems. It should be noted that we are installing a
> completely new version of our associated application on a clean PC, and I
> suspect I may have missed setting up something in either INITORCL.ORA or the
> registry?

> On the failing system, NLS_LANG = "AMERICAN"

> Any assistance would be gratefully accepted.

> Scott Morrow

According to the error manual, this error only occurs if a character is
not a "valid numeric literal".  So, what does your control file look
like?  What does your table look like?
--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.

 
 
 

ORA-01722 during SQL Loader load - Help!

Post by Sybrand Bakke » Mon, 22 Oct 2001 06:35:01



Quote:> After a fresh install of Personal Oracle 7.3.4 on a Win95 machine, and
fresh
> creation of the database, upon attempting to load tables with SQL Loader,
> every record in every table which contains a decimal number is rejected
with
> ORA-01722. Records without decimal numbers load correctly.

> Many other previously installed V7.3.4 PCs with the same database load the
> same CTL files with no problems. It should be noted that we are installing
a
> completely new version of our associated application on a clean PC, and I
> suspect I may have missed setting up something in either INITORCL.ORA or
the
> registry?

> On the failing system, NLS_LANG = "AMERICAN"

> Any assistance would be gratefully accepted.

> Scott Morrow

Looks like your NLS_TERRITORY is incorrect, or the regional settings of the
client don't match the Oracle settings
Most likely , and . have swapped

A new version of an application against a desupported version of the
database looks a bit insane to me, however.

Regards,

Sybrand Bakker, Senior Oracle DBA

 
 
 

ORA-01722 during SQL Loader load - Help!

Post by Dodge » Mon, 22 Oct 2001 22:07:58


Follow up - Problem solved! It turns out that NLS_LANG exists in multiple
registry keys.. One in the Oracle root, one in HOME0, and one in the Dev6i
registry root. I'd missed the one in the Oracle root, which was set to
"CANADIAN_ENGLISH"... instead of "AMERICAN"

Thanks for the replies...

Scott Morrow


Quote:> After a fresh install of Personal Oracle 7.3.4 on a Win95 machine, and
fresh
> creation of the database, upon attempting to load tables with SQL Loader,
> every record in every table which contains a decimal number is rejected
with
> ORA-01722. Records without decimal numbers load correctly.

> Many other previously installed V7.3.4 PCs with the same database load the
> same CTL files with no problems. It should be noted that we are installing
a
> completely new version of our associated application on a clean PC, and I
> suspect I may have missed setting up something in either INITORCL.ORA or
the
> registry?

> On the failing system, NLS_LANG = "AMERICAN"

> Any assistance would be gratefully accepted.

> Scott Morrow

 
 
 

ORA-01722 during SQL Loader load - Help!

Post by Dodge » Mon, 22 Oct 2001 22:15:39





> > After a fresh install of Personal Oracle 7.3.4 on a Win95 machine, and
> fresh
> > creation of the database, upon attempting to load tables with SQL
Loader,
> > every record in every table which contains a decimal number is rejected
> with
> > ORA-01722. Records without decimal numbers load correctly.

> > Many other previously installed V7.3.4 PCs with the same database load
the
> > same CTL files with no problems. It should be noted that we are
installing
> a
> > completely new version of our associated application on a clean PC, and
I
> > suspect I may have missed setting up something in either INITORCL.ORA or
> the
> > registry?

> > On the failing system, NLS_LANG = "AMERICAN"

> > Any assistance would be gratefully accepted.

> > Scott Morrow

> Looks like your NLS_TERRITORY is incorrect, or the regional settings of
the
> client don't match the Oracle settings
> Most likely , and . have swapped

> A new version of an application against a desupported version of the
> database looks a bit insane to me, however.

> Regards,

> Sybrand Bakker, Senior Oracle DBA

Unfortunately, being a contractor for the Canadian Dept. of National
Defense, I don't really have much choice as to the Oracle versions in use...
They are just now migrating from Win95 platforms to Win2k... With hundreds
of Win95 machines which can only run V7.3.4 and won't be upgraded to Win2K
for a year or so, regardless of whether Oracle has desupported it or not,
*I* have to continue to support it.

As mentioned in my follow-up post, I found the problem:

It turns out that NLS_LANG exists in multiple registry keys.. One in the
Oracle root, one in HOME0, and one in the Dev6i registry root. I'd missed
the one in the Oracle root, which was set to "CANADIAN_ENGLISH"... instead
of "AMERICAN"

Thanks for the response!

Scott Morrow
Oracle Developer