Oracle-Fehler ORA-22905: Kein Zugriff auf Zeilen aus einem nicht verschachtelten Tabellenelement

Question

Hier ist die gespeicherte Prozedur, die ich geschrieben habe: In dieser Prozedur ist "p_subjectid" ein Array von Zahlen, die vom Frontend übergeben werden.

PROCEDURE getsubjects(p_subjectid subjectid_tab,p_subjects out refCursor) 
       as

       BEGIN

            open p_subjects for select * from empsubject where subject_id in
            (select column_value from table(p_subjectid));
            --select * from table(cast(p_subjectid as packg.subjectid_tab))
      END getsubjects;

Dies ist die Fehlermeldung, die ich erhalte.

Oracle error ORA-22905: cannot access rows from a non-nested table item OR

Wie ich in verschiedenen Beiträgen gesehen habe, habe ich versucht, "cast(p_subjectid as packg.subjectid_tab)" innerhalb der Tabellenfunktion zu verwenden, wie im unten stehenden Kommentar angegeben, aber ich erhalte einen anderen Fehler: ORA-00902: invalid datatype .

Und dies ist die Definition der "subjectid_tab".

type subjectid_tab is table of number index by binary_integer;

Kann mir jemand sagen, wo der Fehler liegt und ob mit meinem Verfahren etwas nicht stimmt?

Answer 1

Sie müssen den Typ auf "Datenbankebene" deklarieren, wie ammoQ vorgeschlagen hat:

CREATE TYPE subjectid_tab AS TABLE OF NUMBER INDEX BY binary_integer;

anstatt den Typ in PL/SQL zu deklarieren. Wenn Sie den Typ nur im PL/SQL-Block deklarieren, ist er für die SQL-"Engine" nicht verfügbar.

Answer 2

Oracle hat zwei Ausführungsbereiche: SQL und PL/SQL. Wenn Sie eine SELECT / INSERT / UPDATE (usw.)-Anweisung arbeiten Sie im SQL-Bereich, und in Oracle 11g und darunter können Sie nicht auf Typen verweisen, die im PL/SQL-Bereich definiert sind. ( Hinweis: Oracle 12 hat dies geändert, so dass Sie PL/SQL-Typen referenzieren können).

TYPE subjectid_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Ist ein assoziatives Array und kann nur im PL/SQL-Bereich definiert werden, kann also nicht in SQL-Anweisungen verwendet werden.

Was Sie wollen, ist eine Sammlung (kein assoziatives Array) im SQL-Bereich mit zu definieren:

CREATE TYPE subjectid_tab IS TABLE OF NUMBER;

( Hinweis: Sie benötigen nicht die INDEX BY Klausel für eine Sammlung. )

Dann können Sie das tun:

OPEN p_subjects FOR
  SELECT *
  FROM   empsubject
  WHERE  subject_id MEMBER OF p_subjectid;

o

OPEN p_subjects FOR
  SELECT *
  FROM   empsubject
  WHERE  subject_id IN ( SELECT COLUMN_VALUE FROM TABLE( p_subjectid ) );

Answer 3

Ich hatte dieses Problem erst gestern.

DECLARE 
  TYPE number\_table IS TABLE OF NUMBER;
  result\_ids number\_table := number\_table();
BEGIN
  /\* .. bunch of code that uses my type successfully \*/ 

  OPEN ? AS 
  SELECT \* 
  FROM TABLE(CAST(result\_ids AS number\_table)); /\* BOOM! \*/
END;

Dies schlägt in beiden von Ihnen beschriebenen Fällen fehl, wenn es aus einer Java-Routine aufgerufen wird. Ich habe herausgefunden, dass dies daran liegt, dass der Typ number_table nicht in einer exportierbaren Form definiert ist, die aus der Datenbank ausgeliefert werden kann. Intern in der Routine funktioniert der Typ einwandfrei. Aber sobald Sie versuchen, einen zurückgebbaren Datensatz auszuführen, der in irgendeiner Weise darauf verweist (einschließlich IN-Klauseln?!?), erhalten Sie einen nicht definierten Datentyp.

Die eigentliche Lösung lautet also CREATE TYPE myschema.number_table IS TABLE OF NUMBER; Lassen Sie dann die Typdeklaration in Ihrem Block weg und verwenden Sie die Deklaration auf Schemaebene. Verwenden Sie den Schemaqualifizierer, um auf den Typ zu verweisen, nur um sicher zu sein, dass Sie den richtigen Typ verwenden.

Answer 4

Das ist die gute Lösung. Sie können keine Tabelle(cast()) verwenden, wenn sich der Typ, den Sie casten, im DECLARE-Teil des pl/sql-Blocks befindet. Sie müssen WIRKLICH CREATE TYPE my_type [...] verwenden. Andernfalls wird die Ausnahme "cannot fetch row[...]" ausgelöst.

Answer 5

Müssen Sie die Ergebnisse der Pipeline-Abfrage so darstellen:

Wenn Ihre Pipelinefunktion einen Zeilentyp von varchar2 zurückgibt, definieren Sie einen Typ (zum Beispiel )

CREATE OR REPLACE TYPE char_array_t is VARRAY(32) of varchar2(255);
select * from table(cast(fn(x) as user_type_t ) );

wird jetzt funktionieren.