CODEWITHSUNDEEP
xmloracle-databasestored-proceduresxmltype
Stephan B
Stephan B

Reputation: 837

Handling nulls when using Oracle XMLType

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;

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

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?

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.)

Upvotes: 2

Views: 8111

Answers (2)

DazzaL
DazzaL

Reputation: 21973

for an SQL solution: 

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

that should avoid the constructor bug.

eg

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>

Upvotes: 5

Roman Shevelev
Roman Shevelev

Reputation: 1

I have solved same problem handling exception

begin
  xml:=xmltype(cur);
exception when others then 
  dbms_output.put_line(sqlerrm);
  xml:=xmltype('<ROWS></ROWS>');
end;

Upvotes: 0

Related Questions