For
example, CON_K executes the following command:
create type PERSON_TY as object
(Name VARCHAR2(25),
Address ADDRESS_TY);
/
If CON_K does not own the ADDRESS_TY abstract datatype, Oracle will respond to this
create type command with the following message:
Warning: Type created with compilation errors.
The compilation errors are caused by problems creating the constructor method when the
datatype is created. Oracle cannot resolve the reference to the ADDRESS_TY datatype because
CON_K does not own a datatype with that name.
CON_K will not be able to create the PERSON_TY datatype (which includes the ADDRESS_
TY datatype) unless KAREN_SHELL first grants her EXECUTE privilege on the type. The following
listing shows this grant command in action:
grant EXECUTE on ADDRESS_TY to CON_K;
NOTE
You must also grant EXECUTE privilege on the type to any user who
will perform DML operations on the table.
Now that the proper grants are in place, CON_K can create a datatype that is based on
KAREN_SHELL??™s ADDRESS_TY datatype:
create or replace type PERSON_TY as object
(Name VARCHAR2(25),
Address KAREN_SHELL.ADDRESS_TY);
Chapter 5: Developing and Implementing Applications 155
CON_K??™s PERSON_TY datatype will now be successfully created. However, using datatypes
based on another user??™s datatypes is not trivial. For example, during insert operations, you must
fully specify the name of the owner of each type. CON_K can create a table based on her
PERSON_TY datatype (which includes KAREN_SHELL??™s ADDRESS_TY datatype), as shown
in the following listing:
create table CON_K_CUSTOMERS
(Customer_ID NUMBER,
Person PERSON_TY);
If CON_K owned the PERSON_TY and ADDRESS_TY datatypes, an insert into the
CUSTOMER table would use the following format:
insert into CON_K_CUSTOMERS values
(1,PERSON_TY('Jane Doe',
ADDRESS_TY('101 Main Street','Dodgeville','WI',53595)));
This command will not work.
Pages:
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285