ewang1205
asked on
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_num
END LOOP;
END;
/
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_num
END LOOP;
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
did you sovle the problem by that?
ASKER
Still running. It takes about 2 hours to run. I will let you know. Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
declare
a varchar2(10);
begin
a := 'test';
end;
/