Solved

Oracle 11g Scheduled JOB question

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

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
corrupt Databases 9 79
SQL query for highest sequence 4 61
oracle sqlplus query delimiter 8 36
sql script with strange password string 1 42
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 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