Oracle Database 11g TIPS

あくまで自分用のメモですが、
間違った箇所などをご指摘いただけると、ありがたいです。

接続関連

現在のOracleに接続しているプロセス情報

SELECT * FROM v$session;

また上記結果のSIDとSERIAL#を使って接続プロセスをKILL(切断)することができる。

-- 例) sidが123、serial#が444のとき
ALTER SYSTEM KILL SESSION '123, 444';

最大接続数などを表示
sql
SELECT * FROM v$license;


ROWNUMの挙動

ROWNUMはSELECTされるタイミングで番号が振られるので、
副問い合わせとORDER BYをうまく使わないと予想通りの結果を得られない。
よく調べずにMySQLのLIMITみたいな使い方をすると痛い目を見る。
sql
-- テーブル作成
CREATE TABLE "TBL_SHOHIN"
(
"ID" VARCHAR2(20 BYTE),
"NAME" VARCHAR2(20 BYTE)
);
;


  -- ROWNUMの挙動をテスト
  SELECT ROWNUM AS ROWNUM2, HOGE.* FROM (
    SELECT * FROM (
      SELECT 
         ROWNUM AS ROWNUM1
        ,ID
        ,NAME 
      FROM 
        TBL_SHOHIN 
      ORDER BY ID ASC
    ) ORDER BY ID DESC
  ) HOGE
  ;

■実行結果

ROWNUM2	ROWNUM1	ID	NAME
1	5	5	クッキー
2	4	4	スフレ
3	3	3	羊羹
4	2	2	どら焼き
5	1	1	プリン

記号

(+)記号は、先行する列が結合内の外部結合列であることを指定します。
sql
SELECT * FROM Table1, Table2
WHERE Table1.Key = Table2.Key (+)

上記と下記SQLは等価
sql
SELECT * FROM Table1
LEFT JOIN Table2 ON(Table1.Key = Table2.Key)


OTNの「演算子」詳細が記載されている
こちら

オブジェクト情報取得関連

SELECT *
FROM   USER_OBJECTS
WHERE  OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME;

指定できるOBJECT_TYPEは下記の通り
* TABLE
* INDEX
* VIEW
* FUNCTION
* PROCEDURE
* TRIGGER
* PACKAGE
* PACKAGE BODY
* SEQUENCE
* SYNONYM
* DATABASE LINK

また下記のSQLでプロシージャのソースを取得できる

SELECT * FROM USER_SOURCE WHERE NAME = 'プロシージャ名' ORDER BY LINE;

-- カラムの情報を取得
SELECT COLUMN_NAME FROM (
    SELECT 
      C.* 
    FROM 
       USER_TAB_COLUMNS C
      ,USER_TABLES T 
    WHERE
      C.TABLE_NAME = T.TABLE_NAME 
    ORDER BY C.TABLE_NAME, C.COLUMN_ID
)
WHERE TABLE_NAME = 'HOGE';

コードを自動生成するときなど、プロシージャの引数を知りたいことがある。
ソースを読めば判明するが、プログラムで処理する場合パースせずにやりたい。
そんなときに下記方法を利用する。

プロシージャはUSER_PROCEDURESに定義されている。
コンパイルエラーが出ているプロシージャはこのテーブルに記載されない。

-- ユーザー定義のプロシージャ一覧を表示
SELECT 
  OBJECT_NAME 
FROM 
  USER_PROCEDURES
;

プロシージャの引数は、USER_ARGUMENTSテーブルに記載されている。
コンパイルエラーが出ているプロシージャはこのテーブルに記載されない。

-- ユーザー定義プロシージャの引数一覧を取得する
SELECT 
  OBJECT_NAME,
  ARGUMENT_NAME,
  POSITION,
  DATA_TYPE,
  IN_OUT,
  DATA_LENGTH,
  PLS_TYPE
FROM 
  USER_ARGUMENTS
;

型変換の際にヌル文字混入

本来やるべきではないのだろうが、やまれぬ事情でVARCHAR2->NCHARに変換するときに、
ヌル文字列(\0)が混入する件。

    -- 1文字あたりのバイト数が拡張変換になるため？
    RTN_CODES := TO_NCHAR(TMP);

    -- ヌル文字を除去するとキレイなデータになる
    RTN_CODES := REPLACE(RTN_CODES, CHR(0), null);

-- 文字コードを確認する
SELECT NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')) AS CHAR_CS,
       NLS_CHARSET_NAME(NLS_CHARSET_ID('NCHAR_CS')) AS NCHAR_CS FROM DUAL;

実行結果

CHAR_CS	NCHAR_CS
JA16SJISTILDE	AL16UTF16