OraFAQ Forum: SQL & PL/SQL » dbms

Home » SQL & PL/SQL » SQL & PL/SQL » dbms_scheduler (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dbms_scheduler [message #637943]

Fri, 29 May 2015 23:47

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

i need to schedule a task using dbms_Scheduler, i want to run a program at 11pm ,everyday, when i checked in database its giving me the time as below
at 8:39.39 it giving me - 4 hours.how can i specify the correct schedule.


SQL> select systimestamp at time zone DBTIMEZONE FROM DUAL;

SYSTIMESTAMPATTIMEZONEDBTIMEZONE
---------------------------------------------------------------------------
30-MAY-15 04.39.39.631000 AM +00:00

Report message to a moderator

Re: dbms_scheduler [message #637944 is a reply to message #637943]

Fri, 29 May 2015 23:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

m.abdulhaq wrote on Fri, 29 May 2015 21:47

i need to schedule a task using dbms_Scheduler, i want to run a program at 11pm ,everyday, when i checked in database its giving me the time as below
at 8:39.39 it giving me - 4 hours.how can i specify the correct schedule.
SQL> select systimestamp at time zone DBTIMEZONE FROM DUAL;

SYSTIMESTAMPATTIMEZONEDBTIMEZONE
---------------------------------------------------------------------------
30-MAY-15 04.39.39.631000 AM +00:00

What does SYSDATE report?

Report message to a moderator

Re: dbms_scheduler [message #637945 is a reply to message #637943]

Fri, 29 May 2015 23:54

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

m.abdulhaq wrote on Sat, 30 May 2015 10:17

i need to schedule a task using dbms_Scheduler, i want to run a program at 11pm ,everyday

Use the calendering syntax:

'freq=daily; byhour=23; byminute=0; bysecond=0;'

See some good examples here.

Report message to a moderator

Re: dbms_scheduler [message #637947 is a reply to message #637944]

Sat, 30 May 2015 00:00

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Sysdate is giving me '30-MAY-2015'

Report message to a moderator

Re: dbms_scheduler [message #637948 is a reply to message #637947]

Sat, 30 May 2015 00:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

m.abdulhaq wrote on Fri, 29 May 2015 22:00

Sysdate is giving me '30-MAY-2015'

you can lead some folks to knowledge, but you can't make them think.


  1* select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual
SQL> /

TO_CHAR(SYSDATE,'YY
-------------------
2015-05-29 10:02:56

Report message to a moderator

Re: dbms_scheduler [message #637950 is a reply to message #637944]

Sat, 30 May 2015 00:06

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

when i use systimestamp.

SQL> SELECT systimestamp FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
30-MAY-15 09.02.40.313000 AM +04:00

Report message to a moderator

Re: dbms_scheduler [message #637951 is a reply to message #637950]

Sat, 30 May 2015 00:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If you insist on shooting yourself in the foot, you deserve the pain that results.

Report message to a moderator

Re: dbms_scheduler [message #637952 is a reply to message #637951]

Sat, 30 May 2015 00:13

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

What ?

Report message to a moderator

Re: dbms_scheduler [message #637953 is a reply to message #637945]

Sat, 30 May 2015 00:14

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

thanks lalit.

Report message to a moderator

Re: dbms_scheduler [message #637954 is a reply to message #637952]

Sat, 30 May 2015 00:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what is the difference between SYSDATE & SYSTIMESTAMP?

Report message to a moderator

Re: dbms_scheduler [message #637957 is a reply to message #637954]

Sat, 30 May 2015 00:46

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

they are similar but timestamp gives you more details like fraction seconds , hence i posted the later one.

Report message to a moderator

Re: dbms_scheduler [message #637959 is a reply to message #637957]

Sat, 30 May 2015 03:14

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post what is the REPEAT_INTERVAL and NLS_ENV for your job, the content of nls_database_parameters and nls_instance_parameters and the result of:

select sessiontimezone, dbtimezone from dual;

Report message to a moderator

Re: dbms_scheduler [message #637963 is a reply to message #637959]

Sat, 30 May 2015 22:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72325

"If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler uses the time zone of systimestamp when the job or window is enabled."

Report message to a moderator

Re: dbms_scheduler [message #637964 is a reply to message #637959]

Sat, 30 May 2015 23:05

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

hi,

Please find the details of job that i created along with details you require.I want to update the program at every 2am in the morning.



SQL> select j.job_name,
  2         substr(j.nls_env, instr(j.nls_env, 'NLS_DATE_LANGUAGE'), 40)
  3    from user_scheduler_jobs j;

JOB_NAME                       SUBSTR(J.NLS_ENV,INSTR(J.NLS_ENV,'NLS_DA
------------------------------ ----------------------------------------
PURGE_LOG                      NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='B
FGR$AUTOPURGE_JOB              NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='B
GATHER_STATS_JOB               NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='B
AUTO_SPACE_ADVISOR_JOB         NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='B
UPDF_EMP                       NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='B
UP_EMP                         NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='B


--nls_database_parameters

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.4.0


---nls_instance_parameters

SQL> SELECT * FROM nls_instance_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE


BEGIN
   DBMS_SCHEDULER.CREATE_JOB(
   JOB_NAME => 'updf_emp',
     JOB_TYPE => 'STORED_PROCEDURE',
    JOB_ACTION => 'RAKLIVE.UPD_EMP',
    START_DATE => SYSTIMESTAMP,
     REPEAT_INTERVAL =>  'FREQ=DAILY;BYHOUR=07;BYMINUTE=0;BYSECOND=0;',
     END_DATE => NULL,
    ENABLED => TRUE,
    COMMENTS => 'NEW' );
   END;

--actually the above job did not run at 7 am in the morning.

SQL> select sessiontimezone, dbtimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
DBTIME
------
+04:00
+00:00

--procedure.

CREATE OR REPLACE PROCEDURE RAKLIVE.upd_emp
AS
BEGIN
   UPDATE emp
      SET emp_name = sal,
          sal = sal + 100;
END;



---

LAST_START_DATE                                      REPEAT_INTERVAL                                   
---------------------------------------------------- --------------------------------------------
31-MAY-15 07.00.00.415000 AM +04:00                  FREQ=DAILY;BYHOUR=07;BYMINUTE=0;BYSECOND=0;       


-USER_SCHEDULER

NLS_ENV
---------------------------------------------------------------------------------------------------
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUM
ERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERIC
AN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF 
AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' 
NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'

[Updated on: Sun, 31 May 2015 00:34] by Moderator

Report message to a moderator

Re: dbms_scheduler [message #637966 is a reply to message #637964]

Sat, 30 May 2015 23:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>i need to schedule a task using dbms_Scheduler,

PLEASE make up your/our mind.

DBA_JOBS != DBMS_SCHEDULER & neither use SYSTIMESTAMP

Report message to a moderator

Re: dbms_scheduler [message #637967 is a reply to message #637966]

Sat, 30 May 2015 23:34

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

if not systimestamp then what should i use.Okay i am working on dbms_Scheduler.

Report message to a moderator

Re: dbms_scheduler [message #637968 is a reply to message #637967]

Sat, 30 May 2015 23:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when all else fails, Read The Fine Manual

http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin006.htm#ADMIN12062

You are not the first person to use DBMS_SCHEDULER to schedule some task

Report message to a moderator

Re: dbms_scheduler [message #637970 is a reply to message #637968]

Sun, 31 May 2015 00:20

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

thanks blackswan, but my query is very simple , i want to run the job at local UAE time 9 am , i even changed the job as per below but still its not working.


SQL>  BEGIN
  2      DBMS_SCHEDULER.CREATE_JOB(
  3      JOB_NAME => 'updf_emp',
  4        JOB_TYPE => 'STORED_PROCEDURE',
  5       JOB_ACTION => 'RAKLIVE.UPD_EMP',
  6   START_DATE => SYSTIMESTAMP AT TIME ZONE 'ASIA/MUSCAT',
  7   REPEAT_INTERVAL =>  'FREQ=DAILY;BYHOUR=09;BYMINUTE=0;BYSECOND=0;',
  8   END_DATE => NULL,
  9  ENABLED => TRUE,
 10  COMMENTS => 'NEW' );
 11  END;
 12  /

Report message to a moderator

Re: dbms_scheduler [message #637971 is a reply to message #637970]

Sun, 31 May 2015 00:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

m.abdulhaq wrote on Sat, 30 May 2015 22:20

thanks blackswan, but my query is very simple , i want to run the job at local UAE time 9 am , i even changed the job as per below but still its not working.


SQL>  BEGIN
  2      DBMS_SCHEDULER.CREATE_JOB(
  3      JOB_NAME => 'updf_emp',
  4        JOB_TYPE => 'STORED_PROCEDURE',
  5       JOB_ACTION => 'RAKLIVE.UPD_EMP',
  6   START_DATE => SYSTIMESTAMP AT TIME ZONE 'ASIA/MUSCAT',
  7   REPEAT_INTERVAL =>  'FREQ=DAILY;BYHOUR=09;BYMINUTE=0;BYSECOND=0;',
  8   END_DATE => NULL,
  9  ENABLED => TRUE,
 10  COMMENTS => 'NEW' );
 11  END;
 12  /

my car is not working.
tell me how to make my car go.

Report message to a moderator

Re: dbms_scheduler [message #637972 is a reply to message #637971]

Sun, 31 May 2015 00:35

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Sarcascm doesnt help .

Report message to a moderator

Re: dbms_scheduler [message #637973 is a reply to message #637972]

Sun, 31 May 2015 00:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

"not working."
provides ZERO useful details.

We don't know what you did or what you saw.

Garbage In, Garbage Out!

Report message to a moderator

Re: dbms_scheduler [message #637974 is a reply to message #637970]

Sun, 31 May 2015 00:50

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does mean "i even changed the job as per below but still its not working."?
What does this mean?
What do you see?
Always copy and paste what you do and get.

Report message to a moderator

Re: dbms_scheduler [message #637975 is a reply to message #637974]

Sun, 31 May 2015 01:22

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

thanks Michael, i used the following link of solomon's solution to solve my problem , now the schedule is working fine , actually we need to specify the time zone of current location while specifying the start date.

Thanks Blackswan.

http://www.orafaq.com/forum/t/188023/


BEGIN
   DBMS_SCHEDULER.create_job
          (job_name             => 'update_empl',
           start_date           => SYSTIMESTAMP AT TIME ZONE 'ASIA/DUBAI',
           repeat_interval      => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=17; BYSECOND=0;',
           end_date             => NULL,
           job_class            => 'DEFAULT_JOB_CLASS',
           job_type             => 'STORED_PROCEDURE',
           job_action           => 'RAKLIVE.UPD_EMP',
           comments             => 'Run at 10 P.M.uaw every ',
           enabled              => TRUE
          );
END;
/

Report message to a moderator

Re: dbms_scheduler [message #637978 is a reply to message #637975]

Sun, 31 May 2015 08:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>actually we need to specify the time zone of current location while specifying the start date.
so what stops you from doing so?
"current location" is for what entity?

Report message to a moderator

Re: dbms_scheduler [message #638012 is a reply to message #637978]

Mon, 01 June 2015 03:21

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Nothing is stopping , i applied the location and its working fine with me now.

Report message to a moderator

Previous Topic:	Recursive Hierarchy Help
Next Topic:	select first row after appying order by for multiple records

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 10 03:07:31 CDT 2024