Help us understand the problem. What is going on with this article?

Oracle Database 12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしいのでDBMS_XPLANで試してみる。

More than 1 year has passed since last update.

※本エントリで記述されている事項はドキュメント非記載のため、原則非サポートと考えて下さい。
※(2018/12/24追記)マニュアルに載ってました。

Oracle DatabaseデータベースPL/SQL言語リファレンス 18c
12.5.1 テーブル・ファンクションの概要
https://docs.oracle.com/cd/E96517_01/lnpls/plsql-optimization-and-tuning.html#GUID-4E10CBFA-4B6A-4761-8905-83C26C112694
次のように、同等の問合せをTABLE演算子なしで記述できます。
SELECT * FROM table_function_name(parameter_list)

下記ツイートで見かけたOracle Database 12cR2(12.2)の隠し機能彡(゚)(゚)

https://twitter.com/kibeha/status/955557265687359488
Huh? Is this new #OrclDB 12.2 undocumented feature that you can skip TABLE if you just add empty parentheses instead? Or is it just me that didn't know this was possible? #SQL #PLSQL

12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしい?彡(゚)(゚)
TABLEファンクションの代表と云えばDBMS_XPLAN、下記のようTABLE句を付けて使うのがセオリーですが。。。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9ht3ba3arrzt3, child number 0
-------------------------------------
UPDATE KET$_CLIENT_TASKS     SET STATUS = CASE STATUS WHEN 13 THEN 2
ELSE STATUS END,         WINDOW_NAME = NULL,         CURR_JOB_NAME =
NULL,         RETRY_COUNT = RETRY_COUNT+1,         LT_JOB_LOG_ID =
:log,         LT_TERM_CODE = 11,         LT_PRIORITY = TASK_PRIORITY,
      LT_ERROR = :err,         LT_DATE = :end,         LT_DURATION =
:ela,         LT_CPU_TIME = :cpu   WHERE CLIENT_ID = :cid     AND
OPERATION_ID = :oid     AND TARGET_TYPE = :tgt     AND TARGET_NAME =
:tgn

Plan hash value: 3863298075

-----------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |       |       |     1 (100)|
|   1 |  UPDATE            | KET$_CLIENT_TASKS |       |       |            |
|*  2 |   INDEX UNIQUE SCAN| KET$_TSK_PK       |     1 |   112 |     0   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND
              "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN)


27 rows selected.

SQL>

これを12cR2(12.2)環境でTABLE句無しで実行してみると……彡(゚)(゚)

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3');

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  9ht3ba3arrzt3, child number 0
-------------------------------------
UPDATE KET$_CLIENT_TASKS     SET STATUS = CASE STATUS WHEN 13 THEN 2
ELSE STATUS END,         WINDOW_NAME = NULL,         CURR_JOB_NAME =
NULL,         RETRY_COUNT = RETRY_COUNT+1,         LT_JOB_LOG_ID =
:log,         LT_TERM_CODE = 11,         LT_PRIORITY = TASK_PRIORITY,
      LT_ERROR = :err,         LT_DATE = :end,         LT_DURATION =
:ela,         LT_CPU_TIME = :cpu   WHERE CLIENT_ID = :cid     AND
OPERATION_ID = :oid     AND TARGET_TYPE = :tgt     AND TARGET_NAME =
:tgn

Plan hash value: 3863298075

-----------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |       |       |     1 (100)|
|   1 |  UPDATE            | KET$_CLIENT_TASKS |       |       |            |
|*  2 |   INDEX UNIQUE SCAN| KET$_TSK_PK       |     1 |   112 |     0   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND
              "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN)


27 rows selected.

マジやった!TABLE句省略できるやんけ!彡(゚)(゚)

ora_gonsuke777
やきうのお兄ちゃんなITエンジニア、主にOracle Database廻りを担当。日本オラクル株式会社 柴田 歩 ※本ブログに記述された見解は私個人の見解であり、所属する会社&組織の見解を必ずしも反映したものではありません。ご了承ください。
http://d.hatena.ne.jp/gonsuke777/
oracle
Oracle Cloudは、最先端の機能をSoftware as a Service、Platform as a ServiceおよびInfrastructure as a ServiceおよびData as a Serviceとして提供します。
https://cloud.oracle.com/ja_JP/home
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away