0

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.

0

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.

1
  • Ok, it seems like there were some nls parameters being set via the login files. We removed those and seems to be working now, Thanks – f-z-N Nov 25 '14 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.