Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Monday, November 13, 2006

ORA-30625 and XMLType

I will present some differences in handling XMLType in SQL and PL/SQL by Oracle. Test case was done on Oracle 10.2.0.2. Lets define variable and fill it with sample data. It is important to have empty value in tag <empty>.
SQL> var xm varchar2(100);
SQL> exec :xm := '<root><empty></empty></root>';
PL/SQL procedure successfully completed.
Lets try to extract value of empty tag:
SQL> var xmv varchar2(100);
SQL> exec :xmv := XMLType(:xm).extract('//empty/text()').getstringval();
BEGIN :xmv := XMLType(:xm).extract('//empty/text()').getstringval(); END;
*
ERROR at line 1:
ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at line 1
Lets do the same with SQL:
SQL> select XMLType(:xm).extract('//empty/text()').getstringval() from dual;
XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL()
--------------------------------------------------------------------------------
Now we get NULL. You can confirm this with that query:
SQL> select nvl(XMLType(:xm).extract('//empty/text()').getstringval(),
'(NULL)') from dual;
NVL(XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL(),'(NULL)')
--------------------------------------------------------------------------------
(NULL)
So, be careful when you extract data from XMLType in PL/SQL. If you want NULL to be returned for empty tags then you should use function like this:
CREATE OR REPLACE
FUNCTION XML_EXTRACT_NO_EXCEPTION
( p_xml IN XMLTYPE
, p_xpath IN VARCHAR2
, p_namespace IN VARCHAR2 default NULL
) RETURN VARCHAR2 AS
BEGIN
return case when p_xml.extract(p_xpath, p_namespace) is not null
then p_xml.extract(p_xpath, p_namespace).getstringval()
else NULL
end;
END XML_EXTRACT_NO_EXCEPTION;
And then you can use it safe in PL/SQL:
SQL> exec :xmv := XML_EXTRACT_NO_EXCEPTION(XMLType(:xm), '//empt/text()');
PL/SQL procedure successfully completed.
SQL> print :xmv
XMV
--------------------------------------------------------------------------------
Another reason for ORA-30625 can be that you did not provide namespace in execution of extract. More on this in my post
XMLType and Namespace problem.

Paweł

10 comments:

Ronald Weetabix said...

Thanks Paweł. I was getting Oracle error ORA-30625: method dispatch on NULL SELF disallowed. and you gave me the solution. It isn't possible to call the method and see if it returns null, the correct way is to test first, then call the method if it isn't null.

Unknown said...

Hi Pawel,
You can also just test if the node is null, before applying getStringVal.
IF xmlobject.existsNode('/croinfo') = 1 THEN
IF xmlobject.extract('/croinfo/text()') IS NULL THEN
address1 := NULL;
ELSE
address1 := xmlobject.extract('/croinfo/text()').getstringval();
END IF;
END IF;

SS said...

Hi,

I m trying to use XPath expression to check for a node exists or not and the response is a soap response.

If RESP.EXISTSNODE('//ProcessOperationsResult/Response/OperationResponse/NewEnquiryResponse/Enquiry/EnquiryNumber') = 1 then

IF RESP.extract('//ProcessOperationsResult/Response/OperationResponse/NewEnquiryResponse/Enquiry/ExternalSystemReference/text()','xmlns=""') IS NULL THEN

the above 2 commands are not working for me at all and goes into exception raising ora-30625.

Can you let me know what i m doing wrong here.

btw, i have a valid soap response coming back and i can post the soap response here.

Thanks

S

Paweł Barut said...

Hi,

Might by you need to provide proper namespace.

/Paweł

SS said...

hi,

i have tried all the combinations with namespace and without namespaces and i cant get the existsnode function working. i m getting a valid soap response. but its the RESP.EXTRACT bit with the xpath xpression which is not working at all, and i have exhausted my resources...

unable to post my soap response here as it says html tag cannot be accepted.

any help appreciated...
Sri

Anonymous said...

Thank you Paweł for your kind and clear notes on Oracle. I find great help in them.

Dzięki Paweł!

Anonymous said...

thanks for this! - it saved me a lot of time

Anonymous said...

Thanks for your helpful note and clear explanations.

Anonymous said...

Thank you Pawel. This was really help me to iorn out the NULL error and saved me ton of time

Wilson Blanco said...

Thank you very much for posting parts of your experience. It is very helpful to us the begginers.

 

Copyright © Paweł Barut
Printing from DOS to USB Printer