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

From: Gertjan <gvheijst_at_xs4all.nl>
Date: 28 Jan 2007 02:16:49 -0800
Message-ID: <1169979409.902215.5790@q2g2000cwa.googlegroups.com>

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

On 26 jan, 16:06, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> Gertjan schrieb:
>
>
>
>
>
> > Hi Maxim,
>
> > My version is 10g, but the script i write should work on 9i as well, so
> > i want to restrict myself to solutions that are documented for 9i (R2).
> >>From what i read in the newsgroups I presume the problem is somehow
> > related to priviliges, but i can't reallt figure out how...
>
> > Gertjan
>
> > On 26 jan, 14:09, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> >> Gertjan schrieb:
>
> >>> Hi,
> >>> Maybe i should formulate more carefully. I DID find articles on
> >>> PLS-00201. What i did NOT find was a solution to my problem. What I'm
> >>> trying to do is to develop a function that can be used to update some
> >>> values in a XMLdocument that is stored in a clob field. I have no
> >>> further exprience with PL/SQL but after some browser in the manuals i
> >>> thought updateXML should do the job. The function i included was an
> >>> experiment to see how this would work but the i ran into the PLS-00201
> >>> problem... Is it caused by missing priviliges and if so, what do i need
> >>> to change or is there another way to update the XMLdocument for which i
> >>> would not need to modify priviliges.
> >>> Thanks for helpingThis function was new introduced in Oracle 9i R2. And your Oracle
> >> version is?
>
> >> Best regards
>
> >> MaximI don't think, it depends on privileges. Here a testcase with minimum
> privileges where i can't reproduce your issue. You should better check
> (or consult your dba) , whether xml support is installed properly and
> whether all objects on your database are in valid state.
>
> SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
> PL/SQL Release 9.2.0.6.0 - Production
> CORE 9.2.0.6.0 Production
> TNS for Linux: Version 9.2.0.6.0 - Production
> NLSRTL Version 9.2.0.6.0 - Production
>
> SQL> create user test_xml identified by test_xml default tablespace users;
>
> User created.
>
> SQL> alter user test_xml quota 1m on users;
>
> User altered.
>
> SQL> grant create session,create table to test_xml;
>
> Grant succeeded.
>
> SQL> connect test_xml/test_xml
> Connected.
> SQL> create table txml(id number,xml_col xmltype);
>
> Table created.
>
> SQL> insert into txml values(1,xmltype('<row>Scott</row>'));
>
> 1 row created.
>
> SQL> select * from txml;
>
> ID XML_COL
> ---------- --------------------------------------------------
> 1 <row>Scott</row>
>
> SQL> update txml set xml_col = updatexml(xml_col,'/row/text()','King')
> where id=1;
>
> 1 row updated.
>
> SQL> select * from txml;
>
> ID XML_COL
> ---------- --------------------------------------------------
> 1 <row>King</row>
>
> Best regards
>
> Maxim- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk bericht weergeven -
Received on Sun Jan 28 2007 - 04:16:49 CST