ora: 1843: not a valid month

[starla0316]

Hello, I am trying to run this sql statement:

-- set echo on
whenever sqlerror exit 1;
set feedback off
set pages 0

--ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

--whenever sqlerror exit sql.sqlcode

Begin
-- update table
UPDATE PS_COMBO_BLD_REQ
SET AS_OF_DATE=TO_DATE(TO_CHAR(SYSDATE,'DD/MM/YYYY'), 'DD/MM/YYYY')
--SET AS_OF_DATE=SYSDATE
WHERE RUN_CNTL_ID='COMBO'
;

end;
/

commit;

I am running this sql via an Autosys Job. When I run the job, its status is failed, however, when I check on the table itself, it seems to have worked (the as_of_date is updated to the system date). However when I look at the log file, I am given this error:
Connected.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month
ORA-06512: at line 50

Any ideas on how to resolve this?

Thanks in advance

 

[Dagon]

What is your NLS_DATE_FORMAT set to ? You have a commented out line setting it to the wrong value. Try setting it to the right value:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

Since all you seem to be achieving is the removal of the time component from the system date, you could also use:

UPDATE PS_COMBO_BLD_REQ
SET AS_OF_DATE=TRUNC(SYSDATE)

 

[Dagon]

I suspect there may be more going on in your script than you have shown. The SQL you've got should work regardless of the setting of NLS_DATE_FORMAT. There aren't anywhere near 50 lines in the fragement of script you've shown, so there must be a lot more you've not shown. I suspect the error is coming from somewhere else other than the update.

 

[sem]

I suppose that the error was produced by Autosys Job itself. BTW what is it?

Regards, Dima

http://seminihin.narod.ru

 

[starla0316]

Thanks Dagon! We are trying your suggestion as of now. and no, that is the entire content of the sql script. Once we are able to update the as_of_date column, there are other processes that will run.
Thanks again and I'll let you know how it goes.

 

[starla0316]

Hi Sem! Autosys is our batch processing system. It runs jobs via "boxes", that contain command lines to run scripts/sqr's, etc at assigned times.This is produced by Computer Associates.

http://www.ca.com

 

[SantaMufasa]

Starla,

If you have posted all of the code that you believe is running, then you have a trigger on the 'PS_COMBO_BLD_REQ' table that has (or calls a package, procedure, or function that has) at least 50 lines. To identify triggers on your table, could you please run this query and post the results:

select trigger_name from dba_triggers where table_name = 'PS_COMBO_BLD_REQ';

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.