Solved

ORA-01890: NLS error detected

Posted on 2006-06-28
9
2,025 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
  • 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
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!

 

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

679 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