Skip to content

Commit 1622236

Browse files
rophyrophy
committed
fix: add Oracle-compatible validation for PRAGMA EXCEPTION_INIT error codes
Validates error codes at compile time per Oracle documentation: - Accept 100 or negative integers >= -1000000 (except -1403 and 0) - Reject with PLS-00701 for invalid codes
1 parent e8c79ad commit 1622236

4 files changed

Lines changed: 285 additions & 12 deletions

File tree

‎src/pl/plisql/src/expected/plisql_exception.out‎

Lines changed: 128 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,7 @@ END test_pragma_init;
116116
/
117117
CREATE OR REPLACE PACKAGE BODY test_pragma_init IS
118118
my_exception EXCEPTION;
119-
PRAGMA EXCEPTION_INIT(my_exception, 20001);
119+
PRAGMA EXCEPTION_INIT(my_exception, -20001);
120120
PROCEDURE test_basic_pragma IS
121121
BEGIN
122122
RAISE INFO 'PRAGMA EXCEPTION_INIT compiled successfully';
@@ -144,7 +144,7 @@ END test_pragma_raise;
144144
/
145145
CREATE OR REPLACE PACKAGE BODY test_pragma_raise IS
146146
custom_exc EXCEPTION;
147-
PRAGMA EXCEPTION_INIT(custom_exc, 20002);
147+
PRAGMA EXCEPTION_INIT(custom_exc, -20002);
148148
PROCEDURE test_pragma_exception IS
149149
BEGIN
150150
RAISE custom_exc;
@@ -165,7 +165,7 @@ INFO: Caught exception with PRAGMA EXCEPTION_INIT
165165
--
166166
CREATE OR REPLACE PROCEDURE test_pragma_proc IS
167167
my_exc EXCEPTION;
168-
PRAGMA EXCEPTION_INIT(my_exc, 20003);
168+
PRAGMA EXCEPTION_INIT(my_exc, -20003);
169169
BEGIN
170170
RAISE INFO 'PRAGMA EXCEPTION_INIT in procedure works';
171171
RAISE my_exc;
@@ -196,11 +196,11 @@ END test_multi_pragma;
196196
/
197197
CREATE OR REPLACE PACKAGE BODY test_multi_pragma IS
198198
exc1 EXCEPTION;
199-
PRAGMA EXCEPTION_INIT(exc1, 20011);
199+
PRAGMA EXCEPTION_INIT(exc1, -20011);
200200
exc2 EXCEPTION;
201-
PRAGMA EXCEPTION_INIT(exc2, 20012);
201+
PRAGMA EXCEPTION_INIT(exc2, -20012);
202202
exc3 EXCEPTION;
203-
PRAGMA EXCEPTION_INIT(exc3, 20013);
203+
PRAGMA EXCEPTION_INIT(exc3, -20013);
204204
PROCEDURE test_multiple IS
205205
BEGIN
206206
RAISE INFO 'Multiple PRAGMA EXCEPTION_INIT declarations work';
@@ -218,6 +218,128 @@ SELECT 'Multiple PRAGMA EXCEPTION_INIT test passed' AS result;
218218
Multiple PRAGMA EXCEPTION_INIT test passed
219219
(1 row)
220220

221+
--
222+
-- Test 10: PRAGMA EXCEPTION_INIT with positive error codes
223+
-- Oracle documentation: Valid codes are 100 or any negative integer >= -1000000 (except -1403)
224+
-- This test verifies positive error codes: only 100 is accepted, all others are rejected.
225+
--
226+
-- Test 10a: Valid positive error code 100 (should succeed)
227+
CREATE OR REPLACE PACKAGE test_pragma_positive_100 IS
228+
PROCEDURE test_positive;
229+
END test_pragma_positive_100;
230+
/
231+
CREATE OR REPLACE PACKAGE BODY test_pragma_positive_100 IS
232+
exc_100 EXCEPTION;
233+
PRAGMA EXCEPTION_INIT(exc_100, 100); -- Oracle accepts: ANSI NO_DATA_FOUND
234+
PROCEDURE test_positive IS
235+
BEGIN
236+
RAISE INFO 'Error code 100 accepted (ANSI NO_DATA_FOUND)';
237+
END test_positive;
238+
END test_pragma_positive_100;
239+
/
240+
BEGIN
241+
test_pragma_positive_100.test_positive();
242+
END;
243+
/
244+
INFO: Error code 100 accepted (ANSI NO_DATA_FOUND)
245+
DROP PACKAGE test_pragma_positive_100;
246+
-- Test 10b: Invalid positive error code 1 (should fail with PLS-00701)
247+
CREATE OR REPLACE PACKAGE test_pragma_positive_1 IS
248+
exc_1 EXCEPTION;
249+
PRAGMA EXCEPTION_INIT(exc_1, 1); -- Oracle rejects: positive except 100
250+
END test_pragma_positive_1;
251+
/
252+
ERROR: illegal ORACLE error number 1 for PRAGMA EXCEPTION_INIT
253+
LINE 2: PRAGMA EXCEPTION_INIT(exc_1, 1); -- Oracle rejects: posit...
254+
^
255+
QUERY: exc_1 EXCEPTION;
256+
PRAGMA EXCEPTION_INIT(exc_1, 1); -- Oracle rejects: positive except 100
257+
END test_pragma_positive_1
258+
-- Test 10c: Invalid positive error code 1000000 (should fail with PLS-00701)
259+
CREATE OR REPLACE PACKAGE test_pragma_positive_1000000 IS
260+
exc_1000000 EXCEPTION;
261+
PRAGMA EXCEPTION_INIT(exc_1000000, 1000000); -- Oracle rejects: positive except 100
262+
END test_pragma_positive_1000000;
263+
/
264+
ERROR: illegal ORACLE error number 1000000 for PRAGMA EXCEPTION_INIT
265+
LINE 2: PRAGMA EXCEPTION_INIT(exc_1000000, 1000000); -- Oracle re...
266+
^
267+
QUERY: exc_1000000 EXCEPTION;
268+
PRAGMA EXCEPTION_INIT(exc_1000000, 1000000); -- Oracle rejects: positive except 100
269+
END test_pragma_positive_1000000
270+
SELECT 'PRAGMA EXCEPTION_INIT positive error code tests completed' AS result;
271+
result
272+
-----------------------------------------------------------
273+
PRAGMA EXCEPTION_INIT positive error code tests completed
274+
(1 row)
275+
276+
--
277+
-- Test 11: PRAGMA EXCEPTION_INIT with negative error codes and boundary
278+
-- Oracle documentation: Valid codes are 100 or any negative integer >= -1000000 (except -1403)
279+
-- This test verifies the -1000000 boundary and specifically rejected negative codes.
280+
--
281+
-- Test 11a: Valid error code -1000000 (should succeed - at boundary)
282+
CREATE OR REPLACE PACKAGE test_pragma_minus_1000000 IS
283+
PROCEDURE test_boundary;
284+
END test_pragma_minus_1000000;
285+
/
286+
CREATE OR REPLACE PACKAGE BODY test_pragma_minus_1000000 IS
287+
exc_boundary EXCEPTION;
288+
PRAGMA EXCEPTION_INIT(exc_boundary, -1000000); -- At the boundary
289+
PROCEDURE test_boundary IS
290+
BEGIN
291+
RAISE INFO 'Error code -1000000 accepted (at boundary)';
292+
END test_boundary;
293+
END test_pragma_minus_1000000;
294+
/
295+
BEGIN
296+
test_pragma_minus_1000000.test_boundary();
297+
END;
298+
/
299+
INFO: Error code -1000000 accepted (at boundary)
300+
DROP PACKAGE test_pragma_minus_1000000;
301+
-- Test 11b: Invalid error code -1000001 (should fail with PLS-00701 - beyond boundary)
302+
CREATE OR REPLACE PACKAGE test_pragma_minus_1000001 IS
303+
exc_beyond EXCEPTION;
304+
PRAGMA EXCEPTION_INIT(exc_beyond, -1000001); -- Beyond boundary
305+
END test_pragma_minus_1000001;
306+
/
307+
ERROR: illegal ORACLE error number -1000001 for PRAGMA EXCEPTION_INIT
308+
LINE 2: PRAGMA EXCEPTION_INIT(exc_beyond, -1000001); -- Beyond bo...
309+
^
310+
QUERY: exc_beyond EXCEPTION;
311+
PRAGMA EXCEPTION_INIT(exc_beyond, -1000001); -- Beyond boundary
312+
END test_pragma_minus_1000001
313+
-- Test 11c: Invalid error code -1403 (should fail with PLS-00701)
314+
CREATE OR REPLACE PACKAGE test_pragma_minus_1403 IS
315+
exc_1403 EXCEPTION;
316+
PRAGMA EXCEPTION_INIT(exc_1403, -1403); -- Oracle-specific NO_DATA_FOUND, must use 100
317+
END test_pragma_minus_1403;
318+
/
319+
ERROR: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT
320+
LINE 2: PRAGMA EXCEPTION_INIT(exc_1403, -1403); -- Oracle-specifi...
321+
^
322+
QUERY: exc_1403 EXCEPTION;
323+
PRAGMA EXCEPTION_INIT(exc_1403, -1403); -- Oracle-specific NO_DATA_FOUND, must use 100
324+
END test_pragma_minus_1403
325+
-- Test 11d: Invalid error code 0 (should fail with PLS-00701)
326+
CREATE OR REPLACE PACKAGE test_pragma_zero IS
327+
exc_zero EXCEPTION;
328+
PRAGMA EXCEPTION_INIT(exc_zero, 0); -- Zero is rejected by Oracle
329+
END test_pragma_zero;
330+
/
331+
ERROR: illegal ORACLE error number 0 for PRAGMA EXCEPTION_INIT
332+
LINE 2: PRAGMA EXCEPTION_INIT(exc_zero, 0); -- Zero is rejected b...
333+
^
334+
QUERY: exc_zero EXCEPTION;
335+
PRAGMA EXCEPTION_INIT(exc_zero, 0); -- Zero is rejected by Oracle
336+
END test_pragma_zero
337+
SELECT 'PRAGMA EXCEPTION_INIT negative error code tests completed' AS result;
338+
result
339+
-----------------------------------------------------------
340+
PRAGMA EXCEPTION_INIT negative error code tests completed
341+
(1 row)
342+
221343
-- Cleanup
222344
DROP PACKAGE test_pragma_init;
223345
DROP PACKAGE test_pragma_raise;

‎src/pl/plisql/src/pl_exception_type.c‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,45 @@ plisql_exception_set_sqlcode(PLiSQL_exception_var *exc, int sqlcode)
111111
exc->sqlcode = sqlcode;
112112
}
113113

114+
/*
115+
* plisql_validate_exception_error_code
116+
* Validate error code for PRAGMA EXCEPTION_INIT (Oracle compatibility)
117+
*
118+
* Oracle documentation states valid error codes are:
119+
* - Error code 100 (ANSI NO_DATA_FOUND)
120+
* - Any negative integer >= -1000000, except -1403
121+
*
122+
* Oracle rejects at compile time with PLS-00701:
123+
* - Error code 0 (zero is not a valid error code)
124+
* - Error code -1403 (Oracle-specific NO_DATA_FOUND; must use 100 instead)
125+
* - All positive error codes EXCEPT 100
126+
* - Error codes less than -1000000
127+
*
128+
* Returns true if valid, false if invalid.
129+
*/
130+
static bool
131+
plisql_validate_exception_error_code(int sqlcode)
132+
{
133+
/* Reject zero */
134+
if (sqlcode == 0)
135+
return false;
136+
137+
/* Reject -1403 (Oracle-specific NO_DATA_FOUND) */
138+
if (sqlcode == -1403)
139+
return false;
140+
141+
/* For positive codes, only 100 (ANSI NO_DATA_FOUND) is allowed */
142+
if (sqlcode > 0 && sqlcode != 100)
143+
return false;
144+
145+
/* For negative codes, must be >= -1000000 */
146+
if (sqlcode < -1000000)
147+
return false;
148+
149+
/* All other codes are valid */
150+
return true;
151+
}
152+
114153
/*
115154
* plisql_process_pragma_exception_init
116155
* Process PRAGMA EXCEPTION_INIT directive at compile time
@@ -143,6 +182,13 @@ plisql_process_pragma_exception_init(const char *exc_name, int sqlcode,
143182
errmsg("\"%s\" is not an exception", exc_name),
144183
plisql_scanner_errposition(location, yyscanner)));
145184

185+
/* Validate error code (Oracle compatibility) */
186+
if (!plisql_validate_exception_error_code(sqlcode))
187+
ereport(ERROR,
188+
(errcode(ERRCODE_SYNTAX_ERROR),
189+
errmsg("illegal ORACLE error number %d for PRAGMA EXCEPTION_INIT", sqlcode),
190+
plisql_scanner_errposition(location, yyscanner)));
191+
146192
/* Set the exception's sqlcode */
147193
exc = (PLiSQL_exception_var *) plisql_Datums[nse->itemno];
148194
plisql_exception_set_sqlcode(exc, sqlcode);

‎src/pl/plisql/src/pl_gram.y‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -831,6 +831,12 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
831831
$6,
832832
@1, yyscanner);
833833
}
834+
| K_PRAGMA K_EXCEPTION_INIT '(' any_identifier ',' '-' ICONST ')' ';'
835+
{
836+
plisql_process_pragma_exception_init($4,
837+
-$7,
838+
@1, yyscanner);
839+
}
834840
/* function or procedure declare or define */
835841
| function_heading function_properties ';'
836842
{

‎src/pl/plisql/src/sql/plisql_exception.sql‎

Lines changed: 105 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,7 @@ END test_pragma_init;
116116

117117
CREATE OR REPLACE PACKAGE BODY test_pragma_init IS
118118
my_exception EXCEPTION;
119-
PRAGMA EXCEPTION_INIT(my_exception, 20001);
119+
PRAGMA EXCEPTION_INIT(my_exception, -20001);
120120

121121
PROCEDURE test_basic_pragma IS
122122
BEGIN
@@ -143,7 +143,7 @@ END test_pragma_raise;
143143

144144
CREATE OR REPLACE PACKAGE BODY test_pragma_raise IS
145145
custom_exc EXCEPTION;
146-
PRAGMA EXCEPTION_INIT(custom_exc, 20002);
146+
PRAGMA EXCEPTION_INIT(custom_exc, -20002);
147147

148148
PROCEDURE test_pragma_exception IS
149149
BEGIN
@@ -166,7 +166,7 @@ END;
166166
--
167167
CREATE OR REPLACE PROCEDURE test_pragma_proc IS
168168
my_exc EXCEPTION;
169-
PRAGMA EXCEPTION_INIT(my_exc, 20003);
169+
PRAGMA EXCEPTION_INIT(my_exc, -20003);
170170
BEGIN
171171
RAISE INFO 'PRAGMA EXCEPTION_INIT in procedure works';
172172
RAISE my_exc;
@@ -194,13 +194,13 @@ END test_multi_pragma;
194194

195195
CREATE OR REPLACE PACKAGE BODY test_multi_pragma IS
196196
exc1 EXCEPTION;
197-
PRAGMA EXCEPTION_INIT(exc1, 20011);
197+
PRAGMA EXCEPTION_INIT(exc1, -20011);
198198

199199
exc2 EXCEPTION;
200-
PRAGMA EXCEPTION_INIT(exc2, 20012);
200+
PRAGMA EXCEPTION_INIT(exc2, -20012);
201201

202202
exc3 EXCEPTION;
203-
PRAGMA EXCEPTION_INIT(exc3, 20013);
203+
PRAGMA EXCEPTION_INIT(exc3, -20013);
204204

205205
PROCEDURE test_multiple IS
206206
BEGIN
@@ -216,6 +216,105 @@ END;
216216

217217
SELECT 'Multiple PRAGMA EXCEPTION_INIT test passed' AS result;
218218

219+
--
220+
-- Test 10: PRAGMA EXCEPTION_INIT with positive error codes
221+
-- Oracle documentation: Valid codes are 100 or any negative integer >= -1000000 (except -1403)
222+
-- This test verifies positive error codes: only 100 is accepted, all others are rejected.
223+
--
224+
225+
-- Test 10a: Valid positive error code 100 (should succeed)
226+
CREATE OR REPLACE PACKAGE test_pragma_positive_100 IS
227+
PROCEDURE test_positive;
228+
END test_pragma_positive_100;
229+
/
230+
231+
CREATE OR REPLACE PACKAGE BODY test_pragma_positive_100 IS
232+
exc_100 EXCEPTION;
233+
PRAGMA EXCEPTION_INIT(exc_100, 100); -- Oracle accepts: ANSI NO_DATA_FOUND
234+
235+
PROCEDURE test_positive IS
236+
BEGIN
237+
RAISE INFO 'Error code 100 accepted (ANSI NO_DATA_FOUND)';
238+
END test_positive;
239+
END test_pragma_positive_100;
240+
/
241+
242+
BEGIN
243+
test_pragma_positive_100.test_positive();
244+
END;
245+
/
246+
247+
DROP PACKAGE test_pragma_positive_100;
248+
249+
-- Test 10b: Invalid positive error code 1 (should fail with PLS-00701)
250+
CREATE OR REPLACE PACKAGE test_pragma_positive_1 IS
251+
exc_1 EXCEPTION;
252+
PRAGMA EXCEPTION_INIT(exc_1, 1); -- Oracle rejects: positive except 100
253+
END test_pragma_positive_1;
254+
/
255+
256+
-- Test 10c: Invalid positive error code 1000000 (should fail with PLS-00701)
257+
CREATE OR REPLACE PACKAGE test_pragma_positive_1000000 IS
258+
exc_1000000 EXCEPTION;
259+
PRAGMA EXCEPTION_INIT(exc_1000000, 1000000); -- Oracle rejects: positive except 100
260+
END test_pragma_positive_1000000;
261+
/
262+
263+
SELECT 'PRAGMA EXCEPTION_INIT positive error code tests completed' AS result;
264+
265+
--
266+
-- Test 11: PRAGMA EXCEPTION_INIT with negative error codes and boundary
267+
-- Oracle documentation: Valid codes are 100 or any negative integer >= -1000000 (except -1403)
268+
-- This test verifies the -1000000 boundary and specifically rejected negative codes.
269+
--
270+
271+
-- Test 11a: Valid error code -1000000 (should succeed - at boundary)
272+
CREATE OR REPLACE PACKAGE test_pragma_minus_1000000 IS
273+
PROCEDURE test_boundary;
274+
END test_pragma_minus_1000000;
275+
/
276+
277+
CREATE OR REPLACE PACKAGE BODY test_pragma_minus_1000000 IS
278+
exc_boundary EXCEPTION;
279+
PRAGMA EXCEPTION_INIT(exc_boundary, -1000000); -- At the boundary
280+
281+
PROCEDURE test_boundary IS
282+
BEGIN
283+
RAISE INFO 'Error code -1000000 accepted (at boundary)';
284+
END test_boundary;
285+
END test_pragma_minus_1000000;
286+
/
287+
288+
BEGIN
289+
test_pragma_minus_1000000.test_boundary();
290+
END;
291+
/
292+
293+
DROP PACKAGE test_pragma_minus_1000000;
294+
295+
-- Test 11b: Invalid error code -1000001 (should fail with PLS-00701 - beyond boundary)
296+
CREATE OR REPLACE PACKAGE test_pragma_minus_1000001 IS
297+
exc_beyond EXCEPTION;
298+
PRAGMA EXCEPTION_INIT(exc_beyond, -1000001); -- Beyond boundary
299+
END test_pragma_minus_1000001;
300+
/
301+
302+
-- Test 11c: Invalid error code -1403 (should fail with PLS-00701)
303+
CREATE OR REPLACE PACKAGE test_pragma_minus_1403 IS
304+
exc_1403 EXCEPTION;
305+
PRAGMA EXCEPTION_INIT(exc_1403, -1403); -- Oracle-specific NO_DATA_FOUND, must use 100
306+
END test_pragma_minus_1403;
307+
/
308+
309+
-- Test 11d: Invalid error code 0 (should fail with PLS-00701)
310+
CREATE OR REPLACE PACKAGE test_pragma_zero IS
311+
exc_zero EXCEPTION;
312+
PRAGMA EXCEPTION_INIT(exc_zero, 0); -- Zero is rejected by Oracle
313+
END test_pragma_zero;
314+
/
315+
316+
SELECT 'PRAGMA EXCEPTION_INIT negative error code tests completed' AS result;
317+
219318
-- Cleanup
220319
DROP PACKAGE test_pragma_init;
221320
DROP PACKAGE test_pragma_raise;

0 commit comments

Comments
 (0)