importing DBMS_SCHEDULER jobs - date formatting problem...

I have 2 10.2.0.4 Standard Edition 64-bit instances, on 2 separate servers - both running on Win2003 Server x64 OSs. One of these physical servers is Win2003 R2 - if that makes a difference.

I have a schema which I am trying to import into a database on each of these servers.

The schema import contains some DBMS_SCHEDULER jobs.

On Server A (the export was created from a database on Server A), the import runs clean. I have no invalid objects after the import.

But on Server B, I get an error with each of the DBMS_SCHEDULER jobs (job code is included below):

IMP-00017: following statement failed with ORACLE error 1830:
"BEGIN "
"dbms_scheduler.create_job('"DISTRIBUTION_WEDNESDAYS"',"
"job_type=>'PLSQL_BLOCK', job_action=>"
"'DECLARE"
" P_JOBCODE VARCHAR2(200);"
" P_USERID VARCHAR2(200);"
" P_RETURNCODE NUMBER;"
"BEGIN"
" P_JOBCODE := ''INT_BLNC'';"
" P_USERID := ''BATCH'';"
" SP_BATCH_MANAGER("
" P_JOBCODE => P_JOBCODE,"
" P_USERID => P_USERID,"
" P_RETURNCODE => P_RETURNCODE"
" );"
" IF P_RETURNCODE > -2 THEN"
" P_JOBCODE := ''FIN_RECON'';"
" P_USERID := ''BATCH'';"
" SP_BATCH_MANAGER("
" P_JOBCODE => P_JOBCODE,"
" P_USERID => P_USERID,"
" P_RETURNCODE => P_RETURNCODE"
" );"
" END IF;"
" IF P_RETURNCODE > -2 THEN"
" P_JOBCODE := ''DIST'';"
" P_USERID := ''BATCH'';"
" SP_BATCH_MANAGER("
" P_JOBCODE => P_JOBCODE,"
" P_USERID => P_USERID,"
" P_RETURNCODE => P_RETURNCODE"
" );"
" END IF;"
" END;'"
", number_of_arguments=>0,"
"start_date=>'01-JAN-10 03.00.00.000000 AM -06:00', repeat_interval=>
"
"'FREQ=DAILY; BYDAY=WED;'"
", end_date=>NULL,"
"job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE,
auto_drop=>TRUE,comments="
">"
"'Executes Internal balancing, recon and then distribution.'"
");"
"COMMIT; END;"

IMP-00003: ORACLE error 1830 encountered ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 2
IMP-00091: Above error occurred on the following function and object: CREATE DISTRIBUTION_WEDNESDAYS. Remaining PL/SQL blocks for this object will be skipped.

I understand what the 'date format' message indicates, but I can't see why this would import cleanly on one server, and not the other. I'm wondering if anyone has any ideas. Could it be that something in the OS environment is different between these servers, and I need to assert the NLS_DATE_FORMAT variable before the import? Should the start_date always be expressed with a to_date instead of a string?

The 'at line 2' is also throwing me, because the start_date isn't declared until later.

Thanks!!

BD Received on Mon Apr 12 2010 - 15:14:06 CDT