select from table ( cast ( ... : ORA-00904: invalid column name

From: Ronny Voelker <ronny.voelker_at_elaxy.com>
Date: 29 Oct 2001 08:32:45 -0800
Message-ID: <db903314.0110290832.1b3586b8@posting.google.com>

Hello,

i have two users on a 8.1.16 database.
Each user owns a object type and a collection type containing the object type.
The types are exactly equal as in this sample:

---

create user user1 identified by user1 default tablespace data temporary tablespace temp;
create user user2 identified by user2 default tablespace data temporary tablespace temp;

grant create session to user1;
grant create type to user1;
grant create session to user2;
grant create type to user2;

connect user1/user1_at_sid

create type testobjtype is object (

   id                  number   ( 4     )

);
/

create type testarraytype as table of testobjtype;
/

connect user2/user2_at_sid

create type testobjtype is object (

   id                  number   ( 4     )

);
/

create type testarraytype as table of testobjtype;
/

---

If i create a select statement on the collection type as user1, the select works fine. If i connect after that as user2 and execute the same statement i get a 'ORA-00904: invalid column name'. example statement:

---

begin
declare

   ext_id number := 2;
   ext_test testarraytype;
begin

   ext_test := testarraytype ();
   select

      sum (x.id)
   into

      ext_id
   from

      table ( cast ( ext_test as testarraytype ) ) x

   ;
end;
end;
----------------------------------------------------------

If i execute a 'ALTER SYSTEM FLUSH SHARED_POOL' and then first connect as user2, it works fine with this user and user1 gets the error.

if i'm referencing directly to the types with user1.testarraytype and user2.testarraytype, i'm not geting any error. There also occurs no error, if both users have the 'execute any type' right.

It seems that there is only one parsed version in the library cache, which references to the type of the user who executed the statement first. And the second user in fact makes a select on the first users type, not on his type.

The full reference via user.type is no alternative for me. So whats the solution? Received on Mon Oct 29 2001 - 10:32:45 CST