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.

Collapse
Posted by Sebastiano Pilla on
Have you tried simply a
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.
Collapse
Posted by Andrew Grumet on
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;
Collapse
Posted by Yonatan Feldman on
i have updated the script to perform the grant on ctx_ddl that way the job can run as the normal user.