I have a full text database application with a simple procedure which inserts values into a table:
create or replace PROCEDURE put_file
(
p_file_name IN my_doc.file_name%TYPE,
p_upload_date IN my_doc.upload_date%TYPE,
p_filesize IN my_doc.filesize%TYPE,
p_filetype IN my_doc.filetype%TYPE,
p_creation_date IN my_doc.creation_date%TYPE,
p_modification_date IN my_doc.modification_date%TYPE,
p_accessed_date IN my_doc.accessed_date%TYPE
) AS
dummy pls_integer;
BEGIN
select count(*) into dummy from my_doc where file_name = p_file_name;
if dummy = 0
then
INSERT INTO my_doc (id, file_name, upload_date, filesize, filetype, content, creation_date, modification_date, accessed_date)
VALUES (my_doc_seq.NEXTVAL, p_file_name, p_upload_date, p_filesize, p_filetype, BFILENAME('DOCUMENTS',p_file_name), p_creation_date, p_modification_date, p_accessed_date);
end if;
ctx_ddl.sync_index; -- the error occurs here
COMMIT;
END;
In order to successfully execute this procedure, I provided all necessary rights to the current user:
grant resource, connect, ctxapp to my_user;
/
grant execute on ctxsys.ctx_adm to my_user;
grant execute on ctxsys.ctx_cls to my_user;
grant execute on ctxsys.ctx_ddl to my_user;
grant execute on ctxsys.ctx_doc to my_user;
grant execute on ctxsys.ctx_output to my_user;
grant execute on ctxsys.ctx_query to my_user;
grant execute on ctxsys.ctx_report to my_user;
grant execute on ctxsys.ctx_thes to my_user;
grant execute on ctxsys.ctx_ulexer to my_user;
... but, unfortunately, when I execute the procedure I receive the following error:
Error starting at line 1 in command:
EXECUTE put_file('text.txt', sysdate, 12, '.txt', sysdate, sysdate, sysdate);
Error report:ORA-20000: Oracle Text error: DRG-10017: you must be CTXSYS to do this: SYNC
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 847
ORA-06512: at "my_user.PUT_FILE", line 24
ORA-06512: at line 1
- 00000 - "%s"
*Cause: The stored procedure 'raise_application_error' was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.
Can you tell me what am I doing wrong, please?
Thank you,