Member
Joined: Feb 2006
Posts: 14 Plano, Texas USA |
DoDo, If you: * use 10gR2 client * grant execute on *both* types you will not have the problem you report. SQL*Plus: Release 10.2.0.3.0 - Production on Mon Apr 2 11:01:45 2007
Connected to:
Personal Oracle Database 10g Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> create user firstuser identified by pwd;
User created.
SQL> grant connect,create type, create table,unlimited tablespace to firstuser;
Grant succeeded.
SQL>
SQL> create user seconduser identified by pwd;
User created.
SQL> grant connect to seconduser;
Grant succeeded.
SQL>
SQL> connect firstuser/pwd@mjox
Connected.
SQL>
SQL> CREATE OR REPLACE TYPE DEV_MT300_15D_T1 AS OBJECT
2 (
3 p17a Varchar2(1),
4 p32b_1 Varchar2(3),
5 p32b_2 Varchar2(15),
6 p53V Varchar2(1),
7 p53_PID Varchar2(40),
8 p53_1 Varchar2(40),
9 p53_2 Varchar2(40),
10 p53_3 Varchar2(40),
11 p53_4 Varchar2(40),
12 p53_5 Varchar2(40),
13 p56V Varchar2(1),
14 p56_PID Varchar2(40),
15 p56_1 Varchar2(40),
16 p56_2 Varchar2(40),
17 p56_3 Varchar2(40),
18 p56_4 Varchar2(40),
19 p56_5 Varchar2(40),
20 p57V Varchar2(1),
21 p57_PID Varchar2(40),
22 p57_1 Varchar2(40),
23 p57_2 Varchar2(40),
24 p57_3 Varchar2(40),
25 p57_4 Varchar2(40),
26 p57_5 Varchar2(40),
27 p58V Varchar2(1),
28 p58_PID Varchar2(40),
29 p58_1 Varchar2(40),
30 p58_2 Varchar2(40),
31 p58_3 Varchar2(40),
32 p58_4 Varchar2(40),
33 p58_5 Varchar2(40)
34 )
35 ;
36 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE DEV_MT300_15D_T2 AS VARRAY(10) OF DEV_MT300_15D_T1;
2 /
Type created.
SQL>
SQL> create table TESTTABLE
2 (
3 A_P20 VARCHAR2(16) not null,
4 D_P17ATOP58 DEV_MT300_15D_T2
5 )
6 ;
Table created.
SQL>
SQL> grant execute on DEV_MT300_15D_T1 to seconduser;
Grant succeeded.
SQL> grant execute on DEV_MT300_15D_T2 to seconduser;
Grant succeeded.
SQL> grant select on TESTTABLE to seconduser;
Grant succeeded.
SQL>
SQL> connect seconduser/pwd@mjox
Connected.
SQL>
SQL> select * from firstuser.TESTTABLE;
no rows selected I suspect, but cannot prove, your issue is related to grants and not a bug with both 8i/9i clients since I can reproduce the exact error you reported originally with the 10gR2 client and also resolve it with proper grants. Can you try these exact series of commands with your 8i or 9i client and your database? Connect as a DBA account to run: create user firstuser identified by pwd;
grant connect,create type, create table,unlimited tablespace to firstuser;
create user seconduser identified by pwd;
grant connect to seconduser;
connect firstuser/pwd@sid
CREATE OR REPLACE TYPE DEV_MT300_15D_T1 AS OBJECT
(
p17a Varchar2(1),
p32b_1 Varchar2(3),
p32b_2 Varchar2(15),
p53V Varchar2(1),
p53_PID Varchar2(40),
p53_1 Varchar2(40),
p53_2 Varchar2(40),
p53_3 Varchar2(40),
p53_4 Varchar2(40),
p53_5 Varchar2(40),
p56V Varchar2(1),
p56_PID Varchar2(40),
p56_1 Varchar2(40),
p56_2 Varchar2(40),
p56_3 Varchar2(40),
p56_4 Varchar2(40),
p56_5 Varchar2(40),
p57V Varchar2(1),
p57_PID Varchar2(40),
p57_1 Varchar2(40),
p57_2 Varchar2(40),
p57_3 Varchar2(40),
p57_4 Varchar2(40),
p57_5 Varchar2(40),
p58V Varchar2(1),
p58_PID Varchar2(40),
p58_1 Varchar2(40),
p58_2 Varchar2(40),
p58_3 Varchar2(40),
p58_4 Varchar2(40),
p58_5 Varchar2(40)
)
;
/
CREATE OR REPLACE TYPE DEV_MT300_15D_T2 AS VARRAY(10) OF DEV_MT300_15D_T1;
/
create table TESTTABLE
(
A_P20 VARCHAR2(16) not null,
D_P17ATOP58 DEV_MT300_15D_T2
)
;
grant execute on DEV_MT300_15D_T1 to seconduser;
grant execute on DEV_MT300_15D_T2 to seconduser;
grant select on TESTTABLE to seconduser;
connect seconduser/pwd@sid
select * from firstuser.TESTTABLE;
|