select from table ( cast ( ... : ORA-00904: invalid column name
Date: 25 Oct 2001 14:26:14 -0700
Message-ID: <db903314.0110251326.74ec2308_at_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 Thu Oct 25 2001 - 23:26:14 CEST