Oracle SQLPlus根据变量设置环境变量 [英] Oracle SQLPlus setting environment variable based on variable

问题描述

我想根据我要检索的XML数据的大小设置环境变量long.这个想法是这样的:

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

这将产生错误:SP2-0268: long option not a valid number，并且XML文件仅包含80个字符.

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

尝试

set long &XML_DATA_SIZE

:

抱歉，我摆弄了一些其他选项，弄乱了我的测试. 试试这个:

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

这篇关于Oracle SQLPlus根据变量设置环境变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！