（元）社内SEの徒然なる日記

Oracleでデータを登録する時に、SELECT文の抽出結果を登録する方法を良く使うのですが、少し複雑なSQL文（具体的には、副問い合わせを多用するようなもの）を書こうとすると副問い合わせがネストして後で追いにくくなります。

以前からWITH句を使えたらなぁって思っていたのですが、使い方が間違っていただけで普通に使えることに気が付きました。

テーブル「TESTTBL」（文字項目「STR1」、数値項目「NUM1」）という架空のテーブルに抽出結果を登録します。

抽出元はDUAL表を使用しました。

まず、普通のINSERT文。

-- 普通のINSERT〜SELECT
INSERT INTO TESTTBL
       (STR1,NUM1) 
SELECT '1' AS STR1
      , 1  AS NUM1
  FROM DUAL

副問い合わせを使用。

-- 副問い合わせを使用したINSERT〜SELECT
INSERT INTO TESTTBL
       (STR1,NUM1) 
SELECT A.STR1
      ,A.NUM1
  FROM
(
  SELECT '1' AS STR1
        , 1  AS NUM1
    FROM DUAL
) A

そして、これが本題のWITH句を使用したINSERT文です。

-- WITH句を使用したINSERT〜SELECT
INSERT INTO TESTTBL
       (STR1,NUM1) 
WITH A AS
(
  SELECT '1' AS STR1
        , 1  AS NUM1
    FROM DUAL
)
SELECT STR1
      ,NUM1
  FROM A

・・・WITH句って文の先頭にあるべきって思い込んでいました。


改めて、OracleのSQLリファレンスを読み返してみたのですが、INSERT文の肝心の部分って「subquery::=」となっていて、SELECTの構文を参照みたいになっていました。

んで、SELECT文の構文にはWITH句が記載されている訳でして。





ちなみに、手元にあったマニュアルがOracle9iという昔々のものだったのですが、その時代から既に使えたようです。

投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end ---

スポンサードリンク

データを抽出して一覧を表示をする画面で設計ミスがあったらしく、表示結果が重複表示されるケースが出てきました。

調べてみると原因は分かったのですが、それを直そうと思ったら結構面倒なSQLの書き換えが必要になってしまいます。そこで、DISTINCTを追加して抽出結果から重複を除去することにしました。

ところが、実際にSQL文を実行したところ、ORA-01791（SELECT式が無効です）が発生して異常終了してしまいました。


調べてみると、何でもソート順に指定している項目が抽出項目になっていない場合に発生するそうです。

例えば、下記のような書き方をすると同じエラーが発生します。

SELECT DISTINCT
       ITEM1
  FROM (
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL
           UNION ALL
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL 
       )
ORDER BY
       ITEM1
      ,ITEM2

ITEM2という項目がORDER BY句にはあるが、SELECT句にはありません。こういう書き方はダメってことらしいです。


手っ取り早い対応は、SELECT句にORDER BY句で指定した項目を追加することでしょうか。上記のサンプルの場合、下記のようにITEM2を追加することでSQLが通るようになりました。

SELECT DISTINCT
       ITEM1
      ,ITEM2
  FROM (
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL
           UNION ALL
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL 
       )
ORDER BY
       ITEM1
      ,ITEM2

後は、逆にORDER BYからITEM2を消すという方法もあるかと思います。そのソート条件、本当に必要なのって業務的な確認は必要ですけどね。

なお、DISTINCTを消してGROUP BY句で重複を除去とかやってもダメで、ORA-00979（GROUP BYの式ではありません。）が発生します。


テーブルを結合した結果、抽出結果が重複するというのは良くある話です。その対応方法も色々とあるのですが、私が行ったDISTINCTで重複を除去するというのは邪道かと思っています。

ただ、今回の場合は抽出結果を画面上に表示するだけで、そこから何か（更新処理とか）に連携するというものでもなかったので、別に良いかなと。そもそも、ソート順に指定する項目が抽出結果にないっていう書き方の時点で、ねぇ。

投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end ---

スポンサードリンク

ある日、ユーザーから伝票の更新処理がいつまでたっても終わらないと連絡が来ました。Webシステムと言う関係上、たまにサーバとの切断が切れて同じような現象になったりするのですが、伝票画面を開きなおしても状況が変わらないそうです。

結局、ＤＢ（Oracle）でロックが掛ったままになっていたことが原因のようなので、強制的にセッションを切断することにしました。


まず、ロックされているセッションの一覧を表示します。ロックは、V$LOCKED_OBJECTから探せるようです。後でセッションを切断するのに必要な情報（セッション識別子とシリアル番号）は、V$SESSIONから取得します。

-- Oracleのロックを取得するSQL
SELECT
       TO_CHAR(S.LOGON_TIME,'YYYY/MM/DD HH24:MI:SS') AS ログイン日時
      ,L.ORACLE_USERNAME                             AS Oracleユーザー名
      ,S.SID                                         AS セッション識別子
      ,S.SERIAL#                                     AS シリアル番号
  FROM
      V$LOCKED_OBJECT L
     ,V$SESSION S
 WHERE 
       L.SESSION_ID = S.SID
ORDER BY
       1
      ,3

もう少し工夫すれば対象のオブジェクトの絞り込みも出来るのですが、私が管理しているシステムは、日中に長時間かかる処理が存在しません。なので、ログイン日時が分れば無駄にロックされているものが分かります。

結果は、こんな感じ。


この時の時間は１０時頃。ＤＢサーバーは毎日再起動しています。その条件下で見ると、７時のロックが残っているのはちょっと変です。どうやら犯人が見つかったようです。


私はOracle（というかシステム全般）を毎日再起動するようにしています。メーカーなどは、最近は起動しっぱなしでも問題無いと言いますが、私に言わせれば嘘も大概にしろと言いたいです。

実際に、こういう問題が発生するし、そうなっても再起動しておけば対応出来るエンジニアがいなかったとしても、翌日にはロックが解除（ＤＢが再起動されるので、自動的にそうなる）されて問題が解消されます。

なので、放置しても良かったのですが、この日は時間（と心と体に余裕）があったので、手動でロックを解除しました。

-- セッションを切断する
ALTER SYSTEM KILL SESSION 
  '272, 50867'    -- セッション識別子, シリアル番号
  IMMEDIATE

ロックの解除は、ALTER SYSTEM KILL SESSION を使用します。上記のように、V$SESSIONから取得したセッション識別子とシリアル番号を指定して実行すれば、それでＯＫです。


Oracleのバージョンは、Oracle12cです。2017年頃のサーバーリプレースに合わせて当時の最新バージョンにしたのですが、その辺りからロックが解除されない事象が稀に発生するようになりました。

プログラムは変えていないので、Oracle側の何らかのバグのような気がします。ただ、調査を始めると手間暇かかるし、パッチを当てても、それで新たな問題が発生する気もするので、このまま塩漬けにしようかと。

感覚的な話ですが、どうもOracle12cはバグが多い気がします。

投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end ---

スポンサードリンク

私が管理しているシステムは、夜間の処理で異常があるとメールが送信されます。はい、本日も深夜にメールが送信されました。

・・・眠い。

システムが稼働してから既に１５年以上になり、随時メンテナンスを続けているので滅多なことでは異常終了もしなくなってきたのですが、それでも出る時は出ますね。


勤め先の決算は３月末です。なので４月の上旬に年に一度の処理が夜間に実行されるのですが、見事にそれが異常終了しました。困ったものです。

結局出社して対応したのですが、コロナが社会問題になっている昨今の情勢なら、遠隔操作で自宅から対応できるようにした方が良いかもしれません。

・・・眠い。


うん、改めて見直すと文章に中身がない上にボロボロです。いや、障害対応自体は思ったほどの時間は掛からなかったのですが、興奮したのか寝付けなかったのです。

ちなみに、異常終了したのは年に一度だけ作成されていた帳票の出力処理です。違和感を感じる文章かと思いますが、これはシステム上は出力していたのですが、誰も使っていなかったという良くあるアレです。

今までは放置していたのですが、睡眠を邪魔されてムカついたので処理そのものを削除してやりました。これで来年からは安心です。

最新の記事：http://harikofu.blog.fc2.com/
投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end —
スポンサードリンク

年賀状のために新たにプリンタを購入したのですが（年賀状を印刷しようとしたらプリンタが壊れた・・・）、無事に到着しました。

EPSON PX-049Aです。


開封すると、トナーが入っていました。


・・・あぁ、やっぱり入っていたか。無かったら困るから別に購入しちゃったんですよね。そんなに大量に印刷する予定も舞から、数年間眠り続けそうです。

プリンタというか、厳密には複合機なので、これまで使っていたプリンタ（EPSON EP-803AW）と機能的には大差ありません。ですが、大きさはかなり違うようです。



久しぶりのプリンタのセットアップです。ここ数年はセットアップ系の作業から手を引いているので、少しは進化したのか楽しみです。

同封の説明書を開くと、インターネットに接続するように指示されました。Windowsの場合は同封のCDでも良いようですが、Macはインターネット必須のようです。

接続したホームページから機種（PX-049A）を入力して、指示に従って組み立てて、電源を投入して、パソコンに接続・・・しない！

以前のプリンタでもWi-Fi経由で印刷は出来たのですが（使ったことはないけど）、昨今は無線接続が標準になっているようです。Mac miniのUSBポートの数が足りなくて困っていたので、有難い話です。

インストーラーも優秀で、特に困ることもなくサクサク接続できました。

迷ったのは、トナーをセットした後の初期準備処理が妙に長くて心配になったくらいです。放っておくと電源ランプが点滅から点灯に変わるって話だったのですが、これが思ったよりも長く待たされたので、設定間違っていないか心配になりました。


本文では大きさが違うと書きましたが、重さもかなり違いました。本体の方の機能（ボタンとか）もスッキリしているし、こちらの方が好みです。

Macの最新OS（macOS Mojave）にも対応していたし、年賀状も問題なく印刷できたので一安心です。

最新の記事：http://harikofu.blog.fc2.com/
投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end —
スポンサードリンク