Forum OpenACS Q&A: using dbms_job to sync interMedia indeces
I found out that my interMedia syncing dbms_job was failing. I defined the job like this:
connect yon/yon; declare v_job number; begin dbms_job.submit( job => v_job, what => 'ctx_ddl.sync_index(''forums_content_idx'');', interval => 'sysdate + 1/24' ); end; / show errors
I was getting the following errors in my oracle alert log:
Thu Jul 11 09:21:35 2002 Errors in file /home/oracle/ora8/u01/app/oracle/admin/ora8/bdump/snp4_31130.trc: ORA-12012: error on auto execute of job 7801 ORA-06550: line 1, column 96: PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared ORA-06550: line 1, column 96: PL/SQL: Statement ignored
I spent enough time debugging this that I thought it merited sharing what i learned. It turns out that dbms_jobs only inherit your schema user's default privileges, not any privileges it might inherit from roles granted to that user. This means that the job will not run with the ctxsys privilege that you should have granted to your schema user. So what does this mean? It means that you have to run the job as the ctxsys user, this is how you do it (you can also look at the forums/sql/oracle/forums-search-create.sql script):
-- as ctxsys connect ctxsys/ctxsys; declare v_job number; begin dbms_job.submit( job => v_job, what => 'ctxsys.ctx_ddl.sync_index(''yon.forums_content_idx'');', interval => 'sysdate + 1/24' ); end; / show errors
You might also want to make sure that you have the following parameters configured correctly in you init.ora: job_queue_processes (set to at least 1) and job_queue_interval (default is 60 seconds). Without these your dbms_jobs won't run at all.
grant ctxapp to yon;and then running the job as your user? It should work, and AFAIK it's the standard way to do such a task. I have InterMedia indexes successfully synchronized via dbms_job, and I never run the job as ctxsys.
Have you tried simply a grant ctxapp to yon;This doesn't work, because as Yon mentions, dbms_jobs do not inherit privileges granted through roles. "ctxapp" is a role. On the other hand, the needed "execute" privilege can be granted directly:
connect ctxsys/passwd grant execute on ctx_ddl to user;