Oracleでの文字列連結サイズの上限

質問

Oracle9iでテーブル内容をスプール出力していますが、カラム単位で余白を削除したい
（トリムをかけたい）ので以下のようにカラムを連結させてスプールしています。
set colsep ','
spool test.csv
select
a ||','||
b ||','||
c
from test_table
spool off

実際の項目数は100以上あり、レコードの最大長も数千バイトになりますが、
各項目にMAXの値を入力して上記スプールを実行したところ、以下のエラーメッセージが
出力されました。
「ORA-01489: 文字列を連結した結果、長さが最大長を超えました」

データを連結した結果の長さが上限を超えたということなのでしょうが、
この最大長はどこで設定されているものなのでしょうか？
また、そもそもスプール出力でトリムが効かないためにこのようなやり方で
出力していますが、他によい方法をご存知の方いましたら教えて下さい。
よろしくお願いします。

回答No.4

GoF · ベストアンサー

>>Code Tipsの内容も実際に試してみたのですが、結局のところ項目を連結させる
>>やり方では4000バイトまでが限度ということだと判断したのですが

4000バイトまでが限度 とは、どのような理由からでしょうか?
以下のSQLで4000バイトオーバーは可能です。(32KBの壁はあります)

既に完成度の高いスクリプトがデバッグ済みで存在するのに
同じものを 1からコーディングするのは、(自分の勉強にはなるとは思いますが) 意味のない行為だと思いますので、
Code Tipsをお勧めしました。

SPOOL HOGE.TXT
SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 40000
SET LONGC 40000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET VERIFY OFF

SELECT
TO_CLOB('')
|| LPAD('1', 4000,'1')
|| LPAD('2', 4000,'2')
|| LPAD('3', 4000,'3')
|| LPAD('4', 4000,'4')
|| LPAD('5', 4000,'5')
|| LPAD('6', 4000,'6')
|| LPAD('7', 4000,'7')
|| LPAD('8', 4000,'8')
|| LPAD('9', 4000,'9')
|| LPAD('0', 4000,'0')
FROM DUAL
/
SPOOL OFF

お礼

回答ありがとうございます。やはり認識間違っていたようですね。
OcacleのSQLリファレンスで連結演算子の説明に「どちらかの文字列がVARCHAR2型の場合、
連結結果はVARCHAR2型となり、最大文字数は4000」とあります。
また、「どちらかがCLOB型の場合、結果は一時CLOB型になります」とも書いています。
GoFさんの例示されているSQLを見ると、CLOB型のデータと連結してやれば、4000バイト以上の
連結も可能ということなのでしょうか。
（最大長はCLOB型の4GBなのかと思いましたが32KBなのですね）
確かに文字列連結は4000バイト以上可能なのですが、実際にエラーの出ていたスプール処理を
以下のように変更して試してみたのですが、実行結果が何分待っても帰ってこない状態になって
しまいました。（4GBを項目数分連結するので処理に時間がかるのでしょうか？）
spool test.csv
select
to_clob('') ||
a ||','||
b ||','||
c ||','||
//以下100項目以上連結
from test_table

Code Tipsの#764は実際に試したのですが、単純に文字列連結を行っているようで、
質問と同様のエラーが出てしまいます。
#675は内容がやや複雑で実行内容がよく理解できなかったのと、TO_CLOB等も使用していないよう
なので#764と変わらないのかと思い実行は控えていました。

暗黙的な変換等、基本的な部分を理解できていないこともあり、せっかくの回答も生かせていないようです。
もう少し勉強して試してみたいと思います。いろいろとありがとうございました。

回答No.3

GoF

SELECT TO_CLOB('') || LPAD('A', 4001, 'C') FROM DUAL ;

通常 暗黙変換 により VARCHAR2に変換されます。
VARCHAR2の最大長は 4000バイト

すぐ下に同じような質問内容があるので、そちらのスクリプトを使用した方がよいと思います。

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1460873

お礼

回答ありがとうございます。返事が遅れてしまいもうし訳ありません。

連結する文字列のどちらかがVARCHAR2の場合、連結した結果はVARCHAR2の文字列となり、
最大4000バイトとなるということですね。
SQL文の内容はCLOB型のデータと連結すれば連結結果はCLOB型となり、
最大長は4GBとなるのでエラーとはならないことを言われているのでしょうか。

Code Tipsの内容も実際に試してみたのですが、結局のところ項目を連結させる
やり方では4000バイトまでが限度ということだと判断したのですが、上のCLOB型の
組み合わせ次第では4000バイト超のデータも出力可能なのでしょうか？
質問ばかりで申し訳ないのですが、認識間違ってましたら、
反応して頂けないでしょうか。
よろしくお願いします。

回答No.2

entree

簡単なテストケースを使って自力で確認できませんか？

質問を見ていてもいかなる努力をしても解決できない様子が全く見て取れません。

補足

単にスプール出力の方法を質問している訳ではありません。レコード長が短いデータであれば問題はなかったのですが、今回文字列連結云々のエラーが出てしまい、マニュアルを調べても「最大長」が何を意味しているかが分からず質問しています。

回答No.1

ppg-2

以下の環境変数で、直接csvに出力できます。

set heading off
set feedback off
set trimspool on
set termout off
set pages 0
set line 9999

補足

回答ありがとうございます。
すみません、ご指摘のシステム変数は全て設定しており、その上で文字列連結のエラーが出るということなのですが。
情報開示が不十分であったことをお詫びします。