Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created one procedure that is supposed to trigger on 5th date of every month.for that i have used dba scheduler job.but its not triggering.

select job_name,status from dba_scheduler_jobs;

JOB_NAME STATE

BUY_UNITS SCHEDULED

CREATE TABLE  "SIPHOLDER" 
   (    "ACCNO" NUMBER, 
    "PORTFOLIONO" NUMBER, 
    "PORTFOLIONAME" VARCHAR2(4000), 
    "NAV" NUMBER, 
    "PERMONTHINVESTMENT" NUMBER, 
    "TOTALUNITSBOUGHT" NUMBER, 
    "STARTDATE" DATE, 
    "SELLDATE" DATE, 
    "FINALAMOUNT" NUMBER, 
    "RETURN" NUMBER, 
    "ECS" DATE, 
     CONSTRAINT "SIPHOLDER_CON" FOREIGN KEY ("ACCNO")
      REFERENCES  "SAVINGACCPERM" ("ACCNO") ENABLE
   )
CREATE TABLE  "SIPPARAM" 
   (    "EXITLOAD1" NUMBER NOT NULL ENABLE, 
    "SERVICETAX" NUMBER(3,0), 
    "TRANSACTIONTAX" NUMBER(3,0), 
    "EXITLOAD2" NUMBER(3,0), 
    "CAPITALGAIN" NUMBER(3,0)
   )
CREATE TABLE  "SIP_PRICE" 
   (    "PORTFOLIONO" NUMBER, 
    "PORTFOLIONAME" VARCHAR2(4000), 
    "DATEOF" DATE, 
    "NAV" NUMBER, 
     CONSTRAINT "SIP_PRICE_PK" PRIMARY KEY ("PORTFOLIONO", "DATEOF") ENABLE
   )
CREATE TABLE  "SAVINGACCPERM" 
   (    "ACCNO" NUMBER, 
    "PASSWORD" VARCHAR2(4000), 
    "APPNO" NUMBER, 
    "BALANCE" NUMBER(38,0) NOT NULL ENABLE, 
     CONSTRAINT "SAVINGACCPERM_PK" PRIMARY KEY ("ACCNO") ENABLE
   )
CREATE TABLE  "UPDATEDNAV" 
   (    "PORTFOLIONO" NUMBER, 
    "DATEOF" DATE, 
    "NAV" NUMBER(3,0), 
    "PORTFOLIONAME" VARCHAR2(4000)
   )

procedure:

create or replace procedure buyunits is
 mnav number;
 mpno varchar2(100); 
mtransactiontax number; 
mservicetax number; 
maccno number;
 mtotalamountleft number; 
mpermonthinvestment number;
 cursor ecursor is 
select accno from sipholder where EXTRACT(day from TO_DATE(ECS,'DD-MON-RR'))=5;
 begin FOR e IN ecursor LOOP select portfoliono, permonthinvestment into mpno, mpermonthinvestment from sipholder where accno= e.accno; select nav into mnav from sip_price where portfoliono = mpno; select servicetax, transactiontax into mservicetax, mtransactiontax from sipparam where rownum = 1;
 update savingaccperm set balance = balance-balance-mpermonthinvestment where accno = e.accno; 
mtotalamountleft := (mpermonthinvestment - (mpermonthinvestment*mtransactiontax)/100 - (mpermonthinvestment*mservicetax)/100); 
update sipholder set totalunitsbought= totalunitsbought +(mtotalamountleft)/mnav where accno= e.accno; end loop; end;

Scheduler job:

BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'BUY_UNITS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'BUYUNITS',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=monthly;bymonthday=5',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
share|improve this question
    
Edit your question with the output of 'select * from DBA_SCHEDULER_JOBS' –  Phil May 5 '13 at 5:47
    
JOB_NAME OWNER ------------------------------ ------------------------------ NEXT_RUN_DATE ----------------------------------------------------------------------------- LAST_RUN_DATE JOB_CLASS RUN_COUNT ----------------------------- ------------------------------ ---------- AUTO_TASKS_JOB_CLASS 0 TEST_FULL_JOB_DEFINITION NETBANKING 05-MAY-2013 16:47:09 20-APR-2013 20:09:07 DEFAULT_JOB_CLASS 2 BUY_UNITS NETBANKING –  HImadri May 5 '13 at 6:50
    
SQL> select job_name,state from dba_scheduler_jobs; JOB_NAME STATE ------------------------------ --------------- PURGE_LOG SCHEDULED FGR$AUTOPURGE_JOB DISABLED GATHER_STATS_JOB SCHEDULED AUTO_SPACE_ADVISOR_JOB DISABLED TEST_FULL_JOB_DEFINITION SCHEDULED BUY_UNITS SCHEDULED BUY_UNITS_TEN SCHEDULED BUY_UNITS_FIFTEEN SCHEDULED BUY_UNITS_TWENTY SCHEDULED BUY_UNITS_TWENTYFIVE SCHEDULED –  HImadri May 5 '13 at 6:58
    
Hi @HImadri, please edit your question to put that in there (and format it as code by selecting all of it and clicking on the {} button). There's no way to format things in comments so what you pasted is completely unreadable. –  Mat May 5 '13 at 9:15
    
Have you checked the scheduler job log to check whether it is running or not? –  David Aldridge May 5 '13 at 15:34
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.