Re: error: PLS-00201: identifier 'UPDATEXML' must be declared

Gertjan schrieb:
> Hi Maxim - and others -
>
> Things are getting stranger... This test succeeds on my system as
> well. It seems that UPDATEXML is recognised when called directly from
> the SQL*PLUS prompt, but not if it is called in a function that is
> called from SQL*PLUS prompt ...
>
> for example: the query:
>
> SELECT UPDATEXML(XMLType.CreateXML(UDFValues), '/lokaleMetaData/
> metadataWaarde/text()', '45') FROM tProcessen WHERE procesnummer =
> 'B0009';
>
> works ok, but the function
>
> CREATE OR REPLACE FUNCTION getXMLValue
> return varchar2
> IS
> clobval Clob;
> xmlval XMLType;
> BEGIN
> SELECT UDFValues INTO clobval FROM tProcessen WHERE procesnummer =
> 'B0009';
> xmlval := XMLType.CreateXML(clobval);
> xmlval := UPDATEXML(xmlval, '/lokaleMetaData/metadataWaarde/text()',
> '45');
> RETURN xmlval.getStringVal;
> END;
>
> gives the compilation error ....
>
> I get this error both when the function is loaded from a script and
> when i paste the function directly into SQL*PLUS
>
> Thanks for any suggestion...
>
> Gertjan
>

The problem is - updatexml can be used only within sql context (that means, pl sql assignment like variable := value can't be recognized). To get it working, you can rewrite your function as

SELECT UPDATEXML(xmlval, '/lokaleMetaData/metadataWaarde/text()','45') INTO xmlval FROM DUAL;

You can also look on the Note 480368.996 on Metalink, there is mentioned, enhancement request 2295055 which deals with this problem, but i assume, it has very low priority, since the Note is dated as of November 2003, and the same behaviour is still on 10.2.0.3

Best regards

Maxim Received on Sun Jan 28 2007 - 05:08:39 CST