コーソルDatabaseエンジニアのブログ
技術ブログ
このエントリは JPOUG Advent Calendar 2013と、(全部俺) Oracle ACE Director Tanel Poder Advent Calendar 2013 の クロスエントリとなるAdvent Calendar 2日目のエントリです。
もう年末、早いですね。みなさんにとっての2013年はどのような年だったでしょうか。
データベースという観点から、自分の2013年を振り返ると、これまではほぼOracle Database一本で仕事をしていたところが、PostgreSQLやMySQLなどの Oracle Database 以外のRDBMSを使用する割合が増えたことが印象に残っています。
様々な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
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ですが、どうやって実装しているかが気になります。ということで、スクリプトを読んでみましたが・・・・最初は何をやっているのかさっぱり理解できませんでした。
短いのでスクリプト全体を引用しますが・・・
-- 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"があるコマンドはバッファの末尾にコマンドを追加する操作に対応します。
カレント行の後に新しい行を挿入するには、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で導入された「独自のデリミタ文字」を使用しています。
実は、昨年の JPOUG Advent Calendar では、Oracle ACE Directorである Tanel Poderさんの TPT Scriptの導入方法と、これに含まれる i.sql についてご紹介していました。
2年連続で、ネタをお借りしてしまいました。Thanks! Tanel!
明日は charade_oo4o さん です。どうぞよろしくお願いいたします！
渡部 亮太
・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数
2024年
2023年
2022年
2021年
2020年
2019年
2018年
2017年
2016年
2015年
2014年
2013年
2012年
2000年