Solved

Oracle 11g Scheduled JOB question

Posted on 2011-02-19
12
1,640 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
 

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
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.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
case statement in where clause with not exist 15 46
Oracle - Create Procedure with Paramater 16 57
Oracle SQL 6 48
selective queries 7 21
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now