5 Replies Latest reply: Feb 27, 2013 2:32 AM by Gurjeet RSS

    how can i upload document while recreation of domain index

    Gurjeet
      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