• Oracle Database 12c R1
• 2014.03.06
• 渡部 亮太

DBMS_UTILITY.EXPAND_SQL_TEXTでビューへのSELECT文を再帰展開

Oracle Database 12cから、ビューへ発行されたSELECT文を再帰的に展開する機能が追加されました。 （実は昔からあったそうですが・・・後述）

例を用いて説明するのが理解がはやいでしょう。テーブルbase に対してビューv1を、ビューv1に対してビューv2を定義します。

SQL> create table base (i number, c varchar(80));

表が作成されました。

SQL> insert into base values(1, 'AAA');

1行が作成されました。

SQL> insert into base values(1, 'AAC');

1行が作成されました。

SQL> insert into base values(2, 'BBB');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL>
SQL> create view v1 as select i,c from base WHERE i = 1;

ビューが作成されました。

SQL> create view v2 as select i,c from v1 WHERE c = 'AAA';

ビューが作成されました。

SQL> SELECT * FROM v2;

         I C
---------- --------------------------------------------------------------------------------
         1 AAA

テーブルbase ←ビューv1 ← ビューv2の関係になっており、ビューv2に対するSELECTは当然正常に実行できます。このSELECT文をDBMS_UTILITY.EXPAND_SQL_TEXTでベースとなるテーブルのSELECT文に展開してみます。展開処理は再帰的に実行されるので、ビューが多段でもOKです。

SQL> set long 2000000000
SQL> set pagesize 0
SQL> set serveroutput on
SQL> declare
  2     original_sql clob :='SELECT * FROM v2';
  3     expanded_sql clob := empty_clob();
  4  begin
  5      dbms_utility.expand_sql_text(original_sql,expanded_sql);
  6      dbms_output.put_line(expanded_sql);
  7  end;
  8  /
SELECT "A1"."I" "I","A1"."C" "C" FROM  (SELECT "A2"."I" "I","A2"."C" "C" FROM  (SELECT "A3"."I" "I","A3"."C" "C" FROM
RYWATABE."BASE" "A3" WHERE "A3"."I"=1) "A2" WHERE "A2"."C"='AAA') "A1"

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

DBMS_SQL2.EXPAND_SQL_TEXT

• Oracle 12c New Features - DBMS_UTILITY.EXPAND_SQL_TEXT
  blog.ContractOracle.com
  http://blog.contractoracle.com/2013/06/oracle-12c-new-features.html

では、この機能は実は過去のバージョンより存在していたという指摘があります。

• Expanding SQL | Oracle Scratchpad
  http://jonathanlewis.wordpress.com/2012/07/10/expanding-sql/

されて、DBMS_SQL2.EXPAND_SQL_TEXTを使って同様の処理を・・・と思いましたが

SQL> declare
  2      m_sql_in    clob :='SELECT * FROM v2';
  3      m_sql_out   clob := empty_clob();
  4  begin
  5      dbms_sql2.expand_sql_text(m_sql_in,m_sql_out);
  6      dbms_output.put_line(m_sql_out);
  7  end;
  8  /
    dbms_sql2.expand_sql_text(m_sql_in,m_sql_out);
    *
行5でエラーが発生しました。:
ORA-06550: 行5、列5:
PLS-00201: 識別子DBMS_SQL2.EXPAND_SQL_TEXTを宣言してください。 ORA-06550:
行5、列5:
PL/SQL: Statement ignored

SQL> desc dbms_sql2
ERROR:
ORA-04043: オブジェクトdbms_sql2は存在しません。

SQL> desc sys.dbms_sql2
ERROR:
ORA-04043: オブジェクトsys.dbms_sql2は存在しません。

12.1.0.1ではすでにDBMS_SQL2パッケージが削除されているようです。

11.2.0.3ではDBMS_SQL2パッケージがあり、DBMS_SQL2.EXPAND_SQL_TEXTで同様の処理が可能でした。 知らなかった・・・

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> set long 2000000000
SQL> set pagesize 0
SQL> set serveroutput on
SQL> declare
  2      m_sql_in    clob :='SELECT * FROM v2';
  3      m_sql_out   clob := empty_clob();
  4  begin
  5      dbms_sql2.expand_sql_text(m_sql_in,m_sql_out);
  6      dbms_output.put_line(m_sql_out);
  7  end;
  8  /
SELECT "A1"."I" "I","A1"."C" "C" FROM  (SELECT "A2"."I" "I","A2"."C" "C" FROM  (SELECT "A3"."I" "I","A3"."C" "C" FROM RYWATABE."BASE" "A3" WHERE "A3"."I"=1) "A2" WHERE "A2"."C"='AAA') "A1"

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