Toad World® Forums

Improve handling of XMLTYPE arguments and return values


Please store CLOB representation of XMLTYPE instances instead of the message ‘<Sorry, values of type XMLTYPE cannot be displayed.>’. This will greatly enhance the handling of XMLTYPE, making it much easier to test programs using this datatype. As is, it’s next to impossible and one has to revet to using DBMS_OUPUT.

Currently, for XMLTYPE inputs the following is generated (example):

qu_result_xp.add_input (tc_result_guid_in, ‘{784B481D-1110-49F8-99D1-6DC238D167B8}’, ‘XML1’, ‘<Sorry, values of type XMLTYPE cannot be displayed.>’);

I suggest this is handled like CLOB inputs, eg:

qu_result_xp.add_input (tc_result_guid_in, ‘{E3AD3E9D-7912-45CB-8935-C29D49C54863}’, {784B481D-1110-49F8-99D1-6DC238D167B8}’, ‘XML1’, qu_xmlhelper.to_clob(i_XML1));
qu_result_xp.save_argval (‘XML1’, qu_xmlhelper.to_clob(i_XML1));

where QU_XMLHELPER.TO_CLOB is a new function that obtains the CLOB representation of a given XMLTYPE, with an implementation like (you probably don’t want to use Conditional Compilation because of your support for Oracle 9.2 but then use EXECUTE IMMEDIATE instead):

function to_clob(x in xmltype)
return clob as

if x is not null then
$if not dbms_db_version.ver_le_10 $then
– Pretty printed on Oracle 11.1 and newer.
return x.getclobval(0, 2);
– Non pretty printed on Oracle 10.2 and older.
return x.getclobval;
end if;

return null;
end to_clob;

Similar for return values and output arguments, currently this is generated:

/* In case there are no scalar OUT arguments. */ NULL;

and I suggest instead:

/* Return / qu_result_xp.save_argval (‘RETURN’, qu_xmlhelper.to_clob(Q##FUNCTION_RETURN_VALUE));
Return */ qu_result_xp.add_outarg (tc_result_guid_in, ‘{17E0D6A4-F4FF-452D-8458-C6FD1ABD6B5E}’, ‘RETURN’, qu_xmlhelper.to_clob(Q##FUNCTION_RETURN_VALUE), gen_type_in => ‘TEST CODE’);