"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message
news:cf15dee4.0404071910.5c65987c_at_posting.google.com...
> "Bruno D." <bdurren_avirer__at_hotmail.com> wrote in message
news:<40743139$0$19647$4d4eb98e_at_read.news.fr.uu.net>...
> > Hi everyone,
> >
> > Under oracle 9.2i if you execute this:
> >
> > select xmltype('<tag>It''s
> > cool</tag>').extract('//tag/text()').GetStringVal() from dual;
> >
> > you get: It's cool
> >
> > The question is simple: How to get --> It's cool ????
> >
> > Thanks in advance for any response.
>
> Bruno,
>
> You are going to have create a local function to do the decoding of the
XML
> text. This is true for 9.2.0.1 and 10.1.0.2.
>
> According to p.85-7 of the "Oracle9 iSupplied PL/SQL Packages and Types
> Reference" manual, there is a second parameter (FLAG) to the
> DBMS_XMLGEN.CONVERT function which determines whether encoding or decoding
is
> done. The default is to encode.
>
> In theory, one should be able to do the following:
> SELECT
> DBMS_XMLGEN.convert(
> XMLTYPE(
> '<tag>It''s cool</tag>'
> ).extract(
> '//tag/text()'
> ).GetStringVal(),
> DBMS_XMLGEN.ENTITY_DECODE
> )
> FROM
> dual
>
> However in 9.2.0.1 and 10.1.0.2, the following error message appears:
>
> DBMS_XMLGEN.ENTITY_DECODE
> *
> ERROR at line 8:
> ORA-06553: PLS-221: 'ENTITY_DECODE' is not a procedure or is undefined
>
>
> To get around this, I created a function to wrap the CONVERT function:
> CREATE OR REPLACE FUNCTION
> xml_decode(
> i_xml_string IN VARCHAR2
> )
> RETURN VARCHAR2
> IS
> BEGIN
> RETURN
> DBMS_XMLGEN.convert(
> i_xml_string,
> DBMS_XMLGEN.ENTITY_DECODE
> );
> END;
> /
>
> And I modify the query as follows to call my new function:
>
> SELECT
> xml_decode(
> XMLTYPE(
> '<tag>It''s cool</tag>'
> ).extract(
> '//tag/text()'
> ).GetStringVal()
> )
> FROM
> dual
> ;
>
> I get the following result:
>
XML_DECODE(XMLTYPE('<TAG>IT''SCOOL</TAG>').EXTRACT('//TAG/TEXT()').GETSTRING
> VAL(
> --------------------------------------------------------------------------
--
> ----
> It's cool
>
> Which is what you want.
>
> Douglas Hawthorne
very nice - just what we need.
I substituted clob for varchar2 and it worked -- for about 10 minutes --
then got ora-600[17122]
Someone else reported a similar problem on MetaLink on 3/23/04. The analyst
said it would be fixed in oracle 10.2
Received on Thu Apr 08 2004 - 23:08:52 CDT
