Monday, June 03, 2013

How To Run Oracle Text Query From Sqlplus

Sometimes we need to debug some Text queries, we need to run them on sqlplus
Here are some examples:

set serveroutput on;
 ALTER SESSION SET EVENTS '30579 trace name context forever, level 2';

 declare
 text_result clob;
 begin
dbms_lob.createtemporary(text_result, true, DBMS_LOB.CALL);
 ctx_query.result_set('TEST_IDCTEXT2', 'computer',
 '<ctx_result_set_descriptor><count exact="false"/><group
 sdata="sdDrillDown"><count exact="true"/></group><hitlist start_hit_num="1"
 end_hit_num="2" order="dInDate Desc"><sdata name="dID"/><sdata
 name="sddDocName"/><rowid /></hitlist></ctx_result_set_descriptor>',
 text_result);
 dbms_output.put_line(dbms_lob.substr(text_result,200,1));
 dbms_output.put_line('hello');
 dbms_lob.freetemporary(text_result);
 end;
 /

SELECT DDOCNAME  FROM idctext2 WHERE CONTAINS(otsmeta,  '((((DEFINESCORE((henry), RELEVANCE * .01)) ))) * 10.0 * 10.0')>0;


SELECT DDOCNAME  FROM idctext2 WHERE CONTAINS(otsmeta,  '((((DEFINESCORE((test), RELEVANCE * .1)) )))  and  (((((EMPL%) WITHIN
dDocAccount)) or (((CATEST1%) WITHIN dDocAccount)) or (((GIT/GLOBAL#0023IT#0023ONLY%) WITHIN dDocAccount)) or (((GIT/ALL%) WITHIN dDocAccount)) or (((idcnull) WITHIN dDocAccount)))) ')>0;

No comments: