ORA-22905 - bei der Abfrage eine Tabelle mit einer select-Anweisung

Ist es möglich, die Abfrage-Tabelle-Typen in PL/SQL, aber nur, verschachtelte Tabellen und varrays, deren Typen deklariert sind, auf schema-Ebene, also außerhalb von PL/SQL.

Den Fehler

ORA-22905: kein Zugriff auf Zeilen, die von einer nicht-verschachtelte Tabelle-Element

bedeutet, dass Sie versuchen, eine Abfrage von einem nicht unterstützten Tabellentyp. Ihre Art type_tab_AB ist ein assoziatives array, weil der INDEX BY BINARY_INTEGER - Klausel. Entfernen Sie die INDEX BY BINARY_INTEGER - Klausel, um Ihren type_tab_AB eine geschachtelte Tabelle Typ. (Varrays würde auch hier arbeiten, aber ich würde nicht empfehlen, mit Ihnen, wenn Sie wissen eine Obere Schranke für die Anzahl der Zeilen zu erwarten. Bei der Deklaration eines varray-Typ, müssen Sie die maximale Anzahl von Elementen, während die nested table-Typen haben keine solche Einschränkung.)

Nach dieser änderung Ihr code kann immer noch nicht arbeiten. Der nächste Fehler, den Sie bekommen kann (siehe Hinweis unten, wenn Sie nicht) ist

PLS-00642: lokale Sammlung von Typen nicht erlaubt, die in SQL-Anweisungen

Dies ist, weil der Typ Sie auswählen, in erklärt wird, innerhalb von PL/SQL. Sie müssen erklären type_tab_AB, und record_AB außerhalb von PL/SQL mit CREATE TYPE ....

Das nächste problem auftreten wird, weil der keyword -RECORD. Record-Typen können nur innerhalb PL/SQL, Sie kann nicht geschaffen werden auf der schema-Ebene. Ändern RECORD zu OBJECT dieses Problem zu beheben.

Dem letzten problem, dem Sie begegnen, ist mit der SELECT t.AA, t.BB BULK COLLECT INTO tab_AB FROM ... - Anweisung. So wie es da steht, ist diese Abfrage erhalten Sie die folgende Fehlermeldung:

PL/SQL: ORA-00947: nicht genügend Werte

Wählen Sie zwei Elemente aus jeder Zeile und stellen nur eine Tabelle zu bulk-insert die Daten in. Oracle kann nicht ganz herausfinden, dass Sie möchten, zu stoßen die beiden Elemente in Ihrer record_AB geben. Sie können dieses Problem beheben, relativ leicht durch ändern der Abfrage zu SELECT record_AB(t.AA, t.BB) BULK COLLECT INTO tab_AB FROM ....

Gemeinsam diese Veränderungen sollte das problem lösen. Hier ist eine vollständige SQL*Plus-Skript erstellt eine test-Tabelle mit einige test-Daten und überprüft, ob er eine Abfrage der Tabelle Typ:

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

Habe ich das Ergebnis von SELECTing den Inhalt tab_AB in einen cursor, und verwendet ein SQL*Plus-cursor-Variablen auflisten seiner Inhalte. Die Ausgabe die ich bekomme, wenn ich das Skript auf Oracle 11g XE, nach der alle '- Typ angelegt " und "PL/SQL-Prozedur erfolgreich abgeschlossen" - Nachrichten, ist wie folgt:

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

HINWEIS: Einfachheit halber habe ich angenommen, dass der Fragesteller ist mit Oracle 11 oder älter. In Oracle 12, ich glaube, dass Sie berechtigt sind, zu verwenden deklarierte Typen in PL/SQL in einer SQL-Abfrage, so dass Sie möglicherweise nicht auf die PLS-00642 Fehler. Ich kann nicht sagen, was andere änderungen an meiner Antwort eventuell auch für Oracle 12, wie ich noch mit Oracle 12.