-- Entity Extraction example code -- This is designed to be run in SQL*Plus -- it will NOT work properly in SQL Developer -- It expects to be run as a user "enttest" with password "enttest" -- change the call to ctxload if using a different user -- set echo on so we can see what's happening set echo on -- declare SQL*Plus variables to hold our CLOBs variable mydoc clob variable outclob clob -- write the text to be processed into the mydoc variable begin :mydoc:='New York, United States of America

'||chr(10)|| 'The Dow Jones Industrial Average climbed by over 5% yesterday on news of a new software release from database giant Oracle Corporation.'; end; / -- Part 1 : default entity extraction ------------------------------------- -- drop the policy if it already exists (expect an error the first time you run this) exec ctx_entity.drop_extract_policy('mypolicy') -- Create a temporary lob for the output XML -- Create a default extraction policy -- Run entity extraction begin dbms_lob.createtemporary( :outclob, true ); ctx_entity.create_extract_policy( 'mypolicy' ); ctx_entity.extract( 'mypolicy', :mydoc, 'english', :outclob ); end; / -- SQL*Plus settings to make sure the XML gets displayed tidily set long 50000 set pagesize 60 -- Now display the output XML. Casting it to an XMLType gives us a nicely -- formatted display select xmltype(:outclob) from dual; -- Part 2 : extracting only certain entity types ------------------------------------------------ -- assumes we've already run part 1 so our temp lob and our policy are created begin ctx_entity.extract( 'mypolicy', :mydoc, 'english', :outclob, 'city, country' ); end; / select xmltype(:outclob) from dual; -- Part 3 : adding a custom rule -------------------------------- -- This adds to previous examples by adding a rule to our policy -- The policy must be compiled after the rule has been added begin ctx_entity.add_extract_rule( 'mypolicy', 1, '' || '' || '((climbed|gained|jumped|increasing|increased|rallied)' || '( (by|over|nearly|more than))* \d+(\.\d+)?( percent|%))' || '' || 'xPositiveGain' || ''); ctx_entity.compile( 'mypolicy' ); ctx_entity.extract( 'mypolicy', :mydoc, 'english', :outclob ); end; / select xmltype(:outclob) from dual; -- Part 4 : adding a dictionary ---------------------=--------- -- Add a custom dictionary from the external file "dict.load" host ctxload -user enttest/enttest -extract -name mypolicy -file dict.load -- compile the policy again and run extraction begin ctx_entity.compile( 'mypolicy' ); ctx_entity.extract( 'mypolicy', :mydoc, 'english', :outclob ); end; / select xmltype(:outclob) from dual; -- Now fetch that "SQL-style" column offset format 9999 column source format a20 column type format a20 column text format a30 select xtab.offset, xtab.text, xtab.type, xtab.source from xmltable( '/entities/entity' PASSING xmltype( :outclob ) COLUMNS offset number PATH '@offset', lngth number PATH '@length', text varchar2(50) PATH 'text/text()', type varchar2(50) PATH 'type/text()', source varchar2(50) PATH '@source' ) as xtab order by offset;