Solved

ORA-01890: NLS error detected

Posted on 2006-06-28
9
2,033 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 450 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 50 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 450 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

Technology Partners: 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!

Question has a verified solution.

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

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

734 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