What could be the problem or the solutions?1) Create an XMLType table (could be temporary) using binary XML storage :
2) In your procedure, load the XMLType containing the response (rsp_xml) into the table :
create table tmp_xml of xmltype xmltype store as securefile binary xml;
3) Then, execute the query directly from the table :
insert into tmp_xml values (rsp_xml);
4) At the end of the procedure, delete (or truncate) the table or simply let the table delete itself when the session ends (in case you created it TEMPORARY)
SELECT opciones_obj ( ... ) BULK COLLECT INTO t_opciones FROM tmp_xml t , XMLTABLE ( xmlnamespaces ( ... ), '/env:Envelope/env:Body/...' PASSING t.object_value COLUMNS ...