tags:

views:

37

answers:

1

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.

+2  A: 
bhangm
In SQLPlus, that'll result in a prompt...
OMG Ponies
not if you already have it set to a value. It works for me in SQL*Plus
bhangm
I tried that, it prompts me. I can enter the number that the `print` statement gives me and everything is OK, but I eventually want to give this to the analysts who will just run this in batch mode and use the resulting files.
FrustratedWithFormsDesigner
@OMG Ponies - you were right, my bad! I had inadvertently tried something else which actually ended up working but messed up my test case for my original answer
bhangm
No, for me I need the `:` prefix on the bind variable (else I get a `missing keyword` error). Also, I get an error `saying SP2-0267: long option 0 out of range (1 through...`.
FrustratedWithFormsDesigner
Oh, wait, if it's a column I don't need `into`. Never mind, this *does* work! :) :)
FrustratedWithFormsDesigner