Solved

Oracle 11g Scheduled JOB question

Posted on 2011-02-19
12
1,701 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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 77

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 77

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

739 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