SQL*Loaderの制御ファイル内で、
以下のような日付データを変換するにはどうしたらよいでしょうか?

・2010-12-06 00:00:00.000

以前は'2010-12-06 00:00:00'の形式だったので
"to_char(to_date(:DENPYO_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYYMMDD')"
のようにして変換が可能だったのですが、仕様変更がありましてこれに対応しなければいけなくなりました。

ちなみに安易な考えで以下のようにしたのですがさすがにだめでした...
"to_char(to_date(:DENPYO_DATE,'YYYY-MM-DD HH24:MI:SS.000'),'YYYYMMDD')"

方法をご存知の方がいれば是非、ご教示いただきたく存じます。
よろしくお願いいたします。

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

A 回答 (1件)

DATE型ではミリ秒を保持しないので、TO_DATE関数では変換できません。



ですので、TO_TIMESTAMP関数を使えばいいです。
>to_char(to_date(:DENPYO_DATE,'YYYY-MM-DD HH24:MI:SS.000'),'YYYYMMDD')
to_char(to_timestamp(:DENPYO_DATE,'YYYY-MM-DD HH24:MI:SS.FF3'),'YYYYMMDD')

もしくはSUBSTR関数を使って".000"を除外してもいいと思います。
to_char(to_date(substr(:DENPYO_DATE,1,19),'YYYY-MM-DD HH24:MI:SS'),'YYYYMMDD')
    • good
    • 0
この回答へのお礼

いつもありがとうございます。
おかげで無事要件を満たすことが出来ました。
ありがとうございました。

お礼日時:2011/04/22 17:18

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

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

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

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

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

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

QSQL*Loaderでのsysdate使用

SQL*Loaderを使用してデータロードする際に、「更新日時」項目にsysdateでシステム日付を返すようにしているのですが、更新日時がTIMSTAMP型の場合に時間まで設定されません。
時間まで設定するにはどのように指定したらよいでしょうか?よろしくお願いします。

Aベストアンサー

なるほどDATE型だと時分秒は入りますがTIMSTAMP型の場合は自動で入らないのですね。
下記のようにすれば大丈夫なようです。

HOGE_TEST1.sql
--
CREATE TABLE HOGE_TEST1
( KEY1 CHAR(10) NOT NULL,
DATE1 TIMESTAMP(6) NOT NULL
)
--

HOGE_TEST1.ctl
--
LOAD DATA
INFILE 'HOGE_TEST1.dat'
APPEND
INTO TABLE HOGE_TEST1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
"KEY1",
"DATE1" DATE 'yyyy/MM/dd hh24:mi:ss' "to_char(SYSDATE,'yyyy/MM/dd hh24:mi:ss')"
)
--

HOGE_TEST1.dat
--
"AAAAA"
"BBBBB"
"CCCCC"
--

あとはsqlldrコマンドを発行
sqlldr USER/PASS control=HOGE_TEST1.ctl
試してみて下さい

なるほどDATE型だと時分秒は入りますがTIMSTAMP型の場合は自動で入らないのですね。
下記のようにすれば大丈夫なようです。

HOGE_TEST1.sql
--
CREATE TABLE HOGE_TEST1
( KEY1 CHAR(10) NOT NULL,
DATE1 TIMESTAMP(6) NOT NULL
)
--

HOGE_TEST1.ctl
--
LOAD DATA
INFILE 'HOGE_TEST1.dat'
APPEND
INTO TABLE HOGE_TEST1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
"KEY1",
"DATE1" DATE 'yyyy/MM/dd hh24:mi:ss' "to_char(SYSDATE,'yyyy...続きを読む

QSQL*LoaderでCSVから指定した列のみインポートしたい。

SQL*Loaderを用いてOracleのテーブルにデータを格納
しようと思っています。
格納するデータはカンマ区切りのCSVファイルです。

入力CSVファイルは他テーブル用のファイルなので、
ローダーで落とし込むテーブルとは列数が異なります。

ですので、「CSVの5列目の値をテーブルのAA_NOというフィールドに
格納する。ということが可能なのでしょうか?」

CSVファイルのある行が
a,b,c,d,e,f
というデータの場合、テーブルには
a,e,f
とインポートしたいのです。

よろしくお願いします。

Aベストアンサー

さきほど回答しましたhamu1です。
訂正します。
a,b,c,d,e,f
というデータの場合、テーブルには
a,e,f
をインポートするときには、

(略)
(
a,
"b" filler,
"c" filler,
"d" filler,
e,
f
)

となります。
間違えちゃって、、すいません。

QSQL*Loaderで、データを加工してロードしたいです。

SQL*Loaderを使ってデータをテーブルにロードしたいのですが、その際に、データを加工してロードしたいと考えております。

◎使用するコントロールファイルのサンプル
(項目1)(項目2)(項目3)
12345, abcde, ABCDE

◎使用するコントロールファイルのサンプル
Load Data
truncate
Into Table "Sample"
Fields terminated by ','
Optionally enclosed by '"'
(
 項目1,
 項目2,
 項目3,
 項目4 EXPRESSION ":項目1(の下1桁)|| ' ' || :項目2(の下2桁)|| ' ' || :項目3(の下3桁)"
)

まず、項目4には、項目1~3のデータを連結したものをロードしたいと考えております。
その際に、

項目4(6桁)="項目1の下1桁"+"項目2の下2桁"+"項目3の下3桁"

となるようにしたいのですが、どのような関数を使ったらよいのでしょうか?

また、連結する関数"EXPRESSION"とどのように組み合わせたらよいのか教えていただけないでしょうか?


思い当たる様々なキーワードでググってみたのですが、参考になるページを見つけることができませんでした。
「ヒントやるから自分で調べろ」みたいなお返事でも結構ですので、アドバイスをいただけますようお願い申し上げます。

SQL*Loaderを使ってデータをテーブルにロードしたいのですが、その際に、データを加工してロードしたいと考えております。

◎使用するコントロールファイルのサンプル
(項目1)(項目2)(項目3)
12345, abcde, ABCDE

◎使用するコントロールファイルのサンプル
Load Data
truncate
Into Table "Sample"
Fields terminated by ','
Optionally enclosed by '"'
(
 項目1,
 項目2,
 項目3,
 項目4 EXPRESSION ":項目1(の下1桁)|| ' ' || :項目2(の下2桁)|| ' ' || :項目3(の下3桁)"
)

まず、...続きを読む

Aベストアンサー

#1です。すいません、訂正です。

項目4 EXPRESSION "SUBSTR(:項目1,-1,1)|| ' ' || SUBSTR(:項目2,-1,2)|| ' ' || SUBSTR(:項目3,-1,3)"

QSQL/Loaderでの年月日時分秒のセットに関して

こんにちは、よろしくお願いします。

Oracle10gのテーブルの日付型の[SYSTRK_DT」フィールドに、
SQL/Loaderにて、ロードした当日の年月日時分秒をセットする文を
書いておりますが、以下の分で年月日はセットできたのですが、
時分秒までセットする方法をご存知の方いらっしゃいますでしょうか?
このような感じにしたいです→[20060105111353]
できれば24時間表記が良いのですが。。
よろしくお願いします。

LOAD DATA
APPEND
INTO TABLE TEST_MST
(
~~
SYSTRK_DT "TO_CHAR(SYSDATE,'yyyymmdd')"
~~
)

Aベストアンサー

日付型なので、そのままSYSDATEでは無理ですか?

Qsql*loader 数値のロード

SQL*LOADERにて
NUMBER(3,0)のフィールドに、小数点以下を含む数値をもったデータをロードすると
四捨五入か何かされて整数でデータがロードされてしまいます。
テーブル、CTLファイル、csvレコード、登録結果は以下のとおりです。

テーブル:
CREATE TABLE tbl1 (
CLM001NUMBER(3,0)NOT NULL,
CLM002VARCHAR2(30)
)

CTLファイル:
LOAD DATA
INFILE 'tbl1.csv'
BADFILE 'tbl1.bad'
TRUNCATE
INTO TABLE tbl1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
CLM001,
CLM002
)

csvレコード:
0.9,aaaaa
3.1,bbbbb

登録結果(SQL*LOADER実行結果):
1,aaaaa
3,bbbbb


小数点を含むデータをロード時にエラーとしたいのですが、何かいい方法ありますでしょうか?
環境はSQL*Loader: Release 9.2.0.1.0になります。

SQL*LOADERにて
NUMBER(3,0)のフィールドに、小数点以下を含む数値をもったデータをロードすると
四捨五入か何かされて整数でデータがロードされてしまいます。
テーブル、CTLファイル、csvレコード、登録結果は以下のとおりです。

テーブル:
CREATE TABLE tbl1 (
CLM001NUMBER(3,0)NOT NULL,
CLM002VARCHAR2(30)
)

CTLファイル:
LOAD DATA
INFILE 'tbl1.csv'
BADFILE 'tbl1.bad'
TRUNCATE
INTO TABLE tbl1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
CLM001,
CLM002
)

csvレコ...続きを読む

Aベストアンサー

フィールドの後にフィルタとして使うファンクションを指定すればOKです
たとえば、TRAILING NULLCOLSの後を
(
 CLM001 "TO_NUMBER(:CLM001 , '999')",
 CLM002
)
と書けば、CLM001の内容がTO_NUMBER関数で指定した'999'という書式(3桁までの整数)から外れてる物は変換エラーとなるのでbad送りになります。

細かい制御が必要なら自作のフィルタファンクションを作ってみたらどうでしょうか?エラーとする場合は適当な例外を投げてください。

QSQL*Loader Append

SQL*Loaderのコントロールファイル内のパラメータ設定についてですが、
ネットで調べた結果
ロード方法をAPPENDとすると既存データがある場合は新しい行として追加とあるのですが、
これはテーブルのPKが重複していても新しく行がついかされるということでしょうか?

逆にREPLACEはPKが重複しているデータに上書きされるということでしょうか?

よろしくお願いいたします。

Aベストアンサー

■APPENDで主キー重複が起きるか?
ダイレクトパスロードか否かで、結果が変わります。

LoaderのオプションがDIRECT = TRUE、かつダイレクトモード可能な条件を満たしている場合、ダイレクトパスロードとになります(素晴らしく速い)が、APPENDモードで主キー重複のレコードができてしまいます。

主キーが重複した場合、主キー制約/インデックスが無効状態になっており、INSERT/DELETE/主キーのUPDATEがエラーになってしまいますので、ご注意ください。
その場合は、TRUNCATE TABLEでいったんデータを消してしまえば、復活できます。

ダイレクトパスロードでない場合のAPPENDモードでは、主キー重複はエラーではじかれます。

■REPLACEの動き
PKが重複するものの入れ替えではなく、まるっと削除してからの追加になります。
TRUNCATEとの違いは、途中でエラーが発生したときにレコードが削除する前の状態に戻る点です。
レコードが消えて良いならTRUNCATEモードのほうが速いですね

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

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')

と書くべきでしょうね。

QSQL Loaderを使いたい

Oracleのバージョンは8、自分のPCには Oracle8Client(Application User) がインストールされています。

見よう見まねで制御ファイルを記述し、バッチファイルを作成したところ、
( sqlldr USERNAME/PASSWORD@SID control= test.ctl )

コマンドプロンプトには以下のように表示されました。

「C:\SQL>sqlldr USERNAME/PASSWORD@SID control = test.ctl
'sqlldr' は、内部コマンドまたは外部コマンド、
操作可能なプログラムまたはバッチファイルとして認識されていません。」

実行できない原因はどこにあるか教えてください。

1.そもそもSQL Loaderを利用できる環境になっていない
(そのままでは使えなくて何かしら設定が必要とか)

2.コマンドがまちがっている
(test.ctlのパスを記述しないとだめとか、SIDは要らないだとか)

3.制御ファイルがまちがっている

どの可能性が一番高いでしょうか?

Aベストアンサー

Oracle8Client(Application User)だと
SQL Loaderはインストールされないのではないでしょうか?
Clientのインストール時に選択肢があって、
管理者用(正確な名称は覚えていない)でインストールする必要があったと思います。

QSQL*Loaderでのゼロ埋め

SQL*Loaderでデータを取り込む際に、対象データの左桁をゼロ埋めにする方法を教えてください。

例えば、取り込み対象の項目が、5桁未満の場合はゼロ埋めを行う。
・取り込み対象:111
・ゼロ埋め後の結果:00111(この値を取り込む)

上記を実装する方法が分からず困っております。

SQL*Loaderは初めて使用するため、調べる方法も見つけられませんでした。
何か良い方法がありましたら、お教え願えないでしょうか。
また、上記を実装するための参考になるサイト等を教えていただけないでしょうか。

よろしく御願い致します。

Aベストアンサー

左に文字列を追加するLPAD関数があります。
http://download.oracle.com/docs/cd/E16338_01/server.112/b56299/functions095.htm#sthref1253

SQL*Loaderの制御ファイルのフィールドリストでは関数を使用することができます。
http://download.oracle.com/docs/cd/E16338_01/server.112/b56303/ldr_field_list.htm#i1008150

ですので、
項目名 "LPAD(:項目名,5,'0')"
と記述すればいいです。


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

人気Q&Aランキング