Solved

ORA-01890: NLS error detected

Posted on 2006-06-28
9
2,011 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

803 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