Solved

Oracle 11g Scheduled JOB question

Posted on 2011-02-19
12
1,660 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:toooki
  • 8
  • 2
  • 2
12 Comments
 
LVL 11

Expert Comment

by:PlatoConsultant
ID: 34935668
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
 
LVL 11

Expert Comment

by:PlatoConsultant
ID: 34935673
0
 

Author Comment

by:toooki
ID: 34936184
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:toooki
ID: 34936187
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
 

Author Comment

by:toooki
ID: 34938192
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
 

Author Comment

by:toooki
ID: 34938207
SELECT * from V$TIMEZONE_NAMES;
shows:
TZNAME   TZABBREV
--------------------------------
.........................
US/Central      CDT
UTC                 GMT
....................
...................
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34939378
>>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
 

Author Comment

by:toooki
ID: 34940294
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
 

Author Comment

by:toooki
ID: 34940306
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 450 total points
ID: 34951878
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
 

Author Comment

by:toooki
ID: 35032706
The parameters are identical on both databases..... parameter
0
 

Author Comment

by:toooki
ID: 35032759
It could be some specific issue with PL/SQL developer.
I could get the details via SQL developer.
Thanks for your help.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question