This discussion is archived
5 Replies Latest reply: Feb 27, 2013 12:32 AM by Gurjeet RSS

how can i upload document while recreation of domain index

Gurjeet Newbie
Currently Being Moderated
CREATE TABLE my_docs (
id NUMBER(10) NOT NULL,
name VARCHAR2(200) NOT NULL,
doc BLOB NOT NULL
);

ALTER TABLE my_docs ADD (
CONSTRAINT my_docs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE my_docs_seq;

CREATE OR REPLACE DIRECTORY documents AS 'C:\work';

CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name IN my_docs.name%TYPE) AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
RETURN doc INTO v_blob;

v_bfile := BFILENAME('DOCUMENTS', p_file_name);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);

COMMIT;
END;
/

EXEC load_file_to_my_docs('any_document_of_PDF_DOC_DOCX');



--upload any DOCUMENT OF size 10m APPROX. 4-5 documents.


CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('sync(on commit) ');

drop INDEX my_docs_doc_idx;

now recreate index on same column

CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('sync(on commit) ');


-------------- at same time in another session try to upload any document as above table
you will face ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

how can i upload document while recreation of domain index

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points