Debian Codegraph @

mysql-test/suite/compat/oracle/t/func_misc.test

raw
1 SET sql_mode=ORACLE;
2
3 --echo #
4 --echo # MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM
5 --echo #
6
7 --echo #
8 --echo # Using SQLCODE and SQLERRM outside of an SP
9 --echo #
10
11 --error ER_BAD_FIELD_ERROR
12 SELECT SQLCODE;
13
14 --error ER_BAD_FIELD_ERROR
15 SELECT SQLERRM;
16
17 CREATE TABLE t1 (SQLCODE INT, SQLERRM VARCHAR(10));
18 INSERT INTO t1 VALUES (10, 'test');
19 SELECT SQLCODE, SQLERRM FROM t1;
20 DROP TABLE t1;
21
22 --echo #
23 --echo # Normal SQLCODE and SQLERRM usage
24 --echo #
25
26 DELIMITER $$;
27 CREATE PROCEDURE p1(stmt VARCHAR)
28 AS
29 BEGIN
30 EXECUTE IMMEDIATE stmt;
31 SELECT 'Error1: ' || SQLCODE || ' ' || SQLERRM;
32 EXCEPTION
33 WHEN OTHERS THEN
34 SELECT 'Error2: ' || SQLCODE || ' ' || SQLERRM;
35 END;
36 $$
37 DELIMITER ;$$
38 CALL p1('SELECT 1');
39 CALL p1('xxx');
40 CALL p1('SELECT 1');
41 DROP PROCEDURE p1;
42
43 --echo #
44 --echo # SQLCODE and SQLERRM hidden by local variables
45 --echo #
46
47 DELIMITER $$;
48 CREATE PROCEDURE p1()
49 AS
50 sqlcode INT:= 10;
51 sqlerrm VARCHAR(64) := 'test';
52 BEGIN
53 SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM;
54 END;
55 $$
56 DELIMITER ;$$
57 CALL p1;
58 DROP PROCEDURE p1;
59
60 DELIMITER $$;
61 CREATE PROCEDURE p1()
62 AS
63 sqlcode INT;
64 sqlerrm VARCHAR(64);
65 BEGIN
66 SQLCODE:= 10;
67 sqlerrm:= 'test';
68 SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM;
69 END;
70 $$
71 DELIMITER ;$$
72 CALL p1;
73 DROP PROCEDURE p1;
74
75
76 --echo #
77 --echo # SQLCODE and SQLERRM hidden by parameters
78 --echo #
79
80 DELIMITER $$;
81 CREATE PROCEDURE p1(sqlcode INT, sqlerrm VARCHAR)
82 AS
83 BEGIN
84 SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM;
85 END;
86 $$
87 DELIMITER ;$$
88 CALL p1(10, 'test');
89 DROP PROCEDURE p1;
90
91
92 --echo #
93 --echo # SQLCODE and SQLERRM in CREATE..SELECT
94 --echo #
95
96 DELIMITER $$;
97 CREATE PROCEDURE p1
98 AS
99 BEGIN
100 CREATE TABLE t1 AS SELECT SQLCODE, SQLERRM;
101 END;
102 $$
103 DELIMITER ;$$
104 CALL p1;
105 SHOW CREATE TABLE t1;
106 DROP TABLE t1;
107 DROP PROCEDURE p1;
108
109
110 --echo #
111 --echo # SQLCODE and SQLERRM in EXPLAIN EXTENDED SELECT
112 --echo #
113
114 DELIMITER $$;
115 CREATE PROCEDURE p1
116 AS
117 BEGIN
118 EXPLAIN EXTENDED SELECT SQLCode, SQLErrm;
119 END;
120 $$
121 DELIMITER ;$$
122 CALL p1;
123 DROP PROCEDURE p1;
124
125
126
127 --echo #
128 --echo # Warning-alike errors in stored functions
129 --echo #
130
131 CREATE TABLE t1 (a INT);
132 DELIMITER $$;
133 CREATE FUNCTION f1 RETURN VARCHAR
134 AS
135 a INT;
136 BEGIN
137 SELECT a INTO a FROM t1;
138 RETURN 'No exception ' || SQLCODE || ' ' || SQLERRM;
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 RETURN 'Exception ' || SQLCODE || ' ' || SQLERRM;
142 END;
143 $$
144 DELIMITER ;$$
145 SELECT f1() FROM DUAL;
146 DROP FUNCTION f1;
147 DROP TABLE t1;
148
149
150 CREATE TABLE t1 (a INT);
151 DELIMITER $$;
152 CREATE FUNCTION f1 RETURN VARCHAR
153 AS
154 a INT;
155 BEGIN
156 SELECT a INTO a FROM t1;
157 RETURN 'No exception ' || SQLCODE || ' ' || SQLERRM;
158 EXCEPTION
159 WHEN OTHERS THEN
160 RETURN 'Exception ' || SQLCODE || ' ' || SQLERRM;
161 END;
162 $$
163 DELIMITER ;$$
164 SELECT f1() FROM DUAL;
165 DROP FUNCTION f1;
166 DROP TABLE t1;
167
168
169 --echo #
170 --echo # Warning-alike errors in stored procedures
171 --echo #
172
173 CREATE TABLE t1 (a INT);
174 DELIMITER $$;
175 CREATE PROCEDURE p1(res OUT VARCHAR)
176 AS
177 a INT;
178 BEGIN
179 SELECT a INTO a FROM t1;
180 res:= 'No exception ' || SQLCODE || ' ' || SQLERRM;
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 res:= 'Exception ' || SQLCODE || ' ' || SQLERRM;
184 END;
185 $$
186 DELIMITER ;$$
187 CALL p1(@a);
188 SELECT @a;
189 DROP PROCEDURE p1;
190 DROP TABLE t1;
191
192
193 CREATE TABLE t1 (a INT);
194 DELIMITER $$;
195 CREATE PROCEDURE p1(res OUT VARCHAR)
196 AS
197 a INT;
198 BEGIN
199 SELECT a INTO a FROM t1;
200 res:= 'No exception ' || SQLCODE || ' ' || SQLERRM;
201 EXCEPTION
202 WHEN OTHERS THEN
203 res:= 'Exception ' || SQLCODE || ' ' || SQLERRM;
204 END;
205 $$
206 DELIMITER ;$$
207 CALL p1(@a);
208 SELECT @a;
209 DROP PROCEDURE p1;
210 DROP TABLE t1;
211
212
213 --echo #
214 --echo # SQLCODE and SQLERRM are cleared on RETURN
215 --echo #
216
217 CREATE TABLE t1 (a INT);
218 DELIMITER $$;
219 CREATE FUNCTION f1 RETURN VARCHAR
220 AS
221 a INT:=10;
222 BEGIN
223 SELECT a INTO a FROM t1;
224 RETURN 'Value=' || a;
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN RETURN 'Exception|' || SQLCODE || ' ' || SQLERRM;
227 END;
228 $$
229 CREATE FUNCTION f2 RETURN VARCHAR
230 AS
231 a VARCHAR(128);
232 BEGIN
233 RETURN f1() || '|' || SQLCODE || ' ' || SQLERRM;
234 END;
235 $$
236 DELIMITER ;$$
237 SELECT f1() FROM DUAL;
238 SELECT f2() FROM DUAL;
239 DROP TABLE t1;
240 DROP FUNCTION f2;
241 DROP FUNCTION f1;
242
243
244 CREATE TABLE t1 (a INT);
245 DELIMITER $$;
246 CREATE FUNCTION f1 RETURN VARCHAR
247 AS
248 a INT:=10;
249 BEGIN
250 SELECT a INTO a FROM t1;
251 RETURN 'Value=' || a;
252 EXCEPTION
253 WHEN OTHERS THEN RETURN 'Exception|' || SQLCODE || ' ' || SQLERRM;
254 END;
255 $$
256 CREATE FUNCTION f2 RETURN VARCHAR
257 AS
258 a VARCHAR(128);
259 BEGIN
260 RETURN f1() || '|' || SQLCODE || ' ' || SQLERRM;
261 END;
262 $$
263 DELIMITER ;$$
264 SELECT f1() FROM DUAL;
265 SELECT f2() FROM DUAL;
266 DROP TABLE t1;
267 DROP FUNCTION f2;
268 DROP FUNCTION f1;
269
270
271 --echo #
272 --echo # SQLCODE and SQLERRM are cleared on a return from a PROCEDURE
273 --echo #
274
275 CREATE TABLE t1 (a INT);
276 DELIMITER $$;
277 CREATE PROCEDURE p1(res OUT VARCHAR)
278 AS
279 a INT:=10;
280 BEGIN
281 SELECT a INTO a FROM t1;
282 res:='Value=' || a;
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN res:='Exception|' || SQLCODE || ' ' || SQLERRM;
285 END;
286 $$
287 CREATE FUNCTION f2 RETURN VARCHAR
288 AS
289 res VARCHAR(128);
290 BEGIN
291 CALL p1(res);
292 RETURN res || '|' || SQLCODE || ' ' || SQLERRM;
293 END;
294 $$
295 DELIMITER ;$$
296 SELECT f2() FROM DUAL;
297 DROP FUNCTION f2;
298 DROP PROCEDURE p1;
299 DROP TABLE t1;
300
301
302 CREATE TABLE t1 (a INT);
303 DELIMITER $$;
304 CREATE PROCEDURE p1(res OUT VARCHAR)
305 AS
306 a INT:=10;
307 BEGIN
308 SELECT a INTO a FROM t1;
309 res:='Value=' || a;
310 EXCEPTION
311 WHEN OTHERS THEN res:='Exception|' || SQLCODE || ' ' || SQLERRM;
312 END;
313 $$
314 CREATE FUNCTION f2 RETURN VARCHAR
315 AS
316 res VARCHAR(128);
317 BEGIN
318 CALL p1(res);
319 RETURN res || '|' || SQLCODE || ' ' || SQLERRM;
320 END;
321 $$
322 DELIMITER ;$$
323 SELECT f2() FROM DUAL;
324 DROP FUNCTION f2;
325 DROP PROCEDURE p1;
326 DROP TABLE t1;
327
328
329 --echo #
330 --echo # End of MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM
331 --echo #
332
333 --echo #
334 --echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
335 --echo #
336
337 --enable_metadata
338 --disable_ps_protocol
339 DELIMITER $$;
340 BEGIN
341 SELECT SQLCODE;
342 END
343 $$
344 DELIMITER ;$$
345 --enable_ps_protocol
346 --disable_metadata