---------------------------------------------------------------------------------
--
-- Demo of a USER_LEXER for Oracle Text 
--
-- Should be used in conjunction with user_lexer.c
-- This uses database demo user SCOTT, assumes SCOTT's password is TIGER,
-- and that account CTXSYS is unlocked and has password CTXSYS
--
-- User SCOTT must be granted CREATE ANY LIBRARY privilege for this to work
--
-- YOU MUST CHANGE THE CREATE LIBRARY STATEMENT A FEW LINES BELOW
-- to point to the library where you built the shared library
-- 
-- Unless your library location is in %ORACLE_HOME%\bin, you will have to 
-- modify the EXTPROC entry in your listener.ora to something like this:
--
--  (SID_DESC =
--    (SID_NAME = PLSExtProc)
--    (ORACLE_HOME = G:\oracle\ora92)
--    (ENVS=EXTPROC_DLLS=c:\myprojects\user_lexer\debug\user_lexer.dll)
--    (PROGRAM = extproc)
--
-- Tested on Windows, should work on Unix as well
--
-- Roger Ford, Oracle Text Group  roger.ford@oracle.com
--
-- Version      Date         By          Comments
-- Version 0.9  30 May 2003  roger.ford  Initial Version, not fully tested
-- Version 1.0   2 Jun 2003  roger.ford  Fully tested - more documentation 
--
----------------------------------------------------------------------------------

connect ctxsys/ctxsys

-- this is needed to use definition ctx_ulexer.wildcard_tab - role won't do it

grant execute on ctx_ulexer to scott;

connect scott/tiger

CREATE OR REPLACE LIBRARY user_lexer_lib IS
'c:\myprojects\user_lexer\debug\user_lexer.dll';
/

begin
  execute immediate ('drop table mytable');
  exception when others then null;
end;
/

create table mytable (pk number primary key, text clob);

insert into mytable values (1, '!!The quick brown fox jumps over the lazy dog');
insert into mytable values 
(2, 'Now... is the time! for @all _good_ men %%%% to +come+ to the aid of the party....');

begin
  ctx_ddl.drop_preference('my_user_lex_pref');
  exception when others then null;
end;
/

exec ctx_ddl.create_preference('my_user_lex_pref', 'user_lexer')
exec ctx_ddl.set_attribute('my_user_lex_pref', 'input_type', 'clob')  /* default anyway */
exec ctx_ddl.set_attribute('my_user_lex_pref', 'index_procedure', 'my_index_proc')
exec ctx_ddl.set_attribute('my_user_lex_pref', 'query_procedure', 'my_query_proc')

create or replace procedure my_index_proc (
  text       in            clob,
  tokens     in out nocopy clob,
  loc_needed in            boolean)
as language c
name "c_index_proc"
library user_lexer_lib
with context
parameters
(
  context,
  text,
  tokens,
  loc_needed
);
/
show errors

create or replace procedure my_query_proc (
  word       in            varchar2,
  wc_offsets in            varchar2,
  tokens     in out        varchar2)
as language c
name "c_query_proc"
library user_lexer_lib
with context
parameters
(
  context,
  tokens string,
  tokens INDICATOR,
  word string,
  wc_offsets string
);
/
show errors

grant execute on my_query_proc to ctxsys;
grant execute on my_index_proc to ctxsys;

connect ctxsys/ctxsys
-------------------------------------------------------------------
-- Procedures have to be owned by CTXSYS, but these will be stubs
-- to call the user's own procedures
-- In the query procedure, we extract the wildcard info from an
-- index by table (which is difficult, or impossible, to pass to an
-- external procedure) into a VARCHAR2.
-------------------------------------------------------------------

create or replace procedure my_index_proc (
  text       in            clob,
  tokens     in out nocopy clob,
  loc_needed in            boolean) is
begin
  scott.my_index_proc (text, tokens, loc_needed);
end;
/
show errors

create or replace procedure my_query_proc (
  word       in            varchar2,
  wc_offsets in            ctxsys.ctx_ulexer.wildcard_tab,
  tokens_out in out        varchar2
) is
  wc        varchar2(4000) := '';
  comma     varchar2(1) := '';
  i         int;
  counter   int := 0;
begin
  -- convert the wildcards index-by table into a simple
  -- command separated varchar
  i := wc_offsets.first;
  loop
    exit when i is null;
    wc := wc || comma || wc_offsets(i);
    comma := ','; 
    i := wc_offsets.next(i);
    counter := counter+1;
  end loop;
  scott.my_query_proc (word, wc, tokens_out);
end;
/
show errors

grant execute on my_index_proc to scott;
grant execute on my_query_proc to scott;

connect scott/tiger

set serverout on

-- Create an index on the test table and check the words that have been indexed

set echo on
create index mytableindex on mytable (text) indextype is ctxsys.context
parameters ('lexer my_user_lex_pref');

select token_text from dr$mytableindex$i;

set echo off

-- Now test highlighting

drop table mutab;
create table mutab (query_id number, document clob);

set echo on
exec ctx_doc.markup('mytableindex', '1', 'brown AND laz%', 'mutab', '1') 

select document from mutab;
set echo off

quit

OpenworldDatabaseBanner

Oracle has a very active research organization (Oracle Labs) that is charged to 'Identify, explore, and transfer new technologies that have the potential to substantially improve Oracle's business'. One part of the organization is the External Research Office (ERO). The ERO is charged to ' ... invest in research collaborations that fit Oracle's long-term strategic goals. These collaborations are between university researchers and engineers/researchers throughout Oracle's various organizations'. The ERO webpage lists numerous current and past collaborations. Oracle provides funds and direct interactions with highly experienced developers.

If you are interested in the ERO program please contact Steve Jeffreys at
eroadmin_us_grp@oracle.com

If you would like to explore opportunities for a research collaboration with the database team please contact Dieter Gawlick at
dieter.gawlick@oracle.com


or Garret Swart at
garret.swart@oracle.com
.
Oracle Database Cloud