What is "tosend"? Any user-defined function?
I'm not sure but in both the error-ed lines this seems to be the cause. Does "tosend" accept any parameters?
tosend is the alias for the query.
XMLElement returns an XMLType data type. DBMS_OUTPUT has no clue how to handle that data type. In order for DBMS_OUTPUT to be able to do what you want, you would need something like
DECLARE CURSOR c_xml IS SELECT XMLELEMENT ("CHECKINOUT" ,xmlattributes ('1' AS "PROPERTY" ,'2' AS "ID" ,'3' AS "STATUS" ,'4' AS "OCCUPANT" ,'5' AS "email" ,'6' AS "EXTENSION" ,'7' AS "FOLIO")) AS tosend FROM dual; TYPE records IS TABLE OF c_xml%rowtype; charges records; v_content varchar2(32000); BEGIN open c_xml; fetch c_xml bulk collect into charges; close c_xml; dbms_output.put_line(charges(1).tosend.getStringVal()); for i in charges.first .. charges.last loop v_content := v_content||charges(i).tosend.getStringVal(); end loop; end;
I used .getStringVal() to convert the XMLType into a VARCHAR2 value. That works for up to 4k worth of data. There is also a .getClobVal(). Depending upon your version, you could wrap an XMLSerialize around the XMLElement and use XMLSerialize to convert the XMLType into VARCHAR2 instead of using .getStringVal(). That would be the preferred method.
Can you use XMLAgg in your query against the DB? Not sure what your final v_content needs to look like so just throwing out options.
Thanks. What I want to do is to send the v_content as part of the utl_http.write.