I want to set the environment variable long
based on the size of the XML data I'm trying to retrieve. The idea is something like this:
var XML_DATA_SIZE number;
SELECT TRIM(LENGTH(xmltype.getClobVal(xml_data)))
INTO :XML_data_size
FROM xml_tab
WHERE key = '1234';
print XML_DATA_SIZE
set long XML_DATA_SIZE
set pagesize 0
set line 2000
set termout off
spool XMLDATA.xml
select xml_data from xml_tab where key = '1234';
spool off
This yields an error: SP2-0268: long option not a valid number
, and the XML file only contains 80 characters.