« Advanced Oracle Troubleshooting Guide: When the wait interface is not enough [part 1] の翻訳 | メイン | Advanced Oracle Troubleshooting Guide, Part 2: No magic is needed, systematic approach will doの翻訳 »

SQL*Plusで展開表示 - pr.sql from Tanel Poder's TPT scripts

このエントリは JPOUG Advent Calendar 2013と、(全部俺) Oracle ACE Director Tanel Poder Advent Calendar 2013 の クロスエントリとなるAdvent Calendar 2日目のエントリです。

もう年末、早いですね。みなさんにとっての2013年はどのような年だったでしょうか。

データベースという観点から、自分の2013年を振り返ると、これまではほぼOracle Database一本で仕事をしていたところが、PostgreSQLやMySQLなどの Oracle Database 以外のRDBMSを使用する割合が増えたことが印象に残っています。

PostgreSQL、MySQLの 展開表示機能 (Expanded display)

様々なRDBMSを使うと、改めて Oracle Database の良いところや悪いところに気づかされます。 私は Oracle Database がおおむね気に入っているのですが、 PostgreSQLやMySQLが提供する機能で気に入ったものもいくつかあります。細かいところで恐縮ですが、PostgreSQLのCUIクライアントであるpsqlの\xや、MySQLのCUIクライアントであるmysqlの\Gは、SQL*Plusでも欲しい機能です。

これらの機能は、SELECT文で得られる結果セット表示を縦表示にするものです。これらの機能の呼び方はよくわかりませんが、PostgreSQLでは「展開表示(Expanded display)」と呼んでいるようですので、以後「展開表示」と書くようにします。文章で説明するよりも実際に見た方が早いと思いますので、以下にpsqlとmysqlの実行結果を示します。

[postgres ~]$ psql db1 user1
Password for user user1:
psql (9.0.13)
Type "help" for help.

db1=> SELECT * FROM t;
 col1 | col2 | col3 | col4 | col5
------+------+------+------+------
    1 | AAA  | AAA  | AAA  | AAA
    2 | BBB  | BBB  | BBB  | BBB
(2 rows)

db1=> \x
Expanded display is on.
db1=> SELECT * FROM t;
-[ RECORD 1 ]
col1 | 1
col2 | AAA
col3 | AAA
col4 | AAA
col5 | AAA
-[ RECORD 2 ]
col1 | 2
col2 | BBB
col3 | BBB
col4 | BBB
col5 | BBB

db1=>
[root@l64rw3 ~]# mysql -proot -Dtest
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM t;
+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 |
+------+------+------+------+------+
|    1 | AAA  | AAA  | AAA  | AAA  |
|    2 | BBB  | BBB  | BBB  | BBB  |
+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t\G
*************************** 1. row ***************************
col1: 1
col2: AAA
col3: AAA
col4: AAA
col5: AAA
*************************** 2. row ***************************
col1: 2
col2: BBB
col3: BBB
col4: BBB
col5: BBB
2 rows in set (0.00 sec)

展開表示機能は、結果セットの列数が多いときわめて有用です。以下のような表示はうんざりですよね・・・

[oracle@l64rw3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 金 11月 29 16:14:06 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.



Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- ---------------
SADDR            PADDR
---------------- ----------------
SYS                  c101         l64rw3.domain             26    2151     12.1.0.1.0 20131016 11013           22    11010
000000008C4CB908 000000008C9A1258



SQL> SELECT * FROM V$SESSION WHERE sid = 26;

SADDR                   SID    SERIAL#     AUDSID PADDR                 USER#
---------------- ---------- ---------- ---------- ---------------- ----------
USERNAME                          COMMAND    OWNERID TADDR
------------------------------ ---------- ---------- ----------------
LOCKWAIT         STATUS   SERVER       SCHEMA# SCHEMANAME
---------------- -------- --------- ---------- ------------------------------
OSUSER                         PROCESS
------------------------------ ------------------------
MACHINE                                                                PORT
---------------------------------------------------------------- ----------
TERMINAL                       PROGRAM
------------------------------ ------------------------------------------------
TYPE       SQL_ADDRESS      SQL_HASH_VALUE SQL_ID        SQL_CHILD_NUMBER
---------- ---------------- -------------- ------------- ----------------
SQL_EXEC_START    SQL_EXEC_ID PREV_SQL_ADDR    PREV_HASH_VALUE PREV_SQL_ID
----------------- ----------- ---------------- --------------- -------------
PREV_CHILD_NUMBER PREV_EXEC_START   PREV_EXEC_ID PLSQL_ENTRY_OBJECT_ID
----------------- ----------------- ------------ ---------------------
PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID
------------------------- --------------- -------------------
MODULE                                                           MODULE_HASH
---------------------------------------------------------------- -----------
ACTION                                                           ACTION_HASH
---------------------------------------------------------------- -----------
CLIENT_INFO
----------------------------------------------------------------
FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
-------------------- ------------- -------------- --------------- -------------
TOP_LEVEL_CALL# LOGON_TIME        LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI
--------------- ----------------- ------------ --- ------------- ---------- ---
RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU
-------------------------------- -------- -------- --------
CURRENT_QUEUE_DURATION
----------------------
CLIENT_IDENTIFIER                                                BLOCKING_SE
---------------------------------------------------------------- -----------
BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCK FINAL_BLOCKING_INSTANCE
----------------- ---------------- ----------- -----------------------
FINAL_BLOCKING_SESSION       SEQ#     EVENT#
---------------------- ---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT                                                                   P1
---------------------------------------------------------------- ----------
P1RAW
----------------
P2TEXT                                                                   P2
---------------------------------------------------------------- ----------
P2RAW
----------------
P3TEXT                                                                   P3
---------------------------------------------------------------- ----------
P3RAW            WAIT_CLASS_ID WAIT_CLASS#
---------------- ------------- -----------
WAIT_CLASS                                                        WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT STATE               WAIT_TIME_MICRO TIME_REMAINING_MICRO
--------------- ------------------- --------------- --------------------
TIME_SINCE_LAST_WAIT_MICRO
--------------------------
SERVICE_NAME                                                     SQL_TRAC SQL_T
---------------------------------------------------------------- -------- -----
SQL_T SQL_TRACE_ SESSION_EDITION_ID CREATOR_ADDR     CREATOR_SERIAL#
----- ---------- ------------------ ---------------- ---------------
ECID
----------------------------------------------------------------
SQL_TRANSLATION_PROFILE_ID PGA_TUNABLE_MEM     CON_ID
-------------------------- --------------- ----------
EXTERNAL_NAME
--------------------------------------------------------------------------------
000000008C4CB908         26       2151 4294967295 000000008C9A1258          0
SYS                                     3 2147483644
                 ACTIVE   DEDICATED          0 SYS
oracle                         11010
l64rw3.domain                                                             0
pts/1                          sqlplus@l64rw3.domain (TNS V1-V3)
USER       000000007B315E70     2942739648 0vg7yqfrqd960                0
20131129 16:15:06    16777217 000000007B315E70      2942739648 0vg7yqfrqd960
                0 20131129 16:14:37     16777216

sqlplus@l64rw3.domain (TNS V1-V3)                                 2803016664
                                                                           0

             3620561           441              1            3025             0
             94 20131129 16:14:07            0 NO  NONE          NONE       NO
OTHER_GROUPS                     DISABLED ENABLED  ENABLED
                     0
                                                                 NOT IN WAIT
                                   NOT IN WAIT
                               45        371
SQL*Net message to client
driver id                                                        1650815232
0000000062657100
#bytes                                                                    1
0000000000000001
                                                                          0
00                  2000153315           7
Network                                                                  -1
              0 WAITED SHORT TIME                 5
                        89
SYS$USERS                                                        DISABLED FALSE
FALSE FIRST EXEC                133 000000008C9A1258             206

                         0               0          0
oracle

pr.sqlを使ってOracle Databaseでも展開表示

SQL*Plusで展開表示機能を使用したい場合、Tanel Poderさんの TPT Scriptに含まれるpr.sqlを使うのがオススメです。このスクリプトは かの有名は Tom Kyte さんが作成したprint_tableを再実装した位置づけになります。

SQL> @pr
Tom Kyte's printtab
==============================
SADDR                         : 000000008C4CB908
SID                           : 26
SERIAL#                       : 2151
AUDSID                        : 4294967295
PADDR                         : 000000008C9A1258
USER#                         : 0
USERNAME                      : SYS
COMMAND                       : 3
OWNERID                       : 2147483644
TADDR                         :
LOCKWAIT                      :
STATUS                        : ACTIVE
SERVER                        : DEDICATED
SCHEMA#                       : 0
SCHEMANAME                    : SYS
OSUSER                        : oracle
PROCESS                       : 11010
MACHINE                       : l64rw3.domain
PORT                          : 0
TERMINAL                      : pts/1
PROGRAM                       : sqlplus@l64rw3.domain (TNS V1-V3)
TYPE                          : USER
SQL_ADDRESS                   : 00000000774F5CE8
SQL_HASH_VALUE                : 3029793098
SQL_ID                        : 9uvkayfu9dyaa
SQL_CHILD_NUMBER              : 0
SQL_EXEC_START                : 20131129 16:15:53
SQL_EXEC_ID                   : 16777216
PREV_SQL_ADDR                 : 0000000076C842D8
PREV_HASH_VALUE               : 1023521005
PREV_SQL_ID                   : cb21bacyh3c7d
PREV_CHILD_NUMBER             : 2
PREV_EXEC_START               : 20131129 16:15:53
PREV_EXEC_ID                  : 16788625
PLSQL_ENTRY_OBJECT_ID         : 5736
PLSQL_ENTRY_SUBPROGRAM_ID     : 47
PLSQL_OBJECT_ID               :
PLSQL_SUBPROGRAM_ID           :
MODULE                        : sqlplus@l64rw3.domain (TNS V1-V3)
MODULE_HASH                   : 2803016664
ACTION                        :
ACTION_HASH                   : 0
CLIENT_INFO                   :
FIXED_TABLE_SEQUENCE          : 3620693
ROW_WAIT_OBJ#                 : 655
ROW_WAIT_FILE#                : 1
ROW_WAIT_BLOCK#               : 4421
ROW_WAIT_ROW#                 : 0
TOP_LEVEL_CALL#               : 94
LOGON_TIME                    : 20131129 16:14:07
LAST_CALL_ET                  : 0
PDML_ENABLED                  : NO
FAILOVER_TYPE                 : NONE
FAILOVER_METHOD               : NONE
FAILED_OVER                   : NO
RESOURCE_CONSUMER_GROUP       : OTHER_GROUPS
PDML_STATUS                   : DISABLED
PDDL_STATUS                   : ENABLED
PQ_STATUS                     : ENABLED
CURRENT_QUEUE_DURATION        : 0
CLIENT_IDENTIFIER             :
BLOCKING_SESSION_STATUS       : NOT IN WAIT
BLOCKING_INSTANCE             :
BLOCKING_SESSION              :
FINAL_BLOCKING_SESSION_STATUS : NOT IN WAIT
FINAL_BLOCKING_INSTANCE       :
FINAL_BLOCKING_SESSION        :
SEQ#                          : 88
EVENT#                        : 151
EVENT                         : db file sequential read
P1TEXT                        : file#
P1                            : 1
P1RAW                         : 0000000000000001
P2TEXT                        : block#
P2                            : 12052
P2RAW                         : 0000000000002F14
P3TEXT                        : blocks
P3                            : 1
P3RAW                         : 0000000000000001
WAIT_CLASS_ID                 : 1740759767
WAIT_CLASS#                   : 8
WAIT_CLASS                    : User I/O
WAIT_TIME                     : -1
SECONDS_IN_WAIT               : 0
STATE                         : WAITED SHORT TIME
WAIT_TIME_MICRO               : 26
TIME_REMAINING_MICRO          :
TIME_SINCE_LAST_WAIT_MICRO    : 130090
SERVICE_NAME                  : SYS$USERS
SQL_TRACE                     : DISABLED
SQL_TRACE_WAITS               : FALSE
SQL_TRACE_BINDS               : FALSE
SQL_TRACE_PLAN_STATS          : FIRST EXEC
SESSION_EDITION_ID            : 133
CREATOR_ADDR                  : 000000008C9A1258
CREATOR_SERIAL#               : 206
ECID                          :
SQL_TRANSLATION_PROFILE_ID    : 0
PGA_TUNABLE_MEM               : 0
CON_ID                        : 0
EXTERNAL_NAME                 : oracle

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


SQL>

使い方は、TPT Script同梱のlogin.sqlを実行したセッションで、SELECT文の実行後にpr.sqlを実行するだけです。

pr.sqlを読んでみて???

こんな便利な pr.sqlですが、どうやって実装しているかが気になります。ということで、スクリプトを読んでみましたが・・・・最初は何をやっているのかさっぱり理解できませんでした。

短いのでスクリプト全体を引用しますが・・・

-- Notes:   This script is based on Tom Kyte's original printtbl code ( http://asktom.oracle.com )
--          For coding simplicity (read: lazyness) I'm using custom quotation marks ( q'\ ) so 
--          this script works only from Oracle 10gR2 onwards

prompt Pivoting output using Tom Kyte's printtab....

def _pr_tmpfile=&_tpt_tempdir/pr_&_tpt_tempfile..tmp

@@saveset
set serverout on size 1000000 termout off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==============================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      ( rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

@@loadset

get &_pr_tmpfile nolist
host &_delete &_pr_tmpfile 

冒頭の以下の箇所はstoreコマンドを使用して、SQL*Plusの設定を一時ファイルに保管している処理です。

def _pr_tmpfile=&_tpt_tempdir/pr_&_tpt_tempfile..tmp

@@saveset
set serverout on size 1000000 termout off
save &_pr_tmpfile replace
set termout on

最初さっぱり何をやっているのかさっぱりわからなかったのが以下の箇所です。行頭に数字って何だ?

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;

考えてもわからず、隣の席の同僚に相談したら疑問が氷解しました。行頭の数字は、SQL*Plusのバッファ操作のためのものでした。行頭に"0"があるコマンドはバッファの先頭にコマンドを追加する操作に対応し、 行頭に"999999"があるコマンドはバッファの末尾にコマンドを追加する操作に対応します。

  • SQL*Plusユーザーズ・ガイドおよびリファレンス リリース12.1 B71396-01
    新しい行の追加
カレント行の後に新しい行を挿入するには、INPUTコマンドを使用します。

行1の前に行を挿入するには、0(ゼロ)を入力し、その後にテキストを続けます。その行がバッファの先頭に挿入され、すべての行が再度1から番号付けされます。

0 SELECT EMPLOYEE_ID

実際に1行1行コマンドを打つとわかりやすいです。

SQL> SELECT * FROM V$SESSION WHERE sid = 26;

SADDR                   SID    SERIAL#     AUDSID PADDR                 USER#
---------------- ---------- ---------- ---------- ---------------- ----------
 (略)

SQL> list
  1* SELECT * FROM V$SESSION WHERE sid = 26
SQL> 0 c clob := q'\
SQL> list
  1  c clob := q'\ ←★コマンドがバッファの先頭に追加された
  2* SELECT * FROM V$SESSION WHERE sid = 26
SQL> 0 declare
SQL> list
  1  declare       ←★コマンドがバッファの先頭に追加された
  2  c clob := q'\
  3* SELECT * FROM V$SESSION WHERE sid = 26
SQL> 999999      \';;
SQL> list
  1  declare
  2  c clob := q'\
  3  SELECT * FROM V$SESSION WHERE sid = 26
  4*      \';      ←★コマンドがバッファの末尾に追加された

このようなバッファ操作を行うことで、バッファに無名PL/SQLブロックを構成する一連のコマンドを格納しています。また、行末に";"を含むコマンドをバッファに格納するため、行末に";;"を指定していることに注意してください。マニュアルからは確認できませんでしたが、このような工夫が必要なようです。

そして、無名PL/SQLブロックを構成する全ての一連のコマンドをバッファに格納してから、

999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/ ←★

行頭に数字がついていない"/"の箇所で、バッファに格納された無名PL/SQLブロックを実行しています。

いやー まさに「Hack」ですね。

ちなみに、SQLバッファを用いて組み立てた無名PL/SQLブロックの処理の概要は、CLOB型のcという変数に格納されたSQLコマンドをDBMS_SQLパッケージを使って実行し、列の値を整形しながら表示するものです。

また、SQLコマンドをCLOB型のcという変数に格納するため、Oracle Database 10.2で導入された「独自のデリミタ文字」を使用しています。

  • 「Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス 10g リリース2(10.2) B19257-01」
    「文字列リテラル」

最後に

実は、昨年の JPOUG Advent Calendar では、Oracle ACE Directorである Tanel Poderさんの TPT Scriptの導入方法と、これに含まれる i.sql についてご紹介していました。

2年連続で、ネタをお借りしてしまいました。Thanks! Tanel!

明日は charade_oo4o さん です。どうぞよろしくお願いいたします!

About

2013年12月02日 12:00に投稿されたエントリーのページです。

ひとつ前の投稿は「Advanced Oracle Troubleshooting Guide: When the wait interface is not enough [part 1] の翻訳」です。

次の投稿は「Advanced Oracle Troubleshooting Guide, Part 2: No magic is needed, systematic approach will doの翻訳」です。

他にも多くのエントリーがあります。メインページアーカイブページも見てください。

Powered by
Movable Type 3.34