forked from oracle-samples/oracle-db-examples
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdemo.sql
More file actions
262 lines (225 loc) · 8.65 KB
/
demo.sql
File metadata and controls
262 lines (225 loc) · 8.65 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
/* Copyright (c) 2015, 2018, Oracle and/or its affiliates. All rights reserved. */
/******************************************************************************
*
* You may not use the identified files except in compliance with the Apache
* License, Version 2.0 (the "License.")
*
* You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0.
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*
* See the License for the specific language governing permissions and
* limitations under the License.
*
* NAME
* demo.sql
*
* DESCRIPTION
* Create database objects for the examples in your database user account.
*
* The video https://www.youtube.com/watch?v=WDJacg0NuLo
* shows how to create a new database user.
*
* Scripts to create Oracle Database's traditional sample schemas can
* be found at: https://github.com/oracle/db-sample-schemas
*
*****************************************************************************/
SET ECHO ON
-- For plsqlproc.js example for bind parameters
CREATE OR REPLACE PROCEDURE testproc (p_in IN VARCHAR2, p_inout IN OUT VARCHAR2, p_out OUT NUMBER)
AS
BEGIN
p_inout := p_in || p_inout;
p_out := 101;
END;
/
SHOW ERRORS
-- For plsqlfunc.js example on calling a PL/SQL function
CREATE OR REPLACE FUNCTION testfunc (p1_in IN VARCHAR2, p2_in IN VARCHAR2) RETURN VARCHAR2
AS
BEGIN
RETURN p1_in || p2_in;
END;
/
SHOW ERRORS
-- For refcursor.js example of REF CURSORS
CREATE OR REPLACE PROCEDURE get_emp_rs (p_sal IN NUMBER, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_recordset FOR
SELECT first_name, salary, hire_date
FROM employees
WHERE salary > p_sal;
END;
/
SHOW ERRORS
-- For plsqlarray.js example for PL/SQL 'INDEX BY' array binds
BEGIN EXECUTE IMMEDIATE 'DROP TABLE waveheight'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
CREATE TABLE waveheight (beach VARCHAR2(50), depth NUMBER);
CREATE OR REPLACE PACKAGE beachpkg IS
TYPE beachType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE depthType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE array_in(beaches IN beachType, depths IN depthType);
PROCEDURE array_out(beaches OUT beachType, depths OUT depthType);
PROCEDURE array_inout(beaches IN OUT beachType, depths IN OUT depthType);
END;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY beachpkg IS
-- Insert array values into a table
PROCEDURE array_in(beaches IN beachType, depths IN depthType) IS
BEGIN
IF beaches.COUNT <> depths.COUNT THEN
RAISE_APPLICATION_ERROR(-20000, 'Array lengths must match for this example.');
END IF;
FORALL i IN INDICES OF beaches
INSERT INTO waveheight (beach, depth) VALUES (beaches(i), depths(i));
END;
-- Return the values from a table
PROCEDURE array_out(beaches OUT beachType, depths OUT depthType) IS
BEGIN
SELECT beach, depth BULK COLLECT INTO beaches, depths FROM waveheight;
END;
-- Return the arguments sorted
PROCEDURE array_inout(beaches IN OUT beachType, depths IN OUT depthType) IS
BEGIN
IF beaches.COUNT <> depths.COUNT THEN
RAISE_APPLICATION_ERROR(-20001, 'Array lengths must match for this example.');
END IF;
FORALL i IN INDICES OF beaches
INSERT INTO waveheight (beach, depth) VALUES (beaches(i), depths(i));
SELECT beach, depth BULK COLLECT INTO beaches, depths FROM waveheight ORDER BY 1;
END;
END;
/
SHOW ERRORS
-- For selectjson.js example of JSON datatype. Requires Oracle Database 12.1.0.2
BEGIN EXECUTE IMMEDIATE 'DROP TABLE j_purchaseorder'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
-- Note if your applications always insert valid JSON, you may delete
-- the IS JSON check to remove its additional validation overhead.
CREATE TABLE j_purchaseorder (po_document VARCHAR2(4000) CHECK (po_document IS JSON));
-- For selectjsonblob.js example of JSON datatype. Requires Oracle Database 12.1.0.2
BEGIN EXECUTE IMMEDIATE 'DROP TABLE j_purchaseorder_b'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
CREATE TABLE j_purchaseorder_b (po_document BLOB CHECK (po_document IS JSON)) LOB (po_document) STORE AS (CACHE);
-- For DML RETURNING aka RETURNING INTO examples
BEGIN EXECUTE IMMEDIATE 'DROP TABLE dmlrupdtab'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
CREATE TABLE dmlrupdtab (id NUMBER, name VARCHAR2(40));
INSERT INTO dmlrupdtab VALUES (1001, 'Venkat');
INSERT INTO dmlrupdtab VALUES (1002, 'Neeharika');
-- For LOB examples
BEGIN EXECUTE IMMEDIATE 'DROP TABLE mylobs'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
CREATE TABLE mylobs (id NUMBER, c CLOB, b BLOB);
-- For lobbinds.js: Procedure to show IN bind support for LOBs
CREATE OR REPLACE PROCEDURE lobs_in (p_id IN NUMBER, c_in IN CLOB, b_in IN BLOB)
AS
BEGIN
INSERT INTO mylobs (id, c, b) VALUES (p_id, c_in, b_in);
END;
/
SHOW ERRORS
-- For lobbinds.js: Procedure to show bind OUT support for LOBs
CREATE OR REPLACE PROCEDURE lobs_out (p_id IN NUMBER, c_out OUT CLOB, b_out OUT BLOB)
AS
BEGIN
SELECT c, b INTO c_out, b_out FROM mylobs WHERE id = p_id;
END;
/
SHOW ERRORS
-- For lobbinds.js: Procedure to show PL/SQL IN OUT bind support for LOBs
CREATE OR REPLACE PROCEDURE lob_in_out (p_id IN NUMBER, c_inout IN OUT CLOB)
AS
BEGIN
INSERT INTO mylobs (id, c) VALUES (p_id, c_inout);
SELECT 'New LOB: ' || c INTO c_inout FROM mylobs WHERE id = p_id;
END;
/
SHOW ERRORS
-- For DBMS_OUTPUT example dbmsoutputpipe.js
CREATE OR REPLACE TYPE dorow AS TABLE OF VARCHAR2(32767);
/
SHOW ERRORS
CREATE OR REPLACE FUNCTION mydofetch RETURN dorow PIPELINED IS
line VARCHAR2(32767);
status INTEGER;
BEGIN LOOP
DBMS_OUTPUT.GET_LINE(line, status);
EXIT WHEN status = 1;
PIPE ROW (line);
END LOOP;
END;
/
SHOW ERRORS
-- For raw1.js
BEGIN EXECUTE IMMEDIATE 'DROP TABLE myraw'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
CREATE TABLE myraw (r RAW(64));
-- For the executemany*.js examples
BEGIN EXECUTE IMMEDIATE 'DROP TABLE em_tab'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE em_childtab'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE em_parenttab'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
CREATE TABLE em_tab (
id NUMBER NOT NULL,
val VARCHAR2(20)
);
CREATE TABLE em_parenttab (
parentid NUMBER NOT NULL,
description VARCHAR2(60) NOT NULL,
CONSTRAINT parenttab_pk PRIMARY KEY (parentid)
);
CREATE TABLE em_childtab (
childid NUMBER NOT NULL,
parentid NUMBER NOT NULL,
description VARCHAR2(30) NOT NULL,
CONSTRAINT em_childtab_pk PRIMARY KEY (childid),
CONSTRAINT em_childtab_fk FOREIGN KEY (parentid) REFERENCES em_parenttab
);
INSERT INTO em_parenttab VALUES (10, 'Parent 10');
INSERT INTO em_parenttab VALUES (20, 'Parent 20');
INSERT INTO em_parenttab VALUES (30, 'Parent 30');
INSERT INTO em_parenttab VALUES (40, 'Parent 40');
INSERT INTO em_parenttab VALUES (50, 'Parent 50');
INSERT INTO em_childtab VALUES (1001, 10, 'Child 1001 of Parent 10');
INSERT INTO em_childtab VALUES (1002, 20, 'Child 1002 of Parent 20');
INSERT INTO em_childtab VALUES (1003, 20, 'Child 1003 of Parent 20');
INSERT INTO em_childtab VALUES (1004, 20, 'Child 1004 of Parent 20');
INSERT INTO em_childtab VALUES (1005, 30, 'Child 1005 of Parent 30');
INSERT INTO em_childtab VALUES (1006, 30, 'Child 1006 of Parent 30');
INSERT INTO em_childtab VALUES (1007, 40, 'Child 1007 of Parent 40');
INSERT INTO em_childtab VALUES (1008, 40, 'Child 1008 of Parent 40');
INSERT INTO em_childtab VALUES (1009, 40, 'Child 1009 of Parent 40');
INSERT INTO em_childtab VALUES (1010, 40, 'Child 1010 of Parent 40');
INSERT INTO em_childtab VALUES (1011, 40, 'Child 1011 of Parent 40');
INSERT INTO em_childtab VALUES (1012, 50, 'Child 1012 of Parent 50');
INSERT INTO em_childtab VALUES (1013, 50, 'Child 1013 of Parent 50');
INSERT INTO em_childtab VALUES (1014, 50, 'Child 1014 of Parent 50');
INSERT INTO em_childtab VALUES (1015, 50, 'Child 1015 of Parent 50');
CREATE OR REPLACE PROCEDURE em_testproc (
a_num IN NUMBER,
a_outnum OUT NUMBER,
a_outstr OUT VARCHAR2)
AS
BEGIN
a_outnum := a_num * 2;
FOR i IN 1..a_num LOOP
a_outstr := a_outstr || 'X';
END LOOP;
END;
/
COMMIT;
-- For the cqn*.js examples
-- The DBA must grant access:
-- GRANT CHANGE NOTIFICATION TO myuser;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE cqntable'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
/
CREATE TABLE cqntable (k NUMBER);