Works - 逆引きSQL比較

このページでは、ORACLE について述べています。

ORACLE

MSSQL

SSA

共通事項

SQL文を書いたファイルをバッチで実行するには？

特定のディレクトリのスクリプトを実行できるようにするには？

外部のデータベースに接続するには？（異機種間接続）

ONT

WindowsクライアントでHELPコマンド

パスワードマスク

SQL*Plusのプロンプトから別ウィンドウでSQL*Plusを開く

クリアスクリーン

カレントディレクトリ表示（エクスプローラ）

カレントディレクトリ表示（DOSプロンプト）

拡張EDITコマンド（任意のエディタでスクリプトを非同期に開く）

拡張DESCコマンド（INDEXの有無を表示）

パラメータ渡しバッチ

スプールしたファイル名に実行時の日付を使う

SQL文を書いたファイルをバッチで実行するには？　▲TOP

ORACLE　MSSQL　SSA

spool hoge.txt;
select sysdate from dual;
spool off;
exit;

>sqlplus uid/pwd@接続文字列 @hoge.sql

CSVファイルをインポートするには？　▲TOP

ORACLE　MSSQL　SSA

LOAD DATA
INFILE 'C:\kemp.txt'
INTO TABLE KEMP
APPEND
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
EMPNO           integer external,
EMPNM           char,
JOB             char,
DEPTNO          integer external
)

>sqlldr usr/pwd@接続文字列 control=kemp.ctl log=kemp.log

CSVファイルにエクスポートするには？　▲TOP

ORACLE　MSSQL　SSA

exp2csv

set heading        off
set verify         off
set feed           off
set pause          off
set echo           off
set termout        off
set pagesize       0
set linesize 100
spool kemp.txt;
select
EMPNO || ',' ||
EMPNM || ',' ||
JOB   || ',' ||
DEPTNO
from kemp;
exit;

prompt カンマ区切りテキストにアンロードする
accept table_name prompt 'アンロードするテーブル名は？ :'
accept owner prompt 'アンロードするテーブルの所有者は？ :'

select sum(DATA_LENGTH) "類推されるデータ長"
from all_tab_columns
where table_name = upper('&table_name')
and owner = upper('&owner');

accept rec_len prompt 'アンロード時のデータ長は？（上記より多めに） :'

set heading        off
set verify         off
set feed           off
set pause          off
set echo           off
set trims          on
set termout        off
set pagesize       0

define delim = ','

col dummy noprint
col a     newline

spool &table_name.2csv.sql
---- UNLOAD START ----

prompt ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';;

prompt set verify         off
prompt set feed           off
prompt set heading        off
prompt set pagesize       0

prompt prompt ********************************
prompt prompt &table_name UNLOAD

prompt select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ' START' from dual;;

prompt set pause          off
prompt set echo           off
prompt set trims          on
prompt set termout        off
prompt set linesize       &rec_len

prompt spool &table_name..csv

prompt select

    select decode(column_id,1,'','''&delim''||') ||
    column_name || '||'
    from all_tab_columns
    where table_name = upper('&table_name')
    and owner = upper('&owner')
    order by column_id
/
prompt ''
prompt from &owner..&table_name;;

prompt spool off

prompt set termout        on

prompt select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ' END' from dual;;

prompt prompt ********************************

prompt prompt rows of &owner..&table_name
prompt select count(*) from &owner..&table_name;;

prompt prompt lines of &table_name..csv
prompt ho wc -l &table_name..csv

prompt prompt ********************************
prompt set pagesize       14
prompt set heading        on
prompt exit;;
---- UNLOAD END ----
spool off

set heading        on
set termout        on
set linesize       200
set pagesize       24
set feed           on

prompt ***************************************
prompt File '&table_name.2csv.sql' Created.
prompt ***************************************
--@@&table_name.2csv.sql

undef table_name
undef owner
undef rec_len

バッチでエラーをハンドリングするには？　▲TOP

ORACLE　MSSQL

--後続のコマンドが失敗した場合SQL*PlusをExitする
WHENEVER SQLERROR EXIT SQL.SQLCODE

--ORACLEでエラーになる構文
select getdate();
exit;

@echo off
sqlplus misc/misc @hoge.sql
set myerror=%errorlevel%
echo;
echo;
if not %myerror% == 0 (
    REM エラー処理
    echo バッチ処理でエラーが発生しました：%myerror%
) else (
    echo バッチ処理が完了しました
)

>hoge

ログインダイアログなしで接続するには？　▲TOP

ORACLE　MSSQL　SSA

特定のディレクトリのスクリプトを実行できるようにするには？　▲TOP

ORACLE　MSSQL　SSA

外部のデータベースに接続するには？（異機種間接続）　▲TOP

ORACLE　MSSQL

(1) init.ora の設定

　　GLOBAL_NAMES を false にする
　　（DBリンク使用時のFAQですな）

(2) Oracle Generic Connectivity が入っているか Universal Installer で確認

(3) select * from SYS.HS_FDS_CLASS を実行し検索可能か確認

　　テーブルがない場合、ORACLE_HOME\RDBMS\ADMIN\caths.sqlをsysユーザーで実行

(4) ODBC の設定

　　ex.
　　接続データベース：SQL Server 2000
　　データソース名：hsodbctest
　　　　　　　　　　　（とりあえずサクッとOS認証で）

(5) tnsnames.ora の設定

　　※接続文字列とデータソース名は必ずしも同じではない

    hsodbc =
      (DESCRIPTION = 
        (ADDRESS=(PROTOCOL=tcp)(HOST=<host_name>)(PORT=<port>))
        (CONNECT_DATA=(SID=hsodbc))
        (HS=OK)
      )

(6) listener.ora の設定

    (SID_DESC=
      (SID_NAME=hsodbc)
      (ORACLE_HOME = <oracle_home>)
      (PROGRAM=hsodbc)
    )

(7) ORCALE_HOME\HS\ADMIN\inithsodbc.ora の設定

　　※ここは必ずデータソース名でなくてはいけない
　　　　↓
　　HS_FDS_CONNECT_INFO = hsodbctest
　　HS_FDS_TRACE_LEVEL = 1
　　HS_FDS_TRACE_FILE_NAME = C:\Oracle\admin\<sid>\udump\HS.trc

(8) データベースリンクを作成

　　create database link hsodbc using 'hsodbc';

(9) SQL文実行

　　select * from kemp@hsodbc;

WindowsクライアントでHELPコマンド　▲TOP
サーバーが Solaris や HP で、SYSTEM.HELP が作成されていれば、Windows 側の SQL*Plus から使えます。

-- help.sql
-- Usage :
--        SQL> @help
--
accept target prompt 表示したいヘルプトピック（前方一致）：
set hea off;
set pagesize 0;
SELECT INFO FROM SYSTEM.HELP WHERE TOPIC LIKE UPPER('&target%') ORDER BY TOPIC, SEQ;
set hea on;
set pagesize 24;
undef target;

パスワードマスク　▲TOP
CONNECTコマンドだとパスワードが見えるのがヤダ、と言う人に

-- conn.sql
-- Usage :
--        SQL> @conn
--
accept uid prompt ユーザー名を入力：
accept pwd prompt パスワードを入力： HIDE
accept con prompt 接続文字列を入力：
connect &uid/&pwd@&con;

SQL*Plusのプロンプトから別ウィンドウでSQL*Plusを開く　▲TOP
※R7.3なら plus33w , R8.0なら plus80w

-- new+.sql
-- Usage :
--        SQL> @new+
--
show user;
accept uid prompt ユーザー名を入力：
accept pwd prompt パスワードを入力： HIDE
accept con prompt 接続文字列を入力：
ho start sqlplusw &uid/&pwd@&con;

クリアスクリーン　▲TOP
特に意味はないんですが。

-- cls.sql
-- Usage :
--        SQL> @cls
--
CLEAR SCR;

カレントディレクトリ表示（エクスプローラ）　▲TOP
カレントディレクトリを開く(Windows用)
パスを移動してなければ \oraxx\bin が開くでしょう。

-- bin.sql
-- Usage :
--        SQL> @bin
--
ho start explorer .

カレントディレクトリ表示（DOSプロンプト）　▲TOP
カレントディレクトリをDOSプロンプトで開く(Windows用)
パスを移動してなければ \oraxx\bin が開くでしょう。

-- binl.sql
-- Usage :
--        SQL> @binl
--
ho start command

拡張EDIT（任意のエディタでスクリプトを非同期に開く）　▲TOP
拡張EDIT(Windows用)
任意のエディタを使用するには _EDITOR変数を変更します。私はフリー版EMEDITOR を愛用しているので

SQL> define_editor="C:\Program Files\EmEditor\EMEDITOR.EXE"

とすれば EDITコマンドで EMEDITOR が起動します。毎回同じエディタにするなら、 login.sql などに書いておけばよいでしょう。まあ、ここまでは FAQ です。しかし、この状態のまま

SQL> ed 新規スクリプトファイル

と実行すると EMEDITOR のエラーになってしまうので、私は以下のようなスクリプトで EDITコマンドを代替しています。拡張子.SQL はあらかじめ EMEDITOR で開けるようにしておきます。EDITコマンドで開いた時と違い非同期なので、使い勝手も良好。

-- ed.sql
-- Usage :
--        SQL> @ed スクリプトファイル名（拡張子不要）
--
--        ※新規ファイルでもＯＫ
--
undef &1;
ho if not exist &1..sql type nul> &1..sql
ho start &1..sql

拡張DESCコマンド（INDEXの有無を表示）　▲TOP

-- desc.sql
-- INDEXの有無（PK / not PK）を表示
-- Usage :
--        SQL> @desc テーブル名
--
col 列名 format a26
col null format a4
col データ型 format a13
col "index(PK)" format a9
col "index(Not PK)" format a13

select
tc.column_name    "列名",
decode(nvl(tc.data_scale,0),0, --小数点なしか
  --小数点なし→整数か
    decode(tc.data_type,'NUMBER',
      --整数
        tc.data_type || '(' || ltrim(to_char(tc.data_precision)) || ')',
      --整数以外→VARCHAR2か
        decode(tc.data_type,'VARCHAR2',
        --VARCHAR2
          tc.data_type || '(' || ltrim(to_char(tc.data_length)) || ')',
        --VARCHAR2以外→CHARか
          decode(tc.data_type,'CHAR',
          --CHAR
            tc.data_type || '(' || ltrim(to_char(tc.data_length)) || ')',
          --それ以外
            tc.data_type
          )
        )
    ),
  --小数点あり
    tc.data_type || '(' || ltrim(to_char(tc.data_precision)) || ',' || ltrim(to_char(tc.data_scale)) || ')'
) "データ型",
decode(tc.nullable,'Y','  ','No') "Null",
decode(mod(v.v_isPrim,2),1,'○',null) "index(PK)",
decode(greatest(nvl(v.v_isPrim,0),1),1,null,'○') "index(Not PK)"
from user_tab_columns tc,
(
 select ic.column_name as v_column_name,
 sum(decode (ic.index_name,c.constraint_name,1,2)) as v_isPrim
 from user_ind_columns ic, user_constraints c, user_indexes i
 where ic.table_name = c.table_name (+)
 and ic.index_name = i.index_name (+)
 and 'P' = c.constraint_type (+)
 and c.status = 'ENABLED'
 and ic.table_name = upper('&&1')
 and c.table_name (+) = upper('&&1')
 group by ic.column_name
) v
where tc.table_name = upper('&&1')
and tc.column_name = v.v_column_name (+)
order by tc.column_id
;

パラメータ渡しバッチ　▲TOP
コマンドラインの SQL*Plus のヘルプ（ plusxx -h ）を見ると、
開始ファイルの後に、任意のパラメータが指定できる事になっています。
これは、ホスト変数 &1 ～ &n として扱われます。つまり、

-- param.sql
spool param.txt;
select name from tbl 
where col1 = '&1'
and col2 = '&2';
spool off;
exit;

こんなSQLを用意して、次の様なバッチファイルを作れば、
条件に該当するリストを取得できます。
※R7.3なら plus33 , R8.0なら plus80

param.bat
-----------------------------------------------------
sqlplus -s usr/pwd@con @param aaa bbb
start param.txt

スプールしたファイル名に実行時の日付を使う　▲TOP
COLUMNコマンドを使って可能です。

COLUMN system_date NEW_VALUE TIMESTAMP

SELECT to_char(sysdate,'YYYYMMDD') system_date FROM DUAL;
--時分秒まで持つ場合
--SELECT to_char(sysdate,'YYYYMMDDHH24MISS') system_date FROM DUAL;

--本日のLOGファイルをスプールする
spool filename&&TIMESTAMP..log

--ここ以降は、ログを取りたい何かの処理を書く
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd') || 'の処理');
end;
/
--またはSQLスクリプトの実行
@@hoge

spool off;

以前はこんな泥臭い事やってました。

-- todayspool.sql
set heading        off;
set verify         off;
set feed           off;
set linesize       200;
set pause          off;
set echo           off;
set termout        off;
set pagesize       0;
set trims          on;
set verify         off;
set serveroutput on size 1000000;

--本日のLOGファイルを作成するためのスクリプトを作成する
spool makeTodayLog.sql
begin
dbms_output.put_line('spool ' || to_char(sysdate,'yyyymmdd') || '.log');
--時分秒まで持つ場合
--dbms_output.put_line('spool ' || to_char(sysdate,'yyyymmdd_hh24miss') || '.log');
end;
/
spool off;
set heading        on;
set feed           on;
--set echo           on; --この辺はご自由に
set termout        on;
set pagesize       24;

--本日のLOGファイルをスプールする
@makeTodayLog.sql

--ここ以降は、ログを取りたい何かの処理を書く
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd') || 'の処理');
end;
/
--またはSQLスクリプトの実行
@@hoge

spool off;
exit;