forked from ashishjohn1908/Java
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcollection_user_9i.sql
More file actions
80 lines (64 loc) · 1.9 KB
/
Copy pathcollection_user_9i.sql
File metadata and controls
80 lines (64 loc) · 1.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
/*<TOAD_FILE_CHUNK>*/
-- The SQL*Plus script collection_user_9i.sql performs the following:
-- 1. Creates collection_user_9i
-- 2. Creates the database object and collection types
-- 3. Populates the database tables with example data
-- This script should be run by the system user (or the DBA)
CONNECT system/London92;
-- drop collection_user_9i
DROP USER collection_user_9i CASCADE;
-- create collection_user_9i
CREATE USER collection_user_9i IDENTIFIED BY collection_password;
-- allow collection_user_9i to connect and create database objects
GRANT connect, resource TO collection_user_9i;
GRANT UNLIMITED TABLESPACE TO collection_user_9i;
-- connect as collection_user_9i
CONNECT collection_user_9i/collection_password;
-- create the object and collection types
CREATE TYPE varray_phone_typ AS VARRAY(3) OF VARCHAR2(14);
/
/*<TOAD_FILE_CHUNK>*/
CREATE TYPE address_typ AS OBJECT (
street VARCHAR2(15),
city VARCHAR2(15),
state CHAR(2),
zip VARCHAR2(5),
phone_numbers varray_phone_typ
);
/
/*<TOAD_FILE_CHUNK>*/
CREATE TYPE nested_table_address_typ AS TABLE OF address_typ;
/
/*<TOAD_FILE_CHUNK>*/
-- create the tables
CREATE TABLE customers_with_nested_table (
id INTEGER PRIMARY KEY,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
addresses nested_table_address_typ
)
NESTED TABLE
addresses
STORE AS
nested_addresses;
-- insert sample data into customers_with_nested_table table
INSERT INTO customers_with_nested_table VALUES (
1, 'Steve', 'Brown',
nested_table_address_typ(
address_typ('2 State Street', 'Beantown', 'MA', '12345',
varray_phone_typ(
'(800)-555-1211',
'(800)-555-1212',
'(800)-555-1213'
)
),
address_typ('4 Hill Street', 'Lost Town', 'CA', '54321',
varray_phone_typ(
'(800)-555-1211',
'(800)-555-1212'
)
)
)
);
-- commit the transaction
COMMIT;