SQL*Plus

linuxやWindows上で動いているOracle9iのSQL*Plusのメモです。

起動

sqlplusの引数にユーザー名を指定すると、そのユーザーでログオン（接続）する。[2008-02-20]
ユーザーの指定形式は「ユーザー」「ユーザー/パスワード」「ユーザー/パスワード@接続名」「ユーザー@接続名」等。
パスワードを指定していない場合は別途パスワードの入力が求められる。
接続名（サービス名）を省略した場合はデフォルトの接続先になる。

存在しないユーザーだったりパスワードが違ったり接続名が認識できなかったり間違っていたりするとエラーとなり、再度ユーザー名とパスワードの入力が要求される。（-Lオプションを指定していない場合）
ここではユーザー名とパスワードしか聞かれないので接続名が指定できないような感じがするが、ユーザー名の直後に「@接続名」を付ければOK。

> sqlplus scotttttt/tiger@ora92
ERROR:
ORA-01017: invalid username/password; logon denied

ユーザー名を入力してください: scott
パスワードを入力してください: tiger
ERROR:
ORA-12154: TNS: サービス名を解決できませんでした。

ユーザー名を入力してください: scott@ora92
パスワードを入力してください: tiger

また、「as sysdba」も同様にユーザー名の入力時に指定できる。

> sqlplus sys/manager
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper

ユーザー名を入力してください: sys as sysdba
パスワードを入力してください: manager

※要するにただ単にconnectコマンドが裏で実行されているだけっぽい。

ユーザー名を入力してください: scott --foo
SP2-0306: オプションが無効です。
使用方法: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]

sqlplus 〔オプション〕

（nolog以外で）ユーザー・パスワードを指定しなかった場合、別途聞かれる。ここでも、ユーザー名にハイフン「-」が入る場合はダブルクォーテーションで囲むこと。

$ sqlplus
ユーザー名を入力して下さい："test-1"
パスワードを入力して下さい：password

exp 〔ユーザー/パスワード〕 〔help=y〕

エクスポートを行う。

imp 〔ユーザー/パスワード〕 〔help=y〕

インポートを行う。

新しいバージョンのexp/impでは、古いバージョンのDBに対してエクスポート・インポートをする事が出来ない。[2008-12-17]
DBのバージョンに合ったコマンドを使う必要がある。

あるユーザーのDBを、他のユーザーのDBへコピーする方法

$ exp hishidama/hishidama owner=hishidama file=exp.dmp
$ imp hishidummy/hishidummy fromuser=hishidama touser=hishidummy file=exp.dmp

Oracle10gでハッシュパーティション化されたテーブルをエクスポートしたファイルは、バグっていてインポートできない。[2008-12-17]
そういう時はDDLだけは別の方法（例えばSQL Developerのexport）を使い、データだけimpでインポートするといいかも。
ダンプファイルの中にDDLも入っているので インポート時に“オブジェクトが既にある”というエラーにはなるが、「ignore=y」オプションを付けておけば無視して続行される。
（INSERT時の重複（duplicate）エラー等も無視して続行される）

> imp hishidummy/hishidummy fromuser=hishidama touser=hishidummy file=exp.dmp ignore=y

Windows版SQL*Plus

Windows版のSQL*Plusでは、ログオン用のダイアログが開く。[2005-05-27]
この「ユーザー名(U)」に「/nolog」「/as sysdba」「sys/password@ora92 as sysdba」と入れてやれば、コマンドライン版のsqlplusからそれぞれその引数を指定したのと同じになる。
ただし「ホスト文字列(H)」には何も入れてはいけない。
要するにここで指定した文字列をそのまま引数に渡しているみたいなんだな＾＾；

このWindows版のSQL*Plus（sqlpluswコマンド）は、Oracle11gで廃止になったらしい。[2008-01-13]
コマンドプロンプトから実行するsqlplusコマンドは残っているみたいだけど、桁数変更やコピー&ペーストや中断の利便性が全然違うからなぁ…。
代わりにSQL Developerを使えという事なのだろうか。[2008-12-17]

起動時実行SQLファイル

sql*plusの実行ファイルと同じ場所に「login.sql」というファイルを作っておくと、起動時にそのファイルが読み込まれる。[2006-11-21]
このファイルの中に書いたSQLが実行される。

セッションパラメータを初期化するのに便利。

sqlplus上のコマンド

SQL*Plusのコマンドはhelpコマンドで概要を見ることが出来る。

SQL*Plusのバッファには実行したSQLが入っており（SHOWやDESCといったSQL*Plusのコマンドは入らない） 、
バッファの内容を編集したり再実行したりすることが出来る。→バッファ編集

SQL*Plusのコマンド（とSQL文）は、行の末尾に「-（ハイフン1つ）」を付けることで次の行に継続して入力できる。[2007-12-30]
これで継続した行は、入力後には1行として扱われる。

SQL文を書いて末尾に「;（セミコロン。sqlterminatorで変更可能）」を付けてリターンキーを押すと、SQL文がバッファに格納され、実行される。[2008-01-14]
セミコロンを書かずに「.（ピリオド。blockterminatorで変更可能）」のみの行を書いてリターンキーを押すと、実行されずにバッファへの格納のみ行われる。
（でもこれってCtrl+Cで中断するのと同じような？ Ctrl+Cでもそれまでに書いた内容はバッファに入るし…）

SQL> timing start aaa
SQL> 何らかのSQL群を実行
SQL> timing show
aaaのタイミング。
経過: 00:00:10.09
SQL> timing stop
aaaのタイミング。
経過: 00:00:12.07
SQL> timi stop
SP2-0325: stopに対するタイミング要素はありません。

SQL> set timing on
SQL> 何らかのSQLを実行
経過: 00:00:00.01

SQL> host cd & pause

help

ヘルプの内容は、SQL*Plusが接続している“接続先のDB”から内容をとってきているような雰囲気。[2008-02-05]
つまり、実際にそのバージョンのSQL*Plusで使えるコマンドと、ヘルプで表示される内容が一致しない事がある。

例えばOracle9iのSQL*PlusからOracle10gのデータベースへつないでいる場合、「help show」は以下のような情報が表示される。

where option represents one of the following terms or clauses:
    system_variable
    ALL
    BTI[TLE]
    ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
       | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
    LNO
    PARAMETERS [parameter_name]
    PNO
    RECYC[LEBIN] [original_name]
    REL[EASE]
    REPF[OOTER]
    REPH[EADER]
    SGA
    SPOO[L]
    SQLCODE
    TT[ITLE]
    USER

リサイクルビンに関するシステム変数（RECYCLEBIN）がヘルプに表示されているが、実際はOracle10gのSQL*Plusでないと使えない。
（Oracle9iのSQL*Plusでは、使おうとするとエラーになってしまう）

conn / as sysdba

この書式で接続する場合、接続先のSIDは環境変数ORACLE_SIDで指定されているものになる。[2005-05-27]

DBが起動している場合、ORACLE_SIDを指定していなくても、以下の書式で接続できる。

SQL> conn /@orcl10g as sysdba
Connected.

起動していないと、以下のように使えない…。

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> conn /@orcl10g as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

@ ?

@・@@・startコマンドでスクリプトファイル（SQLが書かれたファイル）を実行することが出来る。[2008-02-06]
その際はコマンドの後ろにファイル名を指定するわけだが、ここで「?」という記号は特殊な意味を持つ。
すなわち、実行しているsqlplusがインストールされている場所（ORACLE_HOME？）に変換される。

SQL> @ ?/test.sql
SP2-0310: ファイル"C:\oracle\ora92/test.sql"をオープンできません

ちなみに、ファイル名指定で拡張子を省略すると、システム変数suffixで定義されている文字列（デフォルトは"sql"）が自動的に付加される。

SQL> @ test
SP2-0310: ファイル"test.sql"をオープンできません。

SQL> @ ?
SP2-0310: ファイル"C:\oracle\ora92.sql"をオープンできません。

ついでに「@」も特殊な変換がされるようなされないような…。（バージョンによっては何も変換されない？）

SQL> @ @
SP2-0310: ファイル"ora92.sql"をオープンできません。

環境変数

sql*plusの起動時に読み込まれて使われる。

セッションパラメータ

セッション毎に設定を行う。[2006-11-21]

設定するには、「alter session」を使う。（commitは不要）

SQL> alter session set NLS_DATE_FORMAT='yyyy/mm/dd';

セッションが変更されました。

SQL> select sysdate from dual;

SYSDATE
----------
2006/11/21

設定内容を確認するには、NLS_SESSION_PARAMETERSやv$nls_parametersというビューを使用する。[/2006-12-26]

SQL> select * from NLS_SESSION_PARAMETERS
   2 where parameter='NLS_DATE_FORMAT';

PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
yyyy/mm/dd

システム変数

showで照会し、setで設定する。大文字でも小文字でも指定可。 [2004-10-02]
「show all」で全変数の内容を表示する。
「help set」で変数一覧（超概要説明）が表示される。

置換変数

SQL*PlusのコマンドやSQL文の実行時にメッセージを表示し、文字列部分に値を入力できる。[2006-07-08]
「&文字列」の部分が、入力した値に置き換えられる。（「&」はデフォルトであり、DEFINEによって変えられる）
（置換変数の文字そのものを表示したい場合は、エスケープ文字を使用する。[2007-06-26]）

SQL> select '&var&hoge' from dual;
varに値を入力してください: aa
hogeに値を入力してください: bb
旧 1: select '&var&hoge' from dual
新 1: select 'aabb' from dual

'AAB
----
aabb

SQL> select &col from emp;
colに値を入力してください: empno
旧 1: select &col from emp
新 1: select empno from emp

EMPNO
----------
7369
〜

PL/SQLにも使用可能。

「&」1つだと、置換変数に値をセットしない限り、実行時に何度でも値の入力を求められる。[2008-04-26]
「&&」の様に2つにすると、一度目で入力した値がその置換変数に保持され、二度目からはその値が使用される。

SQL> select &&col from emp;

また、SQL*Plusの起動時にファイルを読み込んで実行する場合、引数を指定することが出来る。
これは「&数値」という形になり、&1が1番目の引数を表す。

test.sql：

spool &1
select '引数2は&2' as 引数2 from dual;
exit

>sqlplus -s hishidama @test.sql zzz.txt arg2
旧 1: select '引数2は&2' as 引数2 from dual
新 1: select '引数2はarg2' as 引数2 from dual

引数2
-----------
引数2はarg2

→spool命令の引数もちゃんと置換されるので、zzz.txtにも同じ内容が出力される。

ここで引数が無かった場合にその番号（例えば上記では&3）を使うと、&文字列と同じく値の入力が要求される。

defineコマンドを使うと、現在定義されている置換変数を知ることが出来る。

SQL> define
DEFINE _CONNECT_IDENTIFIER = "ora92" (CHAR)			…暗黙に色々定義されているらしい
DEFINE _SQLPLUS_RELEASE = "902000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle9i Release 9.2.0.1.0 - Production	…バージョンっぽいのもある。v$versionと関係あるのかな？
JServer Release 9.2.0.1.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000100" (CHAR)
DEFINE 1 = "zzz.txt" (CHAR)					…起動時の引数もある
DEFINE 2 = "arg2" (CHAR)

defineコマンドによって値を定義しておくと 使用時に値の入力が要求されなくなり、定義した値が使われる。

SQL> select '&var' from dual;
varに値を入力してください: zzz	…最初は定義されていないので、値の入力が要求される
旧 1: select '&var' from dual
新 1: select 'zzz' from dual

'ZZ
---
zzz

SQL> def var=abc			…値を定義する
SQL> def var			…定義した値の確認
DEFINE VAR = "abc" (CHAR)

SQL> r
1* select '&var' from dual
旧 1: select '&var' from dual	…値の入力が要求されずに置換が実行される
新 1: select 'abc' from dual

'AB
---
abc

ピリオドは置換後の文字列との結合に使える。[2008-04-26]
（ピリオドはデフォルト値であり、システム変数CONCATによって変更可能）

SQL> select &var.no from emp;
varに値を入力してください: dept
旧   1: select &var.no from emp
新   1: select deptno from emp

    DEPTNO
----------
        20
〜

システム変数のverifyをoffにすれば、置換時の旧・新の情報は表示されなくなる。[2008-02-20]

SQL> set ver off
SQL> select '&var' from dual;
varに値を入力してください: zzz

'ZZ
---
zzz

acceptコマンドを使うと、ユーザーからの入力によって置換変数の値をセットすることが出来る。[2008-04-26]

SQL> accept var
zzz

acceptではオプションを指定することが出来る。（defineでは出来ない）

SQL> acc var num prompt 数値を入れてください：
数値を入れてください：zzz
SP2-0425: "zzz"は有効な数値ではありません。
数値を入れてください：123

置換変数を削除するにはundefineコマンドを使う。[2007-12-30]

SQL> undef var

バインド変数

SQL*Plusのバインド変数とは、SQL*PlusとPL/SQLとの間で共有できる変数。[2007-12-30]
SQL*Plusで代入してPL/SQLから使ったり、PL/SQLで代入してSQL*Plusから参照したり出来る。

PL/SQLはDBサーバー上で実行されるものであり、SQL*Plusはクライアントのツール。
したがってバインド変数の使用はサーバー（PL/SQL）とクライアント（SQL*Plus）間で通信が発生するらしい。

バインド変数の定義はvariableコマンドを使う。
バインド変数への代入はexecuteコマンドによる代入文・SELECT INTO文か、call〜intoを使う。[/2008-02-06]
バインド変数の参照はprintコマンドを使う。

SQL> variable v1 char(6)
SQL> execute :v1 := 'ABC' || 'DEF'

PL/SQLプロシージャが正常に完了しました。

SQL> print :v1

V1
--------------------------------
ABCDEF

SQL> var v2 number
SQL> exec select max(empno) into :v2 from emp

PL/SQLプロシージャが正常に完了しました。

SQL> print v2

        V2
----------
      7934

SQL> var v3 char(6)
SQL> call test_f('ABC') into :v3;

コールが完了しました。

SQL> print v3

V3
--------------------------------
ABCABC

参考： SHIFT the OracleのVARIABLE

→バインド変数の使用例