Oracle SQLPlus setting environment variable based on variable

Question

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.

@Adam Hawkes: From what I've read, if I set long to the maximum possible, the client will need that much free memory, even if the actual data that I return is much smaller. I don't anticipate need the maximum possible, but I'm very reluctant to guess the actual max. In the test data, the sample XML docs range from 20 KB to 3000 KB, and that's only very simple test data. — FrustratedWithFormsDesigner
– FrustratedWithFormsDesigner, Commented Oct 26, 2010 at 21:14
Well, that's going to be a problem. I'm pretty sure the limit is 2 million bytes. So that 3000K message is going to be cut off. And allocating 2MB of RAM on a client doesn't seem like a big deal to me. — anon
– anon, Commented Oct 27, 2010 at 1:54
@Adam Hawkes: Well, if the data they want is too big, hmm... I guess they'll have to go back to doing whatever they want to do with it by hand. ;) From what I've seen that's not going to be the majority of data they want to look at. From what I read here download.oracle.com/docs/cd/B19306_01/server.102/b14357/… , The maximum value of *n* is 2,000,000,000 bytes. — FrustratedWithFormsDesigner
– FrustratedWithFormsDesigner, Commented Oct 27, 2010 at 4:26
Wow, yeah, I was wrong by a factor of 1000. 2 billion, not 2 million. — anon
– anon, Commented Oct 27, 2010 at 12:07

bhangm · Accepted Answer · 2010-10-26 20:27:22Z

4

~~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
...
...

edited Oct 26, 2010 at 20:27

answered Oct 26, 2010 at 20:07

bhangm

6074 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bhangm Over a year ago

not if you already have it set to a value. It works for me in SQL*Plus

FrustratedWithFormsDesigner Over a year ago

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.

bhangm Over a year ago

@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

FrustratedWithFormsDesigner Over a year ago

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 Over a year ago

Oh, wait, if it's a column I don't need into. Never mind, this does work! :) :)

Collectives™ on Stack Overflow

Oracle SQLPlus setting environment variable based on variable

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related