Home » SQL & PL/SQL » SQL & PL/SQL » repeat_interval in oracle Schedule (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: XP Window )
repeat_interval in oracle Schedule [message #579978] Mon, 18 March 2013 09:45 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Hi, Need help.Can u tell me how set a Schedule or run a Job where repeat_interval should be null.

Like Below

DECLARE
-- Create jobs.
M_PROC varchar2(4000);
P_POL_SYS_ID NUMBER;
P_POL_END_NO_IDX NUMBER;
P_POL_END_SR_NO NUMBER;
JOB_NO NUMBER;
BEGIN
P_POL_SYS_ID := 5837;
P_POL_END_NO_IDX := 3;
P_POL_END_SR_NO := 0;

M_PROC := ' BEGIN ' ||
' PGIPK_END_HISTORY.PR_MOVE_TO_HIST_TAB(P_POL_SYS_ID => ' ||
P_POL_SYS_ID || ',' || ' P_END_NO_IDX => ' || P_POL_END_NO_IDX || ',' ||
' P_END_SR_NO => ' || P_POL_END_SR_NO || ');' || ' END;';

-- Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job(job_name => 'MMI_JOB',
job_type => 'PLSQL_BLOCK',
job_action => M_PROC,
start_date => SYSTIMESTAMP,
repeat_interval => null, --'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;


I am running now then Job is not getting created and not able to see it in SELECT * FROM dba_scheduler_jobs;
Re: repeat_interval in oracle Schedule [message #579981 is a reply to message #579978] Mon, 18 March 2013 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2  -- Create jobs.
  3  M_PROC varchar2(4000);
  4  P_POL_SYS_ID NUMBER;
  5  P_POL_END_NO_IDX NUMBER;
  6  P_POL_END_SR_NO NUMBER;
  7  JOB_NO NUMBER;
  8  BEGIN
  9  P_POL_SYS_ID := 5837;
 10  P_POL_END_NO_IDX := 3;
 11  P_POL_END_SR_NO := 0;
 12  
 13  M_PROC := ' BEGIN ' ||
 14  ' PGIPK_END_HISTORY.PR_MOVE_TO_HIST_TAB(P_POL_SYS_ID => ' ||
 15  P_POL_SYS_ID || ',' || ' P_END_NO_IDX => ' || P_POL_END_NO_IDX || ',' ||
 16  ' P_END_SR_NO => ' || P_POL_END_SR_NO || ');' || ' END;';
 17  
 18  -- Job defined entirely by the CREATE JOB procedure.
 19  DBMS_SCHEDULER.create_job(job_name => 'MMI_JOB',
 20  job_type => 'PLSQL_BLOCK',
 21  job_action => M_PROC,
 22  start_date => SYSTIMESTAMP,
 23  repeat_interval => null, --'freq=hourly; byminute=0',
 24  end_date => NULL,
 25  enabled => TRUE,
 26  comments => 'Job defined entirely by the CREATE JOB procedure.');
 27  END;
 28  /

PL/SQL procedure successfully completed.

SQL> select * from user_scheduler_jobs;
JOB_NAME                       JOB_SUBNAME                    JOB_CREATOR
------------------------------ ------------------------------ ------------------------------
CLIENT_ID                                                        GLOBAL_UID
---------------------------------------------------------------- --------------------------------
PROGRAM_OWNER
------------------------------------------------------------------------------------------------------------------------
PROGRAM_NAME
------------------------------------------------------------------------------------------------------------------------
JOB_TYPE
----------------
JOB_ACTION
------------------------------------------------------------------------------------------------------------------------
NUMBER_OF_ARGUMENTS
-------------------
SCHEDULE_OWNER
------------------------------------------------------------------------------------------------------------------------
SCHEDULE_NAME
------------------------------------------------------------------------------------------------------------------------
SCHEDULE_TYP START_DATE
------------ ---------------------------------------------------------------------------
REPEAT_INTERVAL
------------------------------------------------------------------------------------------------------------------------
EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT
------------------------------ ------------------------------ ------------------------------
EVENT_CONDITION
------------------------------------------------------------------------------------------------------------------------
EVENT_RULE
-----------------------------------------------------------------
END_DATE                                                                    JOB_CLASS                      ENABL AUTO_
--------------------------------------------------------------------------- ------------------------------ ----- -----
RESTA STATE           JOB_PRIORITY  RUN_COUNT   MAX_RUNS FAILURE_COUNT MAX_FAILURES RETRY_COUNT
----- --------------- ------------ ---------- ---------- ------------- ------------ -----------
LAST_START_DATE
---------------------------------------------------------------------------
LAST_RUN_DURATION
---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SCHEDULE_LIMIT
---------------------------------------------------------------------------
MAX_RUN_DURATION                                                            LOGG STOP_ INSTA
--------------------------------------------------------------------------- ---- ----- -----
RAISE_EVENTS
------------------------------------------------------------------------------------------------------------------------
SYSTE JOB_WEIGHT
----- ----------
NLS_ENV
------------------------------------------------------------------------------------------------------------------------
SOURCE
------------------------------------------------------------------------------------------------------------------------
DESTINATION
------------------------------------------------------------------------------------------------------------------------
COMMENTS
------------------------------------------------------------------------------------------------------------------------
     FLAGS
----------
MMI_JOB                                                       MICHEL



PLSQL_BLOCK
 BEGIN  PGIPK_END_HISTORY.PR_MOVE_TO_HIST_TAB(P_POL_SYS_ID => 5837, P_END_NO_IDX => 3, P_END_SR_NO => 0); END;
                  0


ONCE         18/03/2013 17:01:06.062 +01:00




                                                                            DEFAULT_JOB_CLASS              TRUE  TRUE
FALSE SCHEDULED                  3          0                        0                        0


18/03/2013 17:01:06.812 +01:00

                                                                            RUNS FALSE TRUE

FALSE          1
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,'
NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT='BINARY' NLS_TIME_F
ORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SS.FF3' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIME
STAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SS.FF3 TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='CHAR' NLS_
NCHAR_CONV_EXCP='FALSE'


Job defined entirely by the CREATE JOB procedure.
    139344

1 row selected.

It works for me.

Regards
Michel
Re: repeat_interval in oracle Schedule [message #579991 is a reply to message #579981] Mon, 18 March 2013 11:39 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

what is DB version you are using ?

I am using Oracle Database 10g Release 10.2.0.3.0 and even tried in Oracle Database 10g Release 10.2.0.4.0.
Re: repeat_interval in oracle Schedule [message #579992 is a reply to message #579991] Mon, 18 March 2013 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> @v

Version Oracle : 10.2.0.4.0

regards
Michel
Re: repeat_interval in oracle Schedule [message #579998 is a reply to message #579992] Tue, 19 March 2013 00:00 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Sad what could be the problem ? any privileged problem ? I have taken the dba privilege.
Re: repeat_interval in oracle Schedule [message #579999 is a reply to message #579998] Tue, 19 March 2013 00:11 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
> I have taken the dba privilege.
WRONG!
There is a difference between privilege & ROLE.
DBA is a ROLE.
Privileges acquired via ROLE do not apply within named PL/SQL procedures.
Re: repeat_interval in oracle Schedule [message #580000 is a reply to message #579999] Tue, 19 March 2013 00:21 Go to previous message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

i got the problem.Job was automatically getting dropped.When i checked using query select * from dba_scheduler_job_run_details where job_name = :JOB_ID. If we use auto_drop => FALSE then its not getting dropped. By the way thanks as usual for replying.
Previous Topic: pivot query
Next Topic: Vertical partitioning
Goto Forum:
  


Current Time: Thu Oct 02 05:37:30 CDT 2014

Total time taken to generate the page: 0.14269 seconds