Rem
Rem $Header: cdemo82.sql 03-jun-97.16:20:49 echen Exp $
Rem
Rem cdemo82.sql
Rem
Rem  Copyright (c) Oracle Corporation 1996, 1997. All Rights Reserved.
Rem
Rem    NAME
Rem      cdemo82.sql - sql to be executed before cdemo82
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cchau       08/18/97 - enable dictionary protection
Rem    echen       06/03/97 - fix name resolution problem
Rem    azhao       04/02/97 - add as object for create type
Rem    dchatter    07/19/96 - scott/tiger to cdemo82/cdemo82
Rem    slari       07/15/96 - Created
Rem

set echo on;

connect internal

drop user cdemo82 cascade;

create user cdemo82 identified by cdemo82;

grant connect, resource to cdemo82;

connect cdemo82/cdemo82;

drop table customerval;

drop table customerobj;

drop table extaddr;

drop table embextaddr;

drop type embedded_address;

drop type address_object;

drop type person;

drop table emp;

create type address_object as object (state char(2), zip char(10));

create type embedded_address as object (state char(2), zip char(10), preaddr REF address_object);

drop type address_value;

create type address_value as object (state char(2), zip char(10));

create table customerval (custno number, addr address_value);

insert into customerval values(100, address_value('CA', '94065'));

create table extaddr of address_object;

create table customerobj (custno number, addr REF address_object);

insert into extaddr values (address_object('CA', '94065'));

insert into customerobj values(1000, null);

update customerobj set addr = (select ref(e) from extaddr e where e.zip='94065');

insert into extaddr values (address_object('CA', '98765'));

insert into extaddr values (address_object('CA', '95117'));

select REFTOHEX(ref(e)) from extaddr e;

create table embextaddr of embedded_address;

insert into embextaddr values (embedded_address('CA', '95117', NULL));

drop table extper;

drop table empref;

drop table emp;

drop type person;

create type person as object ( name char(20), age number, address address_object );

create table emp (emp_id number, emp_info person);

create table empref (emp_id number, emp_info REF person);

create table extper of person;

create or replace procedure upd_addr(addr IN OUT address_object) is
begin
   addr.state := 'CA';
   addr.zip := '95117';
end;
/

commit;

set echo off;



