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
[EMAIL PROTECTED]
(replace 'x' with a 'u' to reply)