5 Replies Latest reply: Sep 20, 2006 5:19 PM by 440432 RSS

    Synchronize CONTEXT Index via Oracle FORMS

    445852
      Dear Colleague,

      when I attempt to synchronize an Oracle Text CONTEXT index from a button trigger in Oracle forms I get the error:

      ORA-20000: Oracle Text error:
      DRG-10017: you must be CTXSYS or RKMS_MGR to do this: SYNC
      ORA-06512: at "CTXSYS.DRUE", line 157
      ORA-06512: at "CTXSYS.CTX_DDL", line 1328
      ORA-06512: at line 1

      In the meantime, I implemented the following:

      1. created a stored procedure as the owner of the index which wraps ctx_ddl.sync_index
      2. granted execute on this procedure to the db users who are using the Oracle Forms code
      3. This user calls this stored procedure to sync index, but the same error occurs

      However, when this same DB user calls this stored procedure from SQL*Plus the synchronization works!

      What do I have to configure in Oracle Forms to make this work?

      Regards,

      Randy
        • 1. Re: Synchronize CONTEXT Index via Oracle FORMS
          rmhardma
          Hi,

          I saw you posted the same question to the Metalink forum for Text and received a response from support to use the workaround from note: 201027.1. It seems that this is what you implemented, correct?

          I know it is a complete hack, but perhaps having your button submit a job that performs the synchronization would work. Worth a try?? 6 degrees of separation...

          btw - what version of the db are you on? The note stated that the issue would be taken care of in 10g, so I'm interested whether that is in fact the case.

          -Ron
          • 2. Re: Synchronize CONTEXT Index via Oracle FORMS
            445852
            Dear Ron,

            Thanks for the response.

            I was on vacation last week, so I have not yet implemented the suggestion from note: 201027.1

            However, after reading through it, I noticed the use of the proposed DB procedure, namely:

            create or replace procedure i_doc_sync
            is
            begin
            ctx_ddl.sync_index( 'ANYUSER.TEXT__INDEX');
            end;
            /

            uses ANYUSER. Is this simply the DB schema that owns the index TEXT__INDEX or is ANYUSER a special Oracle DB schema that enables all users to alter the underlying index?

            If this is not the case, then I was considering a solution to grant the privilege ALTER INDEX on this TEXT_INDEX to those users of the application. Would that work/help?

            I am currently using the following DB release:

            Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

            Regards,

            Randy
            • 3. Re: Synchronize CONTEXT Index via Oracle FORMS
              445852
              Dear Ron,

              Thanks for the response.

              I was on vacation last week, so I have not yet implemented the suggestion from note: 201027.1

              However, after reading through it, I noticed the use of the proposed DB procedure, namely>

              create or replace procedure i_doc_sync
              is
              begin
              ctx_ddl.sync_index( 'ANYUSER.TEXT__INDEX');
              end;
              /

              uses ANYUSER. Is this simply the DB schema that owns the index TEXT__INDEX or is ANYUSER a special Oracle DB schema that enables all users to alter the underlying index?

              If this is not the case, then I was considering a solution to grant the privilege ALTER INDEX on this TEXT_INDEX to those users of the application. Would that work/help?

              I am currently using the following DB release:

              Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

              Regards,

              Randy
              • 4. Re: Synchronize CONTEXT Index via Oracle FORMS
                rmhardma
                Hi,

                ANYUSER in this example is just referring to the owner of the index you created. So, if I have a schema called MY_SCHEMA, I'd use that in place of ANYUSER.

                Regarding the grant - you mentioned that you could run this from SQL*Plus, but just could not execute it from the form. Were you logged in as the same user that the form is using? If so, then the grant shouldn't be an issue (same user, same permissions).

                They indicated in that note that there is a known bug prior to 10g where direct execution of the sync from something other than SQL*Plus (a job as demonstrated in the note) was generating the error message you received. I haven't personally run into this, but my 9i db is on the highest patchset, so they might have resolved it in one of the patches.

                I guess if I were in your shoes, I would try what they have in the note to see if I could reproduce it (create a job that executes sync) on the version you are on. If you can reproduce it, then follow their instructions for the workaround, but if not, post back here and we can see if there is another way around this.

                -Ron
                • 5. Re: Synchronize CONTEXT Index via Oracle FORMS
                  440432
                  This is a little late to the thread, but I'll reply for any one that may still encounter this isssue.

                  I encountered this exact same issue with ctxsys.sync in db ver 9.2.0.4. It would work from sqlplus for any user that had the privileges but would not work from a Forms screen resulting in an error similar to the first post:

                  DRG-10017: you must be CTXSYS or RKMS_MGR to do this: SYNC
                  ORA-06512: at "CTXSYS.DRUE", line 157
                  ORA-06512: at "CTXSYS.CTX_DDL", line 1328
                  ORA-06512: at line 1

                  I attempted the workaround described in Note:201027.1, but it creates a periodically running job. What was desired was a sync upon request via a button in Forms.
                  To facilitate that, we created two procedures in a package, one autonomous transaction that Forms can call which generates a job using dbms_job. That job calls the second procedure in the package which actually does the ctxsys.sync.

                  create or replace package body ama_ctxsync
                  as
                  -------------------------------------------------------------------------------
                  procedure sync
                  is
                  begin
                  for rec in (select distinct
                  PND_INDEX_OWNER
                  , PND_INDEX_NAME
                  from ctxsys.ctx_pending
                  where PND_INDEX_NAME in ('CTX_QUICK_TEXT')
                  )
                  loop
                  ctx_ddl.sync_index(rec.pnd_index_owner||'.'||rec.pnd_index_name);
                  ctx_ddl.optimize_index(rec.pnd_index_owner||'.'||rec.pnd_index_name,'FULL');
                  end loop;
                  end;

                  -------------------------------------------------------------------------------
                  procedure submitSync
                  is
                  pragma autonomous_transaction;
                  job number;
                  begin
                  dbms_job.submit(
                  job => job
                  ,what => 'begin ama_ctxsync.sync; end;'
                  ,next_date => sysdate
                  );

                  commit;
                  end;

                  end;

                  This method will burn a job sequence number each time, however, since the code is triggered by a Forms button, it is assumed there won't be much volume for the syncing any way. This is only a workaround unitl a patch or db upgrade solves the problem. Then the form can just call the sync procedure directly or refactor the submitSync procedure to no longer create a job and just call the sync procedure.

                  Andrew