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

I think you cannot simpy use table() on a table of numbers; it has to be a table of objects.

Answer 2

you have to cast the results of the pipelined query so:

If your pipelined function returns a rowtype of varchar2 then define a type (for example )

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

will now work.

Answer 3

I just had this problem yesterday.

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;

This fails in both of the ways you described earlier when called from a java routine. I discovered this was due to the fact that the type number_table is not defined in an exportable manner than can be shipped off the database. The type works great internally to the routine. But as soon as you try to execute a returnable recordset that references it in any way (including IN clauses?!?) you get a datatype not defined.

So the solution really is CREATE TYPE myschema.number_table IS TABLE OF NUMBER; Then drop the type declaration from your block and use the schema level declaration. Use the schema qualifier to reference the type just to be sure you are using the right one.

Answer 4

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 5

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.