Re: Cannont cast TYPE with a diffrent user then the owner.

ody wrote:

> 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

Have you granted access to the TYPE to the second schema? It appears not.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)