[解決済み] ORA-22905 - select 文でテーブルの種類を照会するとき。

質問事項

 DECLARE
 TYPE record_AB IS RECORD
   (
      AA              VARCHAR2 (16 BYTE),
      BB    VARCHAR2 (16 BYTE)
   );

  TYPE type_tab_AB IS TABLE OF record_AB
                        INDEX BY BINARY_INTEGER;

  tab_AB   type_tab_AB;

  BEGIN
   SELECT *
    BULK COLLECT INTO tab_AB FROM...
    ..
    SELECT * FROM TABLE (tab_AB) ;

SELECT from TABLE ステートメントに到達すると、 "ORA-22905: cannot access rows from a non-nested table item" が表示されます。

PLSQLでテーブル型に問い合わせることは可能でしょうか？

どのように解決するのですか？

PL/SQLでテーブルの型を問い合わせることは可能ですが、スキーマレベル、つまりPL/SQLの外側で型が宣言されているネストされたテーブルと配列に限られます。

エラーについて

ORA-22905: 入れ子になっていないテーブル項目から行にアクセスできない

は、サポートされていないテーブル型からクエリを実行しようとしていることを意味します。 あなたのタイプ type_tab_AB は連想配列であるため INDEX BY BINARY_INTEGER 節があります。 を削除して INDEX BY BINARY_INTEGER 節を使用することで type_tab_AB をネストされたテーブルタイプにすることです。 (配列もここで動作しますが、予想される行数の上限がわかっていない限り、使用することはお勧めしません。 Varray 型を宣言する際には、要素の最大数を指定する必要がありますが、入れ子のテーブル型にはそのような制限はありません)。

この変更を行った後でも、あなたのコードはまだ動作しないかもしれません。 次のようなエラーが発生する可能性があります（発生しない場合は下部の注をご覧ください）。

PLS-00642: SQLステートメントでローカルコレクションタイプは許可されていません。

これは、選択中の型がPL/SQL内部で宣言されているためです。 以下のように宣言する必要があります。 type_tab_AB を、そして record_AB を使用して、PL/SQL の外側で CREATE TYPE ... .

次に発生する問題は、キーワード RECORD . レコードタイプはPL/SQLの内部でのみ作成できます。 スキーマレベルでは作成できない . 変更点 RECORD を OBJECT を使用して修正します。

最後に遭遇する問題は SELECT t.AA, t.BB BULK COLLECT INTO tab_AB FROM ... ステートメントを使用します。 このままでは、このクエリでは次のようなエラーが発生します。

PL/SQL。ORA-00947: 値が足りない

各行から 2 つの項目を選択し、データを一括挿入するためのテーブルを 1 つだけ提供しています。 Oracle は、あなたが 2 つの項目をまとめて record_AB という型があります。 この問題は、クエリを次のように変更することで簡単に解決できます。 SELECT record_AB(t.AA, t.BB) BULK COLLECT INTO tab_AB FROM ... .

これらの変更を総合すると、問題が解決されるはずです。 以下は、いくつかのテストデータでテストテーブルを作成し、テーブルタイプにクエリできることを確認するSQL*Plusスクリプトの全容です。

CREATE TABLE some_table (AA VARCHAR2(16 BYTE), BB VARCHAR2(16 BYTE));

INSERT INTO some_table (AA, BB) VALUES ('aa 1', 'bb 1');
INSERT INTO some_table (AA, BB) VALUES ('aaaaaaaaaa 2', 'b 2');
INSERT INTO some_table (AA, BB) VALUES ('aaaaa 3', 'bbbbbbbbbbbbbb 3');
COMMIT;

VARIABLE curs REFCURSOR;

CREATE OR REPLACE TYPE record_AB AS OBJECT
   (
      AA    VARCHAR2 (16 BYTE),
      BB    VARCHAR2 (16 BYTE)
   );
/

CREATE OR REPLACE TYPE type_tab_AB IS TABLE OF record_AB;
/

DECLARE
  tab_AB   type_tab_AB;
BEGIN
  SELECT record_AB(t.AA, t.BB)
    BULK COLLECT INTO tab_AB 
    FROM some_table t;

  OPEN :curs FOR SELECT * FROM TABLE (tab_AB) ;
END;
/

PRINT :curs

の結果を SELECT の内容を tab_AB をカーソルに入れ、SQL*Plusのカーソル変数を使ってその内容をリストアップしています。 Oracle 11g XE でスクリプトを実行すると、「型が作成されました」「PL/SQL プロシージャが正常に完了しました」というメッセージがすべて表示された後に、以下のような出力が得られます。

AA               BB
---------------- ----------------
aa 1             bb 1
aaaaaaaaaa 2     b 2
aaaaa 3          bbbbbbbbbbbbbb 3

NOTE 簡単のため、質問者はOracle11以前を使用していると仮定しています。 Oracle12では、PL/SQLで宣言された型をSQLクエリで使用することが認められていると思いますので、PLS-00642のエラーは発生しないかもしれません。 その他、Oracle12ではどのような変更が必要になるかは、Oracle12をまだ使用していないので何とも言えません。