Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2194
  • Last Modified:

ORA-01890: NLS error detected

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
ewang1205
Asked:
ewang1205
  • 4
  • 3
  • 2
3 Solutions
 
sathyagiriCommented:
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
 
actonwangCommented:
could you run other pl/sql block ok, such as:

declare
   a varchar2(10);
begin
   a := 'test';
end;
/
0
 
actonwangCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ewang1205Author Commented:
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
 
actonwangCommented:
did you sovle the problem by that?
0
 
ewang1205Author Commented:
Still running.  It takes about 2 hours to run. I will let you know.  Thanks.
0
 
sathyagiriCommented:
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
 
ewang1205Author Commented:
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
 
ewang1205Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now