Re: strip HTML tags from fields?

On Aug 10, 2:05 am, the matt <m..._at_kettlewell.net> wrote:
> Further investigation shows that I do have Oracle Text installed, but
> I don't seem to be able to get it to do what I want.
>
> I'm trying to take some HTML fields (varchar2) and strip out the HTML
> formatting. The Policy_Filter seems to want to use a Blob, but I
> don't have one to give it, so I've tried various variations of the
> code below.
>
> DECLARE
> output_c clob;
> input_b blob;
> BEGIN
> SELECT
> SARQUAN_QUESTION into input_b
> FROM
> SATURN.SPRIDEN,SATURN.SARQUAN
> WHERE spriden_id LIKE '%70%' AND spriden_pidm=sarquan_pidm;
> CTX_DOC.POLICY_FILTER('test_policy', input_b, output_c, TRUE);
> END;
> /
>
> I'm new to Oracle, so go easy on me please :)
>
> Thanks for your help
>
> Matt

POLICY_FILTER accepts VARCHAR2, CLOB, BLOB or BFILE as input. The output is always CLOB, but in 10g it's easily cast to VARCHAR2 with no additional coding. What you must ensure in your code is that the select returns single row (not sure if it does as you presented it.) The function might look like this:

CREATE OR REPLACE FUNCTION StripHTMLTags( p_doc IN VARCHAR2 ) RETURN VARCHAR2
AS

   res CLOB;
BEGIN
   CTX_DOC.Policy_Filter('test_policy', p_doc, res, TRUE);    RETURN substrb(res,1,4000);
END StripHTML;
/

Then you use it in a select statement from the table with HTML documents, like this:

SELECT StripHTMLTags(SARQUAN_QUESTION) sarquan_question   FROM SATURN.SPRIDEN, SATURN.SARQUAN
 WHERE spriden_id LIKE '%70%' AND spriden_pidm=sarquan_pidm

and you should receive plain text output as result. You can also return the resulting CLOB itself if your documents exceed 4000 bytes in length - Oracle will transparently cast a CLOB to a VARCHAR2 when necessary.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Aug 10 2007 - 11:24:31 CDT