Debian Codegraph @

mysql-test/suite/compat/oracle/r/func_misc.result

raw
1 SET sql_mode=ORACLE;
2 #
3 # MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM
4 #
5 #
6 # Using SQLCODE and SQLERRM outside of an SP
7 #
8 SELECT SQLCODE;
9 ERROR 42S22: Unknown column 'SQLCODE' in 'SELECT'
10 SELECT SQLERRM;
11 ERROR 42S22: Unknown column 'SQLERRM' in 'SELECT'
12 CREATE TABLE t1 (SQLCODE INT, SQLERRM VARCHAR(10));
13 INSERT INTO t1 VALUES (10, 'test');
14 SELECT SQLCODE, SQLERRM FROM t1;
15 SQLCODE SQLERRM
16 10 test
17 DROP TABLE t1;
18 #
19 # Normal SQLCODE and SQLERRM usage
20 #
21 CREATE PROCEDURE p1(stmt VARCHAR)
22 AS
23 BEGIN
24 EXECUTE IMMEDIATE stmt;
25 SELECT 'Error1: ' || SQLCODE || ' ' || SQLERRM;
26 EXCEPTION
27 WHEN OTHERS THEN
28 SELECT 'Error2: ' || SQLCODE || ' ' || SQLERRM;
29 END;
30 $$
31 CALL p1('SELECT 1');
32 1
33 1
34 'Error1: ' || SQLCODE || ' ' || SQLERRM
35 Error1: 0 normal, successful completion
36 CALL p1('xxx');
37 'Error2: ' || SQLCODE || ' ' || SQLERRM
38 Error2: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
39 CALL p1('SELECT 1');
40 1
41 1
42 'Error1: ' || SQLCODE || ' ' || SQLERRM
43 Error1: 0 normal, successful completion
44 DROP PROCEDURE p1;
45 #
46 # SQLCODE and SQLERRM hidden by local variables
47 #
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 CALL p1;
57 'Error: ' || SQLCODE || ' ' || SQLERRM
58 Error: 10 test
59 DROP PROCEDURE p1;
60 CREATE PROCEDURE p1()
61 AS
62 sqlcode INT;
63 sqlerrm VARCHAR(64);
64 BEGIN
65 SQLCODE:= 10;
66 sqlerrm:= 'test';
67 SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM;
68 END;
69 $$
70 CALL p1;
71 'Error: ' || SQLCODE || ' ' || SQLERRM
72 Error: 10 test
73 DROP PROCEDURE p1;
74 #
75 # SQLCODE and SQLERRM hidden by parameters
76 #
77 CREATE PROCEDURE p1(sqlcode INT, sqlerrm VARCHAR)
78 AS
79 BEGIN
80 SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM;
81 END;
82 $$
83 CALL p1(10, 'test');
84 'Error: ' || SQLCODE || ' ' || SQLERRM
85 Error: 10 test
86 DROP PROCEDURE p1;
87 #
88 # SQLCODE and SQLERRM in CREATE..SELECT
89 #
90 CREATE PROCEDURE p1
91 AS
92 BEGIN
93 CREATE TABLE t1 AS SELECT SQLCODE, SQLERRM;
94 END;
95 $$
96 CALL p1;
97 SHOW CREATE TABLE t1;
98 Table Create Table
99 t1 CREATE TABLE "t1" (
100 "SQLCODE" int(11) NOT NULL,
101 "SQLERRM" varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL
102 )
103 DROP TABLE t1;
104 DROP PROCEDURE p1;
105 #
106 # SQLCODE and SQLERRM in EXPLAIN EXTENDED SELECT
107 #
108 CREATE PROCEDURE p1
109 AS
110 BEGIN
111 EXPLAIN EXTENDED SELECT SQLCode, SQLErrm;
112 END;
113 $$
114 CALL p1;
115 id select_type table type possible_keys key key_len ref rows filtered Extra
116 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
117 Warnings:
118 Note 1003 select SQLCODE AS "SQLCode",SQLERRM AS "SQLErrm"
119 DROP PROCEDURE p1;
120 #
121 # Warning-alike errors in stored functions
122 #
123 CREATE TABLE t1 (a INT);
124 CREATE FUNCTION f1 RETURN VARCHAR
125 AS
126 a INT;
127 BEGIN
128 SELECT a INTO a FROM t1;
129 RETURN 'No exception ' || SQLCODE || ' ' || SQLERRM;
130 EXCEPTION
131 WHEN NO_DATA_FOUND THEN
132 RETURN 'Exception ' || SQLCODE || ' ' || SQLERRM;
133 END;
134 $$
135 SELECT f1() FROM DUAL;
136 f1()
137 Exception 1329 No data - zero rows fetched, selected, or processed
138 DROP FUNCTION f1;
139 DROP TABLE t1;
140 CREATE TABLE t1 (a INT);
141 CREATE FUNCTION f1 RETURN VARCHAR
142 AS
143 a INT;
144 BEGIN
145 SELECT a INTO a FROM t1;
146 RETURN 'No exception ' || SQLCODE || ' ' || SQLERRM;
147 EXCEPTION
148 WHEN OTHERS THEN
149 RETURN 'Exception ' || SQLCODE || ' ' || SQLERRM;
150 END;
151 $$
152 SELECT f1() FROM DUAL;
153 f1()
154 Exception 1329 No data - zero rows fetched, selected, or processed
155 DROP FUNCTION f1;
156 DROP TABLE t1;
157 #
158 # Warning-alike errors in stored procedures
159 #
160 CREATE TABLE t1 (a INT);
161 CREATE PROCEDURE p1(res OUT VARCHAR)
162 AS
163 a INT;
164 BEGIN
165 SELECT a INTO a FROM t1;
166 res:= 'No exception ' || SQLCODE || ' ' || SQLERRM;
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 res:= 'Exception ' || SQLCODE || ' ' || SQLERRM;
170 END;
171 $$
172 CALL p1(@a);
173 SELECT @a;
174 @a
175 Exception 1329 No data - zero rows fetched, selected, or processed
176 DROP PROCEDURE p1;
177 DROP TABLE t1;
178 CREATE TABLE t1 (a INT);
179 CREATE PROCEDURE p1(res OUT VARCHAR)
180 AS
181 a INT;
182 BEGIN
183 SELECT a INTO a FROM t1;
184 res:= 'No exception ' || SQLCODE || ' ' || SQLERRM;
185 EXCEPTION
186 WHEN OTHERS THEN
187 res:= 'Exception ' || SQLCODE || ' ' || SQLERRM;
188 END;
189 $$
190 CALL p1(@a);
191 SELECT @a;
192 @a
193 Exception 1329 No data - zero rows fetched, selected, or processed
194 DROP PROCEDURE p1;
195 DROP TABLE t1;
196 #
197 # SQLCODE and SQLERRM are cleared on RETURN
198 #
199 CREATE TABLE t1 (a INT);
200 CREATE FUNCTION f1 RETURN VARCHAR
201 AS
202 a INT:=10;
203 BEGIN
204 SELECT a INTO a FROM t1;
205 RETURN 'Value=' || a;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN RETURN 'Exception|' || SQLCODE || ' ' || SQLERRM;
208 END;
209 $$
210 CREATE FUNCTION f2 RETURN VARCHAR
211 AS
212 a VARCHAR(128);
213 BEGIN
214 RETURN f1() || '|' || SQLCODE || ' ' || SQLERRM;
215 END;
216 $$
217 SELECT f1() FROM DUAL;
218 f1()
219 Exception|1329 No data - zero rows fetched, selected, or processed
220 SELECT f2() FROM DUAL;
221 f2()
222 Exception|1329 No data - zero rows fetched, selected, or processed|0 normal, successful completion
223 DROP TABLE t1;
224 DROP FUNCTION f2;
225 DROP FUNCTION f1;
226 CREATE TABLE t1 (a INT);
227 CREATE FUNCTION f1 RETURN VARCHAR
228 AS
229 a INT:=10;
230 BEGIN
231 SELECT a INTO a FROM t1;
232 RETURN 'Value=' || a;
233 EXCEPTION
234 WHEN OTHERS THEN RETURN 'Exception|' || SQLCODE || ' ' || SQLERRM;
235 END;
236 $$
237 CREATE FUNCTION f2 RETURN VARCHAR
238 AS
239 a VARCHAR(128);
240 BEGIN
241 RETURN f1() || '|' || SQLCODE || ' ' || SQLERRM;
242 END;
243 $$
244 SELECT f1() FROM DUAL;
245 f1()
246 Exception|1329 No data - zero rows fetched, selected, or processed
247 SELECT f2() FROM DUAL;
248 f2()
249 Exception|1329 No data - zero rows fetched, selected, or processed|0 normal, successful completion
250 DROP TABLE t1;
251 DROP FUNCTION f2;
252 DROP FUNCTION f1;
253 #
254 # SQLCODE and SQLERRM are cleared on a return from a PROCEDURE
255 #
256 CREATE TABLE t1 (a INT);
257 CREATE PROCEDURE p1(res OUT VARCHAR)
258 AS
259 a INT:=10;
260 BEGIN
261 SELECT a INTO a FROM t1;
262 res:='Value=' || a;
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN res:='Exception|' || SQLCODE || ' ' || SQLERRM;
265 END;
266 $$
267 CREATE FUNCTION f2 RETURN VARCHAR
268 AS
269 res VARCHAR(128);
270 BEGIN
271 CALL p1(res);
272 RETURN res || '|' || SQLCODE || ' ' || SQLERRM;
273 END;
274 $$
275 SELECT f2() FROM DUAL;
276 f2()
277 Exception|1329 No data - zero rows fetched, selected, or processed|0 normal, successful completion
278 DROP FUNCTION f2;
279 DROP PROCEDURE p1;
280 DROP TABLE t1;
281 CREATE TABLE t1 (a INT);
282 CREATE PROCEDURE p1(res OUT VARCHAR)
283 AS
284 a INT:=10;
285 BEGIN
286 SELECT a INTO a FROM t1;
287 res:='Value=' || a;
288 EXCEPTION
289 WHEN OTHERS THEN res:='Exception|' || SQLCODE || ' ' || SQLERRM;
290 END;
291 $$
292 CREATE FUNCTION f2 RETURN VARCHAR
293 AS
294 res VARCHAR(128);
295 BEGIN
296 CALL p1(res);
297 RETURN res || '|' || SQLCODE || ' ' || SQLERRM;
298 END;
299 $$
300 SELECT f2() FROM DUAL;
301 f2()
302 Exception|1329 No data - zero rows fetched, selected, or processed|0 normal, successful completion
303 DROP FUNCTION f2;
304 DROP PROCEDURE p1;
305 DROP TABLE t1;
306 #
307 # End of MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM
308 #
309 #
310 # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
311 #
312 BEGIN
313 SELECT SQLCODE;
314 END
315 $$
316 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
317 def SQLCODE 3 11 1 N 32897 0 63
318 SQLCODE
319 0