使用Oracle XMLType时处理null [英] Handling nulls when using Oracle XMLType

问题描述

我正在使用Oracle的XMLType函数从返回游标的存储过程中获取XML数据.我的SQL看起来像这样:

I am using Oracle's XMLType function to get XML data from a stored procedure that returns a cursor. My SQL looks something like this:

select 
XMLType(
    package_name.storedProcName('PARAM1', 'PARAM2', 'PARAM3')
) as sresult 
from dual;

在存储过程返回记录的情况下，这可以正常工作，并且我得到了期望的XML结果.但是，当该过程未返回任何记录时，会出现以下错误:

In cases where the stored procedure returns records, this works fine and I get the XML result that I expect. However, when the procedure returns no records, I get the following error(s):

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 334
ORA-06512: at line 1

在这种情况下，最好返回null，空字符串或其他值.当然，我想避免每次发生此异常时都引发Oracle异常.我该怎么做?

Preferably, I'd like to return null, a blank string or some other value in this case. Certainly I want to avoid raising an Oracle exception every time this happens. What can I do to achieve this?

澄清:我的问题是，当存储过程返回空游标时，XMLType构造函数将引发异常.如何在SQL查询中检测到空游标? (不幸的是，我在Oracle方面没有足够的编程能力，我正在为Java客户端编程.)

Clarification: My problem is that when the stored procedure returns an empty cursor, the XMLType constructor raises an exception. How can I detect an empty cursor inside my SQL query? (Unfortunately, I don't have the luxury of programming on the Oracle side - I am programming a Java client.)

推荐答案

用于SQL解决方案:

select 
XMLType.createxml(
    package_name.storedProcName('PARAM1', 'PARAM2', 'PARAM3')
) as sresult 
from dual;

应该避免构造器错误.

例如

SQL> select xmltype.createxml(mycur(0)) from dual;

XMLTYPE.CREATEXML(MYCUR(0))
-------------------------------------------------------------------------------


SQL> select xmltype(mycur(0)) from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 334
ORA-06512: at line 1



no rows selected

x
SQL>

这篇关于使用Oracle XMLType时处理null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！