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

Question

here's the stored procedure i wrote.In this proc "p_subjectid" is an array of numbers passed from the front end.

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;

This is the error i am getting.

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

as i have seen in different post,i tried casting "cast(p_subjectid as packg.subjectid_tab)" inside table function as given in the comment below.But i am getting another error: ORA-00902: invalid datatype.

And this is the definition of the "subjectid_tab".

type subjectid_tab is table of number index by binary_integer;

Can anyone please tell me what's the error.Is anything wrong with the my procedure.

Answer 1

You have to declare the type on "the database level" as ammoQ suggested:

CREATE TYPE subjectid_tab AS TABLE OF NUMBER INDEX BY binary_integer;

instead of declaring the type within PL/SQL. If you declare the type just in the PL/SQL block, it won't be available to the SQL "engine".

Answer 2

Oracle has two execution scopes: SQL and PL/SQL. When you use a SELECT/INSERT/UPDATE (etc) statement you are working in the SQL scope and, in Oracle 11g and below, you cannot reference types that are defined in the PL/SQL scope. (Note: Oracle 12 changed this so you can reference PL/SQL types.)

TYPE subjectid_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Is an associative array and can only be defined in the PL/SQL scope so cannot be used in SQL statements.

What you want is to define a collection (not an associative array) in the SQL scope using:

CREATE TYPE subjectid_tab IS TABLE OF NUMBER;

(Note: you do not need the INDEX BY clause for a collection.)

Then you can do:

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

or

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

Answer 3

This is the good solution. You cannot use a table(cast()) if the type that you cast is in the DECLARE part of the pl/sql block. You REALLY need to use CREATE TYPE my_type [...]. Otherwise, it will throw the "cannot fetch row[...]" exception.