Oracle – Resolving ORA-01830 Error for 11.2.0 Client

oracle

I have this piece of code: 

BEGIN
   DBMS_SCHEDULER.DROP_JOB (
   job_name => 'LOANSBUILD.LOANSNEWYORKCLOSE');
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  '***.LOANSNEWYORKCLOSE',
    job_type           =>  'PLSQL_BLOCK',
    job_action         =>  'begin loans_schedule_job.loans_close(TRUNC(SYSDATE), ''N''); end;',
    start_date         =>  '15-NOV-08 12.00.00.000000000 AM AMERICA/NEW_YORK',
    repeat_interval    =>    'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=16;BYMINUTE=0;BYSECOND=0',
    auto_drop          =>   FALSE,
    job_class          =>  'DEFAULT_JOB_CLASS',
    enabled             =>   TRUE,
    comments           =>  'Test.'
);
END;  
/
exit;

When the above is executed from an environment which has oracle sql client 10.2 installed , this goes fine but when the same is executed fron an environment that has 11.2 client installed, this fails as below:

BEGIN
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 2

The variable nls_date_format is set to 'DD-MON-RR' in the 10.2 environment and set to 'YYYY-MM-DD HH24:MI:SS' in the 11.2 env.

As this was getting compiled for 10.2 env, I updated nls_lang_date in the 11.2 env as well to make it 'DD-MON-RR' but even after that I get the same error. Is there anything else I should be setting.

Please note that I am sysadmin and as this code is getting compiled on of the server, my job is to ensure that it does on others as well. Which also means that I do not have permissions to update code.

Best Answer

Is possible that for the 10g client you either have a the date format set in a local login.sql file or set in the $ORACLE_HOME/sqlplus/admin/glogin.sql. If you can find this date formatting, then you should set it the same for your 11g client and it should work.

Essentially, when you put in a date string Oracle look at the nls_date_format to see how a date is formatted by default. If that is different than how you have it in a sting, Oracle might have an issue handling the date. you can also use the to_Date funtion to force a particular date format to work.

Related Question