Tags: coldfusion, ctx_ddlsync_index, database, insert, mysql, needing, oracle, package, page, procedure, sql, table
ctx_ddl.sync_index in procedure
i am needing to put this package into a procedure so it can be called from an coldfusion page on insert of data into the table. however i keep getting errors, can anyone help?
The procedure has created without errors -
CREATE OR REPLACE PROCEDURE REBUILD_TEXT_INDEX(INDEX_NAME IN VARCHAR) AS
VSTATEMENT := 'ctx_ddl.sync_index( idx_name => ''||INDEX_NAME||'');';
EXECUTE IMMEDIATE VSTATEMENT;
but i get errors when trying to call the procedure
ft> exec REBUILD_TEXT_INDEX('ADDRESS_IDX');
BEGIN REBUILD_TEXT_INDEX('ADDRESS_IDX'); END;
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "FRONTDBA.REBUILD_TEXT_INDEX", line 5
ORA-06512: at line 1
i have tried various rejigging of the syntax and have tried removing the parameter and hardcoding the name but still the same error.
Leave a comment...
- 4 Comments
What is your reason for using NDS to execute the package. Do this instead:
CREATE OR REPLACE PROCEDURE sync_text_index (
i_index_name IN VARCHAR2)
ctx_ddl.sync_index( idx_name => i_index_name);
But, this is probably not the best way to handle the sync. Are you on 10g? If so, you can set sync to happen automatically, either on commit or on an interval. It is done as part of the index creation and would be in the 10g Oracle Text app developer's guide or reference.
-Ron#1; Sat, 23 Feb 2008 12:30:00 GMT
- cheers i'll have a look at how i set up the sync automatically.#2; Sat, 23 Feb 2008 12:31:00 GMT
- I agree with Ron that I do not see a real need to use NDS, but the reason your statement fails is that the code you are trying to execute is not valid. You would need to prefix it with EXECUTE or surround it in a anonymous PL/SQL block. So, either:
VSTATEMENT := 'exec ctx_ddl.sync_index( idx_name => ''||INDEX_NAME||'');';
VSTATEMENT := 'begin ctx_ddl.sync_index( idx_name => ''||INDEX_NAME||''); end;';
But anyway, using a procedure or the native scheduling in 10g would be better.
Rob#3; Sat, 23 Feb 2008 12:32:00 GMT
- The index was set up already i was just trying to use, but after looking into it a bit further the context one wasn't the best to use for us, so i have changed this to ctxcat, so my sync issue is no longer an issue.
Thanks#4; Sat, 23 Feb 2008 12:33:00 GMT