Help us understand the problem. What is going on with this article?

バッチでOracleからデータ抽出をする(備忘録)

バッチでオラクルデーダベースに接続してデータ抽出を行う。
抽出結果はtxt形式、ログには実行時刻をファイル名につけて出力する。

仕事でどうしても中身だけが欲しく、形式はどうでもいいときに作ったスクリプトです。
カスタマイズすれば使える形だと思ったので、備忘録として記録します。

実行環境

windows server 2008 R2 Standard

バッチ構成

D:
 |--¥#Batch開発
       |
       |--bat
       |   |--テストora.bat
       |   |--LOG
       | 
       |--sql
           |--テストora.sql

テストora.bat

テストora.bat
SET ID=usr_taro
SET PW=test_pass
SET TNS=TS

SET dd=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%
SET d2=%DATE:~5,2%%DATE:~8,2%
SET tt=%TIME:~0,2%%TIME:~3,2%

SQLPLUS %ID%/%PW%@%TNS% @D:¥#Batch開発¥sql¥テストora.sql %d2% %tt%

テストpos.sql

テストora.sql
SET echo on
SET linesize 2000
SET trimspool on

spool .¥出力結果ログ_&2._&1..log
spool D:¥#Batch開発¥bat¥LOG¥出力結果.txt

select a.日付,a.番号,count(0)
from usr_taro.table_kanri a
where a.日付 > 20190131
group by a.番号 ,a.日付
order by 3 desc;

spool off

補足説明

結果、txtファイルにはecho offしていないので定義箇所が残り、
その下に抽出結果が表示されます。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away