Oracle 11g Scheduled JOB question

I have scheduled a job that calls a stored procedure function:
The job works but I cannot see the status of the job when I right click on the Job name under Jobs menu:
The error says:
ORA-01882: timezone region %s not found.

I created the job as in the attached code.
Do I need to update the code in Job?
I attached the error screen-shot.error screen
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name        => 'MyJobName',
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'MyProc.myFunc',
                            start_date      => to_timestamp_tz('02/16/2011 21:00:00 US/Central',
                                                               'mm/dd/yyyy hh24:mi:ss tzr'),
                            repeat_interval => 'FREQ=daily',
                            enabled         => true,
                            auto_drop       => false,
                            comments        => 'My table refresh');


END;

Open in new window

toookiAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Check the NLS parameters from the 'problem' database and the others that display properly.  It's likely a config issue between your client and the database in question.

select * from NLS_DATABASE_PARAMETERS;

I'm not a PL/SQL Developer person so cannot help with setting up its environment.  Do you have support with them?

You can tell if a job is running from the database views.  I'm not aware of any view that tracks how long a job took to run but I'm not an expert on DBMS_SCHEDULER and all it's options.
0
 
PlatoConsultantCommented:
SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

TO_TIMESTAMP_TZ('1999-12-0111:00:00-08:00','YYYY-MM-DDHH:MI:SSTZH:TZM')


i think you are missing the timizone format


 please try to use the select statement to  verify the time zone format

 thanks

0
 
PlatoConsultantCommented:
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
toookiAuthor Commented:
Thank you. But I still get the same error with the updated value. I attached the Job code.

BEGIN
--DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST', force => TRUE);
DBMS_SCHEDULER.CREATE_JOB(job_name        => 'TEST',
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'MyProc.myFunc',
                            start_date      => to_timestamp_tz('2011-02-20 21:00:00 -6:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
                            repeat_interval => 'FREQ=daily',
                            enabled         => true,
                            auto_drop       => false,
                            comments        => 'TEST');


END;

Open in new window

0
 
toookiAuthor Commented:
I tried with this too and got same error.
BEGIN
--DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST', force => TRUE);
DBMS_SCHEDULER.CREATE_JOB(job_name        => 'TEST',
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'MyProc.myFunc',

                            start_date      => to_timestamp_tz('2011-02-20 21:00:00-6:00', 'YYYY-MM-DD HH24:MI:SSTZH:TZM'),
                            repeat_interval => 'FREQ=daily',
                            enabled         => true,
                            auto_drop       => false,
                            comments        => 'TEST');

Open in new window

0
 
toookiAuthor Commented:
Even if I try this , I get error (same error).
BEGIN
--DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST', force => TRUE);
DBMS_SCHEDULER.CREATE_JOB(job_name        => 'TEST',
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'REPORT_TEST.load_test',
                            start_date      => to_timestamp_tz('01/13/2011 14:00:00 UTC', 'mm/dd/yyyy hh24:mi:ss tzr'),
                            repeat_interval => 'FREQ=daily',
                            enabled         => true,
                            auto_drop       => false,
                            comments        => 'TEST');


END;

Open in new window

0
 
toookiAuthor Commented:
SELECT * from V$TIMEZONE_NAMES;
shows:
TZNAME   TZABBREV
--------------------------------
.........................
US/Central      CDT
UTC                 GMT
....................
...................
0
 
slightwv (䄆 Netminder) Commented:
>>The job works

If the job is running then I don't think the problem is with the job creation.

>>I right click on the Job name under Jobs menu:

The problem might be with he tool you are using.  What software are you using to 'right click' on the jobs menu?
0
 
toookiAuthor Commented:
I am using PL/SQL developer.
The job runs. But I do not know how long it took to run or if it is running -- these information. This comes when I highlight the Job name -->Right click->"Edit". This happens with PL/SQL developer on other oracle databases. But this database is exception.

I attached the screen-shots.

Is there any way to query the database to get the details?  screen  screen screen
0
 
toookiAuthor Commented:
I also have SQL developer. If I use that I right click on the Job Name->"Open". Then it shows basic information about the Job. Creator/create date/Valid/etc. Not previous run details/etc. I attached the screen. screen
0
 
toookiAuthor Commented:
The parameters are identical on both databases..... parameter
0
 
toookiAuthor Commented:
It could be some specific issue with PL/SQL developer.
I could get the details via SQL developer.
Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.