はじめての親子ハイキングに挑戦!! >>

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

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

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

このQ&Aに関連する最新のQ&A

A 回答 (4件)

>>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
    • good
    • 1
この回答へのお礼

回答ありがとうございます。やはり認識間違っていたようですね。
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と変わらないのかと思い実行は控えていました。

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

お礼日時:2005/06/28 07:30

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



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

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

参考URL:http://oshiete1.goo.ne.jp/kotaeru.php3?q=1460873
    • good
    • 0
この回答へのお礼

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

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

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

お礼日時:2005/06/27 14:25

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



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

この回答への補足

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

補足日時:2005/06/24 09:37
    • good
    • 1

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



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

この回答への補足

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

補足日時:2005/06/24 09:09
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QORACLEから各テーブルをCSV形式で出力するコマンド

上記のとおりですが、そういったコマンドはないのでしょうか?
調べてもラインサイズを1000にしてページサイズを0にしてトリムして…できたけど無駄な文字列が入っていたり。。
どなたかアドバイスお願いします。

Aベストアンサー

残念ですがそういうコマンドはありません。
他の回答者さんが言っているようにPL/SQLやツールですね。

私はObjectBrowserってツールを使ってます。
これあると非常に便利ですよ。
有料ですが一ヶ月の試用もあるので検討してみては?

参考URL:http://www.sint.co.jp/siob/default.asp

Qsqlplusで表示が変なので、出力を整形したい。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------

上記のように意味不明な形式で出てきます。

例えばこんな風に

select カラム1,カラム2,カラム3 from hoge;

カラム1 1の値
---------------------------
カラム2 2の値
---------------------------
カラム3 3の値

等のように分かりやすく表示できないでしょうか?

ちなみにOracle9iR2を使用しています。
sqlに関するツールは使用できないルールでして、あくまでsqlplusのコマンド上でみやすくしなければなりません。

分かりづらくですいませんが、皆さま、ご教授お願いします。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
-----------------------...続きを読む

Aベストアンサー

SQLPLUSを起動して、

SQL>set linesize 列数

でどうだ。

SQL>show linesize

で確認ができる。

QCLOB型へのINSERT

いつも参考にさせて頂いています。

まず、問題はCLOB型に4000Byte以上のデータを
格納出来なくて困っております。

環境は下記のとおりです。
OS:Windows2003
Oracleバージョン:9.0.1.4.0

テストした手順は
(1)テーブル作成
create table TEST_TBL (
id number(9),
data clob
);

(2)INSERT
insert into TEST_TBL values(1,'4000Byte以上のデータ');

ORA-01704: 文字列リテラルが長すぎます


CLOBは4Gまで入るはずなのになぜなんでしょうか?
ご回答よろしくお願いいたします。

Aベストアンサー

リテラルを分割する方法や PL/SQL を使用する方法もあります。

参考 URL にサンプルがあります。

参考URL:http://biz.rivus.jp/oerr_ora_01704.html

Qupdate文で改行を入れる

こんにちは。
いつもお世話になっています。

update文を使用して、データに改行を入れたいのですが、どうすればよいでしょうか。

対象列のデータ型はVARCHAR2です。

例えばSQLPLUSから、
SPL>update 「テーブル名」set 「対象列」='あ改行い改行う改行'
を投入し、そのあと
select 「対象列」 from 「テーブル名」
とすると
SQL>あ
SQL>い
SQL>う
と出てくればよいんですが、
SQL>update 「テーブル名」set 「対象列」='あ\nい\nう\n'
としても
SQL>あ\nい\nう\n
と、「\n」が文字列としてでてきてしまいます。

SPL>update 「テーブル名」set 「対象列」='あ
2い
3う'
と投入すると、期待通りになるのですが、
そうではなく一行にまとめたいのですがどうすれば良いでしょうか。

Oracle9iを使用しています。
宜しくお願いいたします。

Aベストアンサー

こんにちわyukio200263さん
以下のSQL文でどうでしょうか?

UPDATE 「テーブル名」
SET 「対象列」= 'あ' || CHR(13) || CHR(10) ||'い'

ちょっと長くなってしまいますが、一行で可能です。

QSELECTで1件のみ取得するには?

こんにちわ。
いまORACLE9iを使用している者です。

ACCESSでは
SELECT TOP 1 項目名 FROM テーブル名
ORDER BY 項目名;
で並べ替えたデータ群のうち,先頭の1件だけを
取ることができますが,
ORACLEでそのような機能(SQL)はあるでしょうか?
教えてください。
よろしくお願いします。

Aベストアンサー

order by と rownum を併用する場合は注意が必要です。

[tbl01]
cola | colb
------------
1000 | aaaa
1001 | bbbb

というデータがある場合、
select cola from tbl01 where rownum < 1 order by cola desc;
とすると、「1001」ではなく、「1000」が返されます。
これは、order by の前に rownum < 1 が適用されてしまうからです。

解決するには、
select aaa from (select cola aaa from tbl01 order by cola desc) where rownum = 1;
とすれば良いです。

QOracle(オラクル)で、日付時刻型の検索方法について

質問させていただきます。
データベースはオラクルを使っていて、
SQL文で、抽出するときにエラーが出て困っています。

日付時刻型が「2005/05/26 19:13:00」という感じで入ってます。
2005/05/26 を抽出したいのですが、
BETWEEN '2005/05/26 00:00:00' AND '2005/05/26 23:59:59'

だと、エラーでできません。
どなた様か、ご教授よろしくお願いしますm(_ _)m

Aベストアンサー

日付検索を行う場合は、以下のように書式を含める必要があります。

col BETWEEN TO_DATE('2005/05/26 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2005/05/26 23:59:59','YYYY/MM/DD HH24:MI:SS')

ただ、厳密には

col >= TO_DATE('2005/05/26', 'YYYY/MM/DD')
AND
col < TO_DATE('2005/05/27', 'YYYY/MM/DD')

と書くべきでしょうね。

Qbatファイルからsql文実行

クライアントOS:WIN2000
Oracle:9i(サーバ(UNIX)上にあります)

現在、クライアントからbatを起動し、SQL文を投げ、結果を取得したいと思っております。
(SQL文は単純にTBLをカウントしているだけです)

・batの中身
sqlplus %UID%/%PASS%@%SID% @test.sql > output

結果は取得出来るのですが、余分な情報も結果に出力されてしまいます。結果のみを出力させるにはどうすればよろしいでしょうか?

・余分な情報
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
に接続されました。

Aベストアンサー

sqlplus に -S オプションを追加してみればどうでしょうか。

参考URL:http://biz.rivus.jp/sqlplus_overview.html

Q3つの表の外部結合

表A、B、Cの3つがあり、Aのすべての行を出力したいと考えています。
外部結合を用いるのだとは思うのですが、3つの表に対して行う場合の
書き方がわからず困っています。
ご教授いただけないでしょうか?
select * from a,b,c
where a.商品ID =b.商品ID (+) and b.商品ID (+) =c.商品ID (+)
としてみましたが、うまくいきませんでした。

Aベストアンサー

ansi構文の趣旨からいえば、結合条件と絞り込み条件は分けて書くので・・

select *
from a
left join b on (a.商品ID =b.商品ID)
left join c on (b.商品ID =c.商品ID)
where a.年月 = 任意の値

と書くのが一般的でしょうね。

QBLOBやCLOBのパフォーマンスを改善したい

Oracle 10g R2 を使用し、開発を行っていますが、
パフォーマンス改善という壁にぶつかっており、
お助けいただきたいと思いました。

要求定義の段階で、可変長のバイナリデータを扱う必要があり、
テーブル内には BLOB フィールドを用意しております。
レコード件数にすると、全体でおおよそ 500,000 ~ 1,000,000 件に
なります。(導入先によって上記範囲内で変動する)
データはさらに区分によって判別されるようになっております。

プログラムから投入する SQL 文については、
最適化を行っておりまして、最高のパフォーマンスが得られる
であろう構文になっておりますし、テーブルの設計についても、
検索条件となる項目へのインデックス設定を行っています。

現状としてプログラムの中でボトルネックとなっているのは、
BLOB データを Fetch するタイミングなんです。
開発環境は、C#.NET+ODP.NET なんですけど、ミドルウェアは、
oo4o を使った場合でも、Microsoft の Oracle のドライバを
使用したときでも同じ部分でボトルネックになっていました。
ちなみに、BLOB をやめて、CLOB にした場合も、
CLOB データを Fetch する部分がボトルネックになっていました。

テスト段階なのでバイナリデータが 4 KB 未満ですが、
これを VARCHAR2(4000) のフィールドに Base64文字列などで
格納した場合は、LOB データの Fetch はありませんので、
かなり高速な処理が実現できるのですが、実際の運用では、
4 KB を超えるようなバイナリデータが普通に現れてきます。
なので、VARCHAR2 でのデータベース設計ができない状態です。

そもそも、この BLOB フィールドや CLOB フィールドのデータを
Fetch する速度を向上させること、というのは可能なのでしょうか?
ちなみに、Oracle サーバ側の設定は一切行っておりません。

Oracle 10g R2 を使用し、開発を行っていますが、
パフォーマンス改善という壁にぶつかっており、
お助けいただきたいと思いました。

要求定義の段階で、可変長のバイナリデータを扱う必要があり、
テーブル内には BLOB フィールドを用意しております。
レコード件数にすると、全体でおおよそ 500,000 ~ 1,000,000 件に
なります。(導入先によって上記範囲内で変動する)
データはさらに区分によって判別されるようになっております。

プログラムから投入する SQL 文については、
最適化を行ってお...続きを読む

Aベストアンサー

1冊のマニュアルになるボリュームがあって色々書かれているので
開発者ガイドを見てください。
「アプリケーション開発者ガイド- ラージ・オブジェクト」です
個人的には設計段階でLOBは使用しないように最大限粘っていると思います。

参考URL:http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19247-01/toc.htm

Qデータを削除しても表領域の使用率が減りません

いつもお世話になっております。

今使用している表領域の使用率が高くなってきたため、
不要なテーブルやデータを削除しました。

ですが、少ししか使用率が減らず困っています。

テーブルを削除した際にはPURGE RECYCLEBINで、BIN~のテーブルも
消しています。

何か他に原因があるのでしょうか?

Aベストアンサー

前の方のおっしゃるとおり、DELETEしただけでは領域は開放されません(ハイウォーターマークが下がらない)ので、以下を試してみてください。

1.該当テーブルの全件削除で良い場合
truncate テーブル名 drop storage;
を実行する。
これで領域も開放されます。(最後のところをreuse storageとすると領域保持する意味となる)

2.部分的にdeleteして、領域を開放したい場合
alter table テーブル名 enable row movement;
alter table テーブル名 shrink space cascade;
alter table テーブル名 disable row movement;
を実行する。
1行目は領域開放の前準備、3行目は1行目の変更を元に戻す意味。
2行目でcascadeしておくと、関連インデックスの領域も一緒に縮小してくれます。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング