Oracle Error ORA-22905: Unable to access rows from non-nested table

Question

This is where the procedure I wrote is stored. 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 saw in different posts, I tried pouring "cast (p_subjectid as packg.subjectid_tab)" inside the table function, as indicated in the comment below. But I get another error: ORA-00902: invalid datatype .

And that definition is "subjectid_tab".

 type subjectid_tab is table of number index by binary_integer; 

Can someone please tell me what the error is. Something is wrong with my procedure.

Answer 1

You must declare the type at the "database level" as ammoQ suggested:

 CREATE TYPE subjectid_tab AS TABLE OF NUMBER INDEX BY binary_integer; 

instead of type declaration in PL / SQL. If you declare a type only in the PL / SQL block, it will not be available for the SQL engine.

Answer 2

I think you cannot use table table () in a table of numbers; it should be a table of objects.

Answer 3

This is a good decision. You cannot use the table (cast ()) if the type you are doing is in the DECLARE part of the pl / sql block. You really need to use CREATE TYPE my_type [...]. Otherwise, it will throw an exception "it is impossible to receive a string [...]".

Answer 4

you need to pass the results of the pipeline request like this:

If your pipeline function returns the string type varchar2, then determine the type (for example)

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

will work now.

Answer 5

I had this problem yesterday.

  Decare 
   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 methods described earlier when calling from a java procedure. I found that this is due to the fact that the number_table type is not defined in an export manner, which can be sent from the database. Type works fine inside a routine. But as soon as you try to execute a returned recordset that references it in some way (including IN?!? Clauses), you get an undefined data type.

So the solution is really CREATE TYPE myschema.number_table IS TABLE OF NUMBER; Then cancel the type declaration from your block and use the schema level declaration. Use the schema specifier to refer to the type to make sure that you are using the correct one.