{{ message }}
forked from oracle/python-cx_Oracle
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSetupSamples.sql
More file actions
311 lines (254 loc) · 9.31 KB
/
Copy pathSetupSamples.sql
File metadata and controls
311 lines (254 loc) · 9.31 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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
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
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
/*-----------------------------------------------------------------------------
* Copyright 2017, Oracle and/or its affiliates. All rights reserved.
*---------------------------------------------------------------------------*/
/*-----------------------------------------------------------------------------
* SetupSamples.sql
* Creates users and populates their schemas with the tables and packages
* necessary for the cx_Oracle samples. An edition is also created for the
* demonstration of PL/SQL editioning.
*
* Run this like:
* sqlplus / as sysdba @SetupSamples
*
* Note that the script SampleEnv.sql should be modified if you would like to
* use something other than the default configuration.
*---------------------------------------------------------------------------*/
whenever sqlerror exit failure
-- drop existing users and edition, if applicable
@@DropSamples.sql
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_numeric_characters='.,';
create user &main_user identified by &main_password
quota unlimited on users
default tablespace users;
grant
create session,
create table,
create procedure,
create type,
select any dictionary,
change notification
to &main_user;
grant execute on dbms_aqadm to &main_user;
grant execute on dbms_lock to &main_user;
create user &edition_user identified by &edition_password;
grant
create session,
create procedure
to &edition_user;
alter user &edition_user enable editions;
create edition &edition_name;
grant use on edition &edition_name to &edition_user;
-- create types
create type &main_user..udt_SubObject as object (
SubNumberValue number,
SubStringValue varchar2(60)
);
/
create or replace type &main_user..udt_Building as object (
BuildingId number(9),
NumFloors number(3),
Description varchar2(60),
DateBuilt date
);
/
-- create tables
create table &main_user..TestNumbers (
IntCol number(9) not null,
NumberCol number(9, 2) not null,
FloatCol float not null,
UnconstrainedCol number not null,
NullableCol number(38)
);
create table &main_user..TestStrings (
IntCol number(9) not null,
StringCol varchar2(20) not null,
RawCol raw(30) not null,
FixedCharCol char(40) not null,
NullableCol varchar2(50)
);
create table &main_user..TestCLOBs (
IntCol number(9) not null,
CLOBCol clob not null
);
create table &main_user..TestBLOBs (
IntCol number(9) not null,
BLOBCol blob not null
);
create table &main_user..TestTempTable (
IntCol number(9) not null,
StringCol varchar2(400),
constraint TestTempTable_pk primary key (IntCol)
);
create table &main_user..TestUniversalRowids (
IntCol number(9) not null,
StringCol varchar2(250) not null,
DateCol date not null,
constraint TestUniversalRowids_pk primary key (IntCol, StringCol, DateCol)
) organization index;
create table &main_user..TestBuildings (
BuildingId number(9) not null,
BuildingObj &main_user..udt_Building not null
);
create table &main_user..BigTab (
mycol varchar2(20)
);
create table &main_user..SampleQueryTab (
id number not null,
name varchar2(20) not null
);
create table &main_user..MyTab (
id number,
data varchar2(20)
);
create table &main_user..ParentTable (
ParentId number(9) not null,
Description varchar2(60) not null,
constraint ParentTable_pk primary key (ParentId)
);
create table &main_user..ChildTable (
ChildId number(9) not null,
ParentId number(9) not null,
Description varchar2(60) not null,
constraint ChildTable_pk primary key (ChildId),
constraint ChildTable_fk foreign key (ParentId) references &main_user..ParentTable
);
create table &main_user..Ptab (
myid number,
mydata varchar(20)
);
-- populate tables
begin
for i in 1..20000
loop
insert into &main_user..BigTab (mycol) values (dbms_random.string('A',20));
end loop;
end;
/
begin
for i in 1..10 loop
insert into &main_user..TestNumbers
values (i, i + i * 0.25, i + i * .75, i * i * i + i *.5,
decode(mod(i, 2), 0, null, power(143, i)));
end loop;
end;
/
declare
t_RawValue raw(30);
function ConvertHexDigit(a_Value number) return varchar2 is
begin
if a_Value between 0 and 9 then
return to_char(a_Value);
end if;
return chr(ascii('A') + a_Value - 10);
end;
function ConvertToHex(a_Value varchar2) return varchar2 is
t_HexValue varchar2(60);
t_Digit number;
begin
for i in 1..length(a_Value) loop
t_Digit := ascii(substr(a_Value, i, 1));
t_HexValue := t_HexValue ||
ConvertHexDigit(trunc(t_Digit / 16)) ||
ConvertHexDigit(mod(t_Digit, 16));
end loop;
return t_HexValue;
end;
begin
for i in 1..10 loop
t_RawValue := hextoraw(ConvertToHex('Raw ' || to_char(i)));
insert into &main_user..TestStrings
values (i, 'String ' || to_char(i), t_RawValue,
'Fixed Char ' || to_char(i),
decode(mod(i, 2), 0, null, 'Nullable ' || to_char(i)));
end loop;
end;
/
insert into &main_user..ParentTable values (1, 'Parent 1');
insert into &main_user..ParentTable values (2, 'Parent 2');
insert into &main_user..ParentTable values (3, 'Parent 3');
insert into &main_user..ParentTable values (4, 'Parent 4');
insert into &main_user..ParentTable values (5, 'Parent 5');
insert into &main_user..ChildTable values (1001, 1, 'Child 1 of Parent 1');
insert into &main_user..ChildTable values (1002, 2, 'Child 1 of Parent 2');
insert into &main_user..ChildTable values (1003, 2, 'Child 2 of Parent 2');
insert into &main_user..ChildTable values (1004, 2, 'Child 3 of Parent 2');
insert into &main_user..ChildTable values (1005, 3, 'Child 1 of Parent 3');
insert into &main_user..ChildTable values (1006, 3, 'Child 2 of Parent 3');
insert into &main_user..ChildTable values (1007, 4, 'Child 1 of Parent 4');
insert into &main_user..ChildTable values (1008, 4, 'Child 2 of Parent 4');
insert into &main_user..ChildTable values (1009, 4, 'Child 3 of Parent 4');
insert into &main_user..ChildTable values (1010, 4, 'Child 4 of Parent 4');
insert into &main_user..ChildTable values (1011, 4, 'Child 5 of Parent 4');
insert into &main_user..ChildTable values (1012, 5, 'Child 1 of Parent 5');
insert into &main_user..ChildTable values (1013, 5, 'Child 2 of Parent 5');
insert into &main_user..ChildTable values (1014, 5, 'Child 3 of Parent 5');
insert into &main_user..ChildTable values (1015, 5, 'Child 4 of Parent 5');
insert into &main_user..SampleQueryTab values (1, 'Anthony');
insert into &main_user..SampleQueryTab values (2, 'Barbie');
insert into &main_user..SampleQueryTab values (3, 'Chris');
insert into &main_user..SampleQueryTab values (4, 'Dazza');
insert into &main_user..SampleQueryTab values (5, 'Erin');
insert into &main_user..SampleQueryTab values (6, 'Frankie');
insert into &main_user..SampleQueryTab values (7, 'Gerri');
commit;
--
-- For PL/SQL Examples
--
create or replace function &main_user..myfunc (
a_Data varchar2,
a_Id number
) return number as
begin
insert into &main_user..ptab (mydata, myid) values (a_Data, a_Id);
return (a_Id * 2);
end;
/
create or replace procedure &main_user..myproc (
a_Value1 number,
a_Value2 out number
) as
begin
a_Value2 := a_Value1 * 2;
end;
/
--
-- Create package for demoing PL/SQL collections and records.
--
create or replace package &main_user..pkg_Demo as
type udt_StringList is table of varchar2(100) index by binary_integer;
type udt_DemoRecord is record (
NumberValue number,
StringValue varchar2(30),
DateValue date,
BooleanValue boolean
);
procedure DemoCollectionOut (
a_Value out nocopy udt_StringList
);
procedure DemoRecordsInOut (
a_Value in out nocopy udt_DemoRecord
);
end;
/
create or replace package body &main_user..pkg_Demo as
procedure DemoCollectionOut (
a_Value out nocopy udt_StringList
) is
begin
a_Value(-1048576) := 'First element';
a_Value(-576) := 'Second element';
a_Value(284) := 'Third element';
a_Value(8388608) := 'Fourth element';
end;
procedure DemoRecordsInOut (
a_Value in out nocopy udt_DemoRecord
) is
begin
a_Value.NumberValue := a_Value.NumberValue * 2;
a_Value.StringValue := a_Value.StringValue || ' (Modified)';
a_Value.DateValue := a_Value.DateValue + 5;
a_Value.BooleanValue := not a_Value.BooleanValue;
end;
end;
/
You can’t perform that action at this time.