| Type Permissions [message #39332] |
Mon, 08 July 2002 11:12  |
Nils Anderson
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
How can I make object types public?
I run the following as PROD user:
SQL> create type MY_TYPE as object
(ACCOUNT VARCHAR2(20));
I can use this type from the PROD user, but I want to be able to use it from other users, but when I attempt it I get:
PLS-00201: identifier 'MY_TYPE' must be declared
I've tried referencing as both 'PROD.MY_TYPE' and simply 'MY_TYPE' but neither works.
If I create a public synonym I get:
ORA-22863: synonym for datatype PROD.MY_TYPE not allowed
I can describe the type if I explicitly reference:
descr PROD.MY_TYPE will give me the type definition.
How can I use MY_TYPE from other users?
Thanks.
|
|
|
|
| Re: Type Permissions [message #39333 is a reply to message #39332] |
Mon, 08 July 2002 11:20  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
SQL> connect scott/tiger
SQL> create type MY_TYPE as object
2 (ACCOUNT VARCHAR2(20));
3
4 /
Type created.
SQL> connect suresh/suresh
Connected.
SQL> desc scott.my_type
Name Null? Type
------------------------------- -------- ----
ACCOUNT VARCHAR2(20)
SQL> connect scott/tiger
Connected.
SQL> connect scott/tiger
Connected.
SQL> create public synonym my_type for my_type;
Synonym created.
SQL> connect suresh/suresh
Connected.
SQL> desc my_type
Name Null? Type
------------------------------- -------- ----
ACCOUNT VARCHAR2(20)
|
|
|
|
| Re: Type Permissions [message #39335 is a reply to message #39332] |
Mon, 08 July 2002 13:22 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Suresh and Mahesh, I don't think we're actually answering Nils' question here. He would like other users to be able to use (compile code) using these types. The goal is not just to describe the object. I am actually trying to do just what Nils is trying and haven't figured out how to do this yet.
If, from the non-owner, you refer to the synonym within a PL/SQL block, you get the error:
ORA-22863: synonym for datatype (owner.typename) not allowed |
|
|
|
| Re: Type Permissions [message #39337 is a reply to message #39332] |
Mon, 08 July 2002 14:09 |
Nils Anderson
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
Thanks for checking.
I'm currently running Oracle 8.1.7.3.
The real reason that I'm trying to do this is that I'm trying to use ORACLE AQ. The payload that gets passed into the queue is a RAW type which will take an object type, but not a more conventional Record or Table Type. One user has create/drop/start/stop access to the queue (the PROD user), and I'd like to have all other users have enqueue/dequeue access...which I have been able to successfully set up if I use a native datatype for a payload (VARCHAR2 for example). However, if I set the queue to use an object data type (so I can pass several pieces of information to the queue) I get these errors.
If this cannot be done, does anyone have suggestions for workarounds?
Thanks.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
