---------------------------------------------------------------------------------
--
-- 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