Re: DML job queue problem

Thank you very much for the help, It's working now. It was actually a stupid mistake by me. I was placing the the time as my next_date thinking that it would just run continously, but I needed to set the interval as well... that was the problem, so I fixed it up, and it seems to be working fine now. This is all hit and miss for me because there isn't a whole lot of resources with examples out there.

I wouldn't have figured it out without your help.

Thanks again,

Craig

"mlm" <not_at_listed.com> wrote in message news:3AD4A5F0.36D6C22_at_listed.com...
> First, I believe that you need to have the keyword EXECUTE before
 'PROD_INDEX_SYNC;'
> to tell Oracle to run the procedure. Otherwise, it's like just entering
 the name of
> the proc at the SQL*Plus prompt. Nothing will happen, so Oracle probably
 is dropping
> the job since there is a syntax error. Also, the time that you are using
 '710/1440'
> seems weird to me. Can you explain what you are doing?
>
> Craig Taylor wrote:
>
> > Hi,
> >
> > I have created a .sql file which creates a strored procedure to sync my
> > intermedia index on a job queue. The problem I am having is that my jobs
> > seem to just stop, and disappear after a couple of days. Here is what I
> > do to set up my job queues:
> >
> > 1. start my ctx server unix command line prompt:
> > >ctxsrv -user ctxsys/ctxsys -personality M -log ctx.log &
> >
> > 2. load my .sql file:
> > ////////////STORED PROCEDURE
> >
> > CREATE OR REPLACE PROCEDURE PROD_INDEX_SYNC
> > IS
> > BEGIN
> > EXECUTE IMMEDIATE
> > 'ALTER INDEX PROD_INDEX' ||
> > ' REBUILD' ||
> > 'PARAMETERS ( ''SYNC'')' ;
> > END PROD_INDEX_SYNC;
> > /
> >
> > ///////////CREATE JOB QUEUE
> >
> > VARIABLE JOB1 NUMBER;
> > BEGIN
> > DBMS_JOB.SUBMIT
> > (:JOB1,
> > 'PROD_INDEX_SYNC;'.
> > ((SYSDATE +1) + 710/1440));
> > COMMIT;
> > END;
> > /
> >
> > Everything loads up fine, and the jobs show up when I select job from
> > user_jobs, but if I manually run it using DBMS_JOB.RUN(12); the job
> > disappears (when I select job from user_jobs). It also will just
> > disappear after a few days. I'm not really sure what is happening.
> >
> > I appreciate the help,
> >
> > Thanks,
> >
> > Craig
> >
> > --
> > One day Chao-chou fell down in the snow, and called out, "Help me up!
 Help me up!"
> > A monk came and lay down beside him. Chao-chao got up and went away.
>
> --
> The views expressed here are soley mine and do not represent my company in
 any way.
>
>
Received on Mon Apr 16 2001 - 11:00:25 CDT