Objective

This article contains an amended script for creating the index required for PowerOn Fusions Network Property Link feature.
The original script lives in /user/bin/sql_scripts/npl_address_script.sql, version 5.2.2.1.9 of PowerOn Fusion.
The script has been modified to, 1) Be run as system, 2) work on 11g, and just generally work as the original didn’t work straight up.
The changes required were:

  • Added ‘tcs.’ to npl_textstore property
  • Extra grants to TCS user.
  • Catch exception when dropping npl_textstore that doesn’t yet exist.
  • Change tablespaces used to TC_ADDR and TC_ADDR_I

Script

— Run as system
— Original is located at /user/bin/sql_scripts/npl_address_script.sql

GRANT CTXAPP TO TCS;
grant create trigger to tcs;
grant create TABLE to tcs;
alter user tcs quota unlimited on TC_ADDR_I;

EXEC DATABASE_UPGRADE.DROPINDEX(‘TCS’,’CD_ADDRESS_SEARCH_ADDR_IDX’);
EXEC DATABASE_UPGRADE.DROPINDEX(‘TCS’,’CD_ADDRESS_SEARCH_IDX’);
EXEC DATABASE_UPGRADE.DROPTABLE(‘TCS’,’CD_ADDRESS_SEARCH’);

BEGIN
  ctx_ddl.drop_preference(‘tcs.npl_textstore’);
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

CREATE TABLE TCS.CD_ADDRESS_SEARCH
(
ADDR_NO NUMBER(22) NOT NULL,
SEARCH_TEXT VARCHAR2(140)
)
TABLESPACE TC_ADDR;

COMMENT ON TABLE TCS.CD_ADDRESS_SEARCH
IS ‘OracleText address search data used by Network Property Link Manager’;

COMMENT ON COLUMN TCS.CD_ADDRESS_SEARCH.ADDR_NO
IS ‘Unique address number’;

COMMENT ON COLUMN TCS.CD_ADDRESS_SEARCH.SEARCH_TEXT
IS ‘Aggregate of street, town and postcode for flexible searching’;

EXEC DATABASE_UPGRADE.GRANTACCESS(‘TCS’,’CD_ADDRESS_SEARCH’);

BEGIN
ctx_ddl.create_preference(‘tcs.npl_textstore’, ‘BASIC_STORAGE’);
ctx_ddl.set_attribute(‘tcs.npl_textstore’,’I_TABLE_CLAUSE’,’tablespace TC_ADDR_I’);
ctx_ddl.set_attribute(‘tcs.npl_textstore’,’K_TABLE_CLAUSE’,’tablespace TC_ADDR_I’);
ctx_ddl.set_attribute(‘tcs.npl_textstore’,’R_TABLE_CLAUSE’,’tablespace TC_ADDR_I’);
ctx_ddl.set_attribute(‘tcs.npl_textstore’,’N_TABLE_CLAUSE’,’tablespace TC_ADDR_I’);
ctx_ddl.set_attribute(‘tcs.npl_textstore’,’I_INDEX_CLAUSE’,’tablespace TC_ADDR_I’);
ctx_ddl.set_attribute(‘tcs.npl_textstore’,’P_TABLE_CLAUSE’,’tablespace TC_ADDR_I’);
END;
/

CREATE INDEX
TCS.CD_ADDRESS_SEARCH_IDX ON TCS.CD_ADDRESS_SEARCH(SEARCH_TEXT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS (‘storage tcs.npl_textstore’);

CREATE INDEX
TCS.CD_ADDRESS_SEARCH_ADDR_IDX ON TCS.CD_ADDRESS_SEARCH(ADDR_NO)
TABLESPACE TC_ADDR_I;

INSERT INTO
TCS.CD_ADDRESS_SEARCH (addr_no, search_text)
SELECT
ca.addr_no, ca.street_name || ‘ ‘ || ca.town_name || ‘ ‘ || ca.postcode
FROM cd_addresses ca;

EXEC ctx_ddl.sync_index(‘TCS.CD_ADDRESS_SEARCH_IDX’);

COMMIT;

CREATE OR REPLACE TRIGGER tcs.cd_address_search_update
AFTER INSERT OR UPDATE OF
postcode, town_name, street_name
ON tcs.cd_addresses
FOR EACH ROW
DECLARE
new_search_text VARCHAR2(140);
BEGIN
new_search_text := :NEW.street_name || ‘ ‘ || :NEW.town_name || ‘ ‘ || :NEW.postcode;

IF (UPDATING) THEN
UPDATE tcs.cd_address_search
SET search_text = new_search_text
WHERE addr_no = :NEW.addr_no;
END IF;

IF (INSERTING) THEN
INSERT INTO tcs.cd_address_search (addr_no, search_text)
VALUES (:NEW.addr_no, new_search_text);
END IF;

END;
/

SHOW ERRORS

CREATE OR REPLACE TRIGGER tcs.cd_address_search_resync
AFTER
INSERT OR
UPDATE OF search_text
ON tcs.cd_address_search
DECLARE
i_job NUMBER;
BEGIN

— resync the index to reflect changes
dbms_job.submit(job=>i_job,
what=>’CTX_DLL_WRAPPER.ResyncSearchIndex;’,
next_date=>SYSDATE);

END;
/

SHOW ERRORS

CREATE OR REPLACE PACKAGE TCS.CTX_DLL_WRAPPER AS

PROCEDURE ResyncSearchIndex;

END;
/

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY TCS.CTX_DLL_WRAPPER AS

PROCEDURE ResyncSearchIndex
IS
BEGIN
ctx_ddl.sync_index(‘TCS.CD_ADDRESS_SEARCH_IDX’);
END;

END;
/

SHOW ERRORS

EXIT; 

Categories