Cannont cast TYPE with a diffrent user then the owner.

From: ody <odys_at_gmx.net>
Date: 2 Dec 2003 07:42:15 -0800
Message-ID: <926fbe70.0312020742.5e677804@posting.google.com>

Hi Folks.

I've a problem. Shouldn't it be possible to cast TYPEs with an other user than the owner?

I made the following things:

• First i created a type and a table (with the owner of the database)

create or replace type test_row as object(test varchar2(15))
/

create type test_array as table of test_row
/

• Then i made a package fill the array (with the owner of the database)

create or replace package test_pack as
  function test
  return test_array;
end;
/

create or replace package body test_pack as   function test
  return test_array
  is
    t_test test_array := test_array();
  begin

    t_test.extend;
    t_test(t_test.last) := test_row('test1');
    t_test.extend;
    t_test(t_test.last) := test_row('test2');

    --
    return t_test;
  end;
end;
/

• Finally i did some grants for a test user (with the owner of the database)

create public synonym Test_Pack for Test_Pack
/

grant execute on Test_Pack to [2nd user (end user)]
/

grant execute on test_array to [2nd user (end user)]
/

grant execute on test_row to [2nd user (end user)]
/

• Now the select executed with the owner:

select * from table(cast(Test_Pack.test as test_array));

this works fine :)

• But if I execute this select with the sencond user (end user) i receve an Oracle error! I really don't konw why!

D10PA2_SQL> r
  1* select * from table(cast(Test_Pack.test as test_array)) select * from table(cast(Test_Pack.test as test_array))

                    *

ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

Thanks a lot for your HELP!

Greets
Ody Received on Tue Dec 02 2003 - 09:42:15 CST