Re: DML job queue problem

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.