---------------------------------------------------------------------------------------------------------------- -- WATERS CORPORATION -- -- VP-ELN Content Search Terms, release 2 -- -- This script produces a report of all searchable terms for the contents of an ELN document; -- optionally, the report can be limited to the search terms for a single document section. -- -- EXPECTED INPUTS: -- 1. (console, required) an ELN document ID. -- 2. (console, optional) a section number within the ELN document. -- -- EXPECTED OUTPUTS: -- 1. (console and file) a report of the indexed search terms for the ELN document or section. -- -- Recommended execution environment: command-line SQL*Plus client logged in as the System user. -- -- MINIMUM PRIVILEGES TO RUN THIS SCRIPT: -- GRANT SELECT ON elnprod.measureorder TO ... -- GRANT SELECT ON elnprod.mo_method TO ... -- GRANT SELECT ON elnprod.systemvalues TO ... -- GRANT SELECT ON elnprod.dr$ctx_binarydocument$i TO ... -- GRANT SELECT ON elnprod.dr$ctx_binarydocument$k TO ... -- -- PERSON RELEASE DATE REASON -- MMORRISON 1 2015-07-21 Creation -- MMORRISON 2 2015-07-22 Display the status of the Content Search setting in ELN. Display a troubleshooting message -- in case of 0 search terms for a document or section. ----------------------------------------------------------------------------------------------------------------- -- ALTER SESSION SET NLS_DATE_FORMAT = "YYYY-MM-DD HH24:MI:SS"; set serveroutput on size 999999 linesize 100 pagesize 50 trimspool on trim on feedback off verify off echo off autoprint off showmode off; COLUMN file NEW_VALUE file NOPRINT SELECT 'LMS_ContentSearchTerms_r2_'||to_char(sysdate,'yyyy-mm-dd_hh24-mi-ss')||'.log' "file" FROM DUAL; spool &file ACCEPT DOC_ID CHAR DEFAULT ' ' PROMPT 'Please enter a document ID (case-sensitive) : ' ACCEPT SECTION_NO NUMBER DEFAULT 0 PROMPT 'Please enter a section number (leave blank for all sections): ' VARIABLE v_DocumentID VARCHAR2(500); VARIABLE v_SectionPositionNo NUMBER; VARIABLE v_DocumentOrderID VARCHAR2(500); VARIABLE v_InputsOK NUMBER; DECLARE v_Count PLS_INTEGER := 0; v_AlphaValue elnprod.systemvalues.alphavalue%TYPE; BEGIN :v_DocumentID := NVL('&DOC_ID', ' '); :v_SectionPositionNo := NVL('&SECTION_NO', 0); :v_InputsOK := 0; SELECT COUNT(*) INTO v_Count FROM elnprod.measureorder WHERE limsorderno = :v_DocumentID; IF (v_Count = 0) THEN DBMS_OUTPUT.PUT_LINE('Error: document ID ' || :v_DocumentID || ' does not exist in this database!'); ELSE DBMS_OUTPUT.PUT_LINE('.'); DBMS_OUTPUT.PUT_LINE('Document ID: ' || :v_DocumentID || ' exists in this database.'); SELECT orderid INTO :v_DocumentOrderID FROM elnprod.measureorder WHERE limsorderno = :v_DocumentID; IF (:v_SectionPositionNo = 0) THEN DBMS_OUTPUT.PUT_LINE('Section number not specified; retrieving search tokens for all sections in this document.'); :v_InputsOK := 1; ELSE SELECT COUNT(*) INTO v_Count FROM elnprod.mo_method WHERE orderid = :v_DocumentOrderID AND position = :v_SectionPositionNo; IF (:v_SectionPositionNo > 0 AND v_Count < 1) THEN DBMS_OUTPUT.PUT_LINE('Error: section number ' || :v_SectionPositionNo || ' does not exist for this document in this database!'); :v_InputsOK := 0; ELSE DBMS_OUTPUT.PUT_LINE('Section no.: ' || :v_SectionPositionNo || ' exists for this document in this database.'); :v_InputsOK := 1; END IF; END IF; END IF; SELECT alphavalue INTO v_AlphaValue FROM elnprod.systemvalues WHERE systemtypeid = 'PRODUCTOPTIONS' AND valuecode = 'FULLTEXTSEARCH'; DBMS_OUTPUT.PUT_LINE('.'); DBMS_OUTPUT.PUT_LINE('ELN Content search enabled: '||v_AlphaValue); IF (:v_InputsOK = 1) THEN DBMS_OUTPUT.PUT_LINE('.'); DBMS_OUTPUT.PUT_LINE('.'); DBMS_OUTPUT.PUT_LINE('Retrieving the content search terms ...'); END IF; END; / DECLARE v_SQLStatement VARCHAR2(4000); v_SearchTerm elnprod.dr$CTX_BINARYDOCUMENT$i.token_text%TYPE; v_Count PLS_INTEGER := 0; TYPE refcur IS REF CURSOR; C_ContentSearchTerms refcur; BEGIN IF (:v_InputsOK = 1) THEN v_SQLStatement := 'SELECT token_text FROM elnprod.dr$ctx_binarydocument$i WHERE token_first IN (SELECT docid FROM elnprod.dr$ctx_binarydocument$k WHERE textkey IN (SELECT rowid FROM elnprod.binarydocument WHERE binaryreference IN (SELECT resultfile FROM elnprod.mo_result_binary WHERE orderid = '; v_SQLStatement := v_SQLStatement || '' || :v_DocumentOrderID || ''; IF (:v_SectionPositionNo > 0) THEN v_SQLStatement := v_SQLStatement || ' AND methodno = (SELECT methodno FROM elnprod.mo_method WHERE orderID = ''' || :v_DocumentOrderID ||''' AND position = ''' || :v_SectionPositionNo ||''')'; END IF; v_SQLStatement := v_SQLStatement || '))) OR token_last IN (SELECT docid FROM elnprod.dr$ctx_binarydocument$k WHERE textkey IN (SELECT rowid FROM elnprod.binarydocument WHERE binaryreference IN (SELECT resultfile FROM elnprod.mo_result_binary WHERE orderid = '; v_SQLStatement := v_SQLStatement || :v_DocumentOrderID; IF (:v_SectionPositionNo > 0) THEN v_SQLStatement := v_SQLStatement || ' AND methodno = (SELECT methodno FROM elnprod.mo_method WHERE orderID = ''' || :v_DocumentOrderID ||''' AND position = ''' || :v_SectionPositionNo ||''')'; END IF; v_SQLStatement := v_SQLStatement || '))) ORDER BY token_text'; -- DBMS_OUTPUT.PUT_LINE(v_SQLStatement); -- DEBUG; re-enable this line if ever needed for debugging purposes. OPEN C_ContentSearchTerms FOR v_SQLStatement; LOOP FETCH C_ContentSearchTerms INTO v_SearchTerm; EXIT WHEN C_ContentSearchTerms%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_SearchTerm); v_Count := v_Count + 1; END LOOP; CLOSE C_ContentSearchTerms; DBMS_OUTPUT.PUT_LINE('.'); DBMS_OUTPUT.PUT_LINE('.'); DBMS_OUTPUT.PUT_LINE('Document ID: '||:v_DocumentID); IF (:v_SectionPositionNo > 0) THEN DBMS_OUTPUT.PUT_LINE('Section number: '||:v_SectionPositionNo); ELSE DBMS_OUTPUT.PUT_LINE('Section number: N/A'); END IF; DBMS_OUTPUT.PUT_LINE('Number of search terms: '||v_Count); IF (v_Count = 0) THEN DBMS_OUTPUT.PUT_LINE('.'); DBMS_OUTPUT.PUT_LINE('No search terms were found for this document or document section. If the ELN Content Search setting is True (see above), and no search terms are found, then use the schema_verify SQL script from Waters Support to dtermine whether: the Oracle Text option is installed in this database; the text sync and optimization jobs are active; and whether the CTX_BINARYDOCUMENT index is present. Also check the types of data in the document or section, as some types, like image files, cannot be indexed.'); END IF; END IF; END; / spool off