 "Select * Form testtable" froze Developer
|
Joined: Mar 2007
Posts: 147 Zagreb, Croatia
Member
|
OP
Member
Joined: Mar 2007
Posts: 147 Zagreb, Croatia |
Situation:
1-->
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)
);
2-->
CREATE OR REPLACE TYPE DEV_MT300_15D_T2 AS VARRAY(10) OF DEV_MT300_15D_T1;
3-->
create table TESTTABLE
(
A_P20 VARCHAR2(16) not null,
...
D_P17ATOP58 DEV_MT300_15D_T2,
...)
4-->
Grant all to second user and make public synonym
5--> Connect as second user
6--> select * Form TESTTABLE
RESULT:
ERROR screen saying:
Access violation at adress 606B90F0 in module 'OraClient9.Dll'. Read of address 00000000
7--> After OK only "END TASK" helps
Anyone?
p.s. When first user does the same "select" there's no error!
DoDo
--------------------------------------------
Be happy, tomorrow it can become only worse. If not, even better!
|
|
|
 Re: "Select * Form testtable" froze Developer
|
Joined: Aug 1999
Posts: 22,206
Member
|
|
Member
Joined: Aug 1999
Posts: 22,206 |
Seems like an Oracle Net 9 bug (OraClient9.Dll is an internal Oracle Net library). Can you try the same from a 10g client?
Marco Kalter
Allround Automations
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Mar 2007
Posts: 147 Zagreb, Croatia
Member
|
|
OP
Member
Joined: Mar 2007
Posts: 147 Zagreb, Croatia |
I have Odacle 8.1.7 and the same error was with Oracle 8 client! Don'n know is 10g client will work with 8 databese?
DoDo
--------------------------------------------
Be happy, tomorrow it can become only worse. If not, even better!
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Aug 1999
Posts: 22,206
Member
|
|
Member
Joined: Aug 1999
Posts: 22,206 |
It should work with 8.1.7.
Marco Kalter
Allround Automations
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Feb 2006
Posts: 14 Plano, Texas USA
Member
|
|
Member
Joined: Feb 2006
Posts: 14 Plano, Texas USA |
This is not a bug of any kind in either PLD or the Oracle dll.
In addition to granting privileges to the table, one must also grant execute privileges on both types.
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Mar 2007
Posts: 147 Zagreb, Croatia
Member
|
|
OP
Member
Joined: Mar 2007
Posts: 147 Zagreb, Croatia |
R:Marco, as I reported this over E-mail, you answerd that it is problem with 8 client and I shoud upgrade client to 9 and now to 10g???
R:"Clever Idea Consulting", did it before reporting bug!
DoDo
--------------------------------------------
Be happy, tomorrow it can become only worse. If not, even better!
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Feb 2006
Posts: 14 Plano, Texas USA
Member
|
|
Member
Joined: Feb 2006
Posts: 14 Plano, Texas USA |
I'm not sure what DoDo means by his last posting, so let me clarify:
I don't have a machine with 8i or 9i client. However, I can verify that DoDo's behavior (in his first post) is completely reproducible via 10gR2 client libraries as well.
So for 10gR2, it's not a library issue. It's a vanilla RDBMS privileges issue.
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Mar 2007
Posts: 147 Zagreb, Croatia
Member
|
|
OP
Member
Joined: Mar 2007
Posts: 147 Zagreb, Croatia |
C.I.C:
I meant: "Is it good idea to try 10gR2?"
Thank you, now I know it is not!
By "did it before reporting bug!" I meant that I already tryed "granting". It was the first thing I thought off.
DoDo
DoDo
--------------------------------------------
Be happy, tomorrow it can become only worse. If not, even better!
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Feb 2006
Posts: 14 Plano, Texas USA
Member
|
|
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;
|
|
|
Re: "Select * Form testtable" froze Developer
|
Joined: Mar 2007
Posts: 147 Zagreb, Croatia
Member
|
|
OP
Member
Joined: Mar 2007
Posts: 147 Zagreb, Croatia |
Solved by "revoking" and "granting" again!
Thanks!
DoDo
--------------------------------------------
Be happy, tomorrow it can become only worse. If not, even better!
|
|
|
|
|
