Oracle SQLPlus setting environment variable based on variable
I want to set the environment var开发者_运维技巧iable 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.
Instead of
set long XML_DATA_SIZE
try
set long &XML_DATA_SIZE
[EDIT]:
Apologies, I had fiddled around with some other options which messed up my test. Try this:
define xml_data_size=0
column xml_data_size new_value xml_data_size noprint
select trim(length(xmltype.getClobVal(xml_data))) xml_data_size from xml_tab where key = '1234';
set long &xml_data_size
sho long
set pagesize 0
...
...
精彩评论