Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ORA-01890: NLS error detected

Posted on 2006-06-28
9
Medium Priority
?
2,103 Views
Last Modified: 2008-01-09
I got the following ERROR when running the following pl/sql.  It doesn't happen when I ran stand alone in sqlplus window.  It happens when I ran the whole large script which includes the following code.  Please help.

ERROR at line 1:
ORA-01890: NLS error detected
ORA-06512: at line 7


DECLARE
   CURSOR net_booked
   IS
      SELECT   SUM(burdened_cost) net, project_number, TO_CHAR(gl_date, 'RRRR') gl
          FROM afe_all GROUP BY project_number, TO_CHAR(gl_date, 'RRRR');
BEGIN
   FOR net_booked_rec IN net_booked
   LOOP
      UPDATE afe_rc a
         SET a.period_net_booked = net_booked_rec.net
       WHERE a.project_num = net_booked_rec.project_number AND a.period = net_booked_rec.gl;
   END LOOP;
END;
/
0
Comment
Question by:ewang1205
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Accepted Solution

by:
sathyagiri earned 1800 total points
ID: 17001055
What version of ORACLE are you running?

Also try modifying the RRRR format to YYYY in your TO_CHAR and see if that works.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17001323
could you run other pl/sql block ok, such as:

declare
   a varchar2(10);
begin
   a := 'test';
end;
/
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 200 total points
ID: 17001326
ORA-01890:      NLS error detected
Cause:      An NLS error was detected.
Action:      Look for additional error messages and take appropriate action. If there are no additional errors, contact Oracle Support Services.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ewang1205
ID: 17001354
This TO_CHAR(gl_date, 'YYYY') or TO_CHAR(gl_date, 'RRRR') probably is the problem.  I have changed to  substr(gl_date, 8, 2) instead of TO_CHAR(gl_date, 'RRRR') and rerunning.  
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17001390
did you sovle the problem by that?
0
 

Author Comment

by:ewang1205
ID: 17001405
Still running.  It takes about 2 hours to run. I will let you know.  Thanks.
0
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 1800 total points
ID: 17001433
If you are  9.0.1 or lesser , I think there is a bug in oracle, you will probably need to apply a patch or something.
0
 

Author Comment

by:ewang1205
ID: 17001447
It is 8.1.7.  Yes, I think there is a bug in Oracle.  But, let's see the result after finish the running.  My new code will work fine, let's not to bother the DBA to apply a patch just for this little thing.
0
 

Author Comment

by:ewang1205
ID: 17003543
The problem seems gone away after I change to substr(gl_date, 8, 2) from TO_CHAR(gl_date, 'YYYY').  Thanks for the help.  I will split the points.  
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question