Skip to content

Commit f9b4c69

Browse files
rophyrophy
committed
feat: add user-defined EXCEPTION support for PL/iSQL
Implement Oracle-compatible user-defined exception variables in PL/iSQL. Exceptions can be declared in packages and procedures, raised with RAISE, and caught with WHEN handlers. Example: CREATE PACKAGE BODY pkg IS custom_error EXCEPTION; PROCEDURE test IS BEGIN RAISE custom_error; EXCEPTION WHEN custom_error THEN RAISE INFO 'Caught exception'; END; END; Implementation stores exceptions as datums with unique dno, registered in namespace for lookup. Default SQLCODE is 1 for user-defined exceptions.
1 parent 0a43e33 commit f9b4c69

12 files changed

Lines changed: 663 additions & 6 deletions

‎src/pl/plisql/src/Makefile‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ OBJS = \
4747
pl_scanner.o \
4848
pl_subproc_function.o \
4949
pl_package.o \
50+
pl_exception_type.o \
5051
$(top_builddir)/src/backend/oracle_parser/ora_scan.o
5152

5253
DATA = plisql.control plisql--1.0.sql
@@ -56,7 +57,8 @@ ORACLE_REGRESS_OPTS = --dbname=$(PL_TESTDB)
5657
REGRESS = plisql_array plisql_call plisql_control plisql_copy plisql_domain \
5758
plisql_record plisql_cache plisql_simple plisql_transaction \
5859
plisql_trap plisql_trigger plisql_varprops plisql_nested_subproc \
59-
plisql_nested_subproc2 plisql_out_parameter plisql_type_rowtype
60+
plisql_nested_subproc2 plisql_out_parameter plisql_type_rowtype \
61+
plisql_exception
6062

6163
# where to find ora_gen_keywordlist.pl and subsidiary files
6264
TOOLSDIR = $(top_srcdir)/src/tools

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

Lines changed: 114 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,114 @@
1+
--
2+
-- Test user-defined EXCEPTION type declarations
3+
--
4+
-- Note: This test verifies EXCEPTION type declarations compile correctly.
5+
-- RAISE and WHEN handler support for user-defined exceptions is not yet
6+
-- implemented and is commented out below.
7+
--
8+
-- Test 1: Basic EXCEPTION declaration in package body
9+
CREATE OR REPLACE PACKAGE test_exc_pkg1 IS
10+
PROCEDURE test_proc;
11+
END test_exc_pkg1;
12+
/
13+
CREATE OR REPLACE PACKAGE BODY test_exc_pkg1 IS
14+
bad_interval EXCEPTION; -- User-defined exception
15+
PROCEDURE test_proc IS
16+
BEGIN
17+
RAISE INFO 'Package with EXCEPTION compiled successfully';
18+
END test_proc;
19+
END test_exc_pkg1;
20+
/
21+
-- Verify the package body was created
22+
SELECT 'Package test_exc_pkg1 created' AS result;
23+
result
24+
-------------------------------
25+
Package test_exc_pkg1 created
26+
(1 row)
27+
28+
-- Test 2: Multiple EXCEPTION declarations
29+
CREATE OR REPLACE PACKAGE test_exc_pkg2 IS
30+
PROCEDURE multi_exc_test;
31+
END test_exc_pkg2;
32+
/
33+
CREATE OR REPLACE PACKAGE BODY test_exc_pkg2 IS
34+
exc_invalid_data EXCEPTION;
35+
exc_timeout EXCEPTION;
36+
exc_not_found EXCEPTION;
37+
PROCEDURE multi_exc_test IS
38+
BEGIN
39+
RAISE INFO 'Multiple exceptions declared successfully';
40+
END multi_exc_test;
41+
END test_exc_pkg2;
42+
/
43+
SELECT 'Package test_exc_pkg2 with multiple exceptions created' AS result;
44+
result
45+
--------------------------------------------------------
46+
Package test_exc_pkg2 with multiple exceptions created
47+
(1 row)
48+
49+
-- Test 3: EXCEPTION in standalone procedure (should work in declaration block)
50+
CREATE OR REPLACE PROCEDURE test_standalone_exc IS
51+
my_exception EXCEPTION;
52+
BEGIN
53+
RAISE INFO 'Standalone procedure with EXCEPTION compiled';
54+
END;
55+
/
56+
SELECT 'Standalone procedure with EXCEPTION created' AS result;
57+
result
58+
---------------------------------------------
59+
Standalone procedure with EXCEPTION created
60+
(1 row)
61+
62+
-- Test 4: EXCEPTION mixed with other variable types
63+
CREATE OR REPLACE PACKAGE test_exc_pkg3 IS
64+
PROCEDURE mixed_decl_test;
65+
END test_exc_pkg3;
66+
/
67+
CREATE OR REPLACE PACKAGE BODY test_exc_pkg3 IS
68+
v_counter NUMBER := 0;
69+
exc_custom EXCEPTION;
70+
v_name VARCHAR2(100) := 'Test';
71+
exc_another EXCEPTION;
72+
v_date DATE;
73+
PROCEDURE mixed_decl_test IS
74+
BEGIN
75+
RAISE INFO 'Mixed declarations compiled successfully';
76+
END mixed_decl_test;
77+
END test_exc_pkg3;
78+
/
79+
SELECT 'Package test_exc_pkg3 with mixed declarations created' AS result;
80+
result
81+
-------------------------------------------------------
82+
Package test_exc_pkg3 with mixed declarations created
83+
(1 row)
84+
85+
--
86+
-- Test 5: RAISE and exception handling
87+
--
88+
CREATE OR REPLACE PACKAGE test_exc_raise IS
89+
PROCEDURE test_raise_catch;
90+
END test_exc_raise;
91+
/
92+
CREATE OR REPLACE PACKAGE BODY test_exc_raise IS
93+
custom_error EXCEPTION;
94+
PROCEDURE test_raise_catch IS
95+
BEGIN
96+
RAISE custom_error; -- Raise user-defined exception
97+
EXCEPTION
98+
WHEN custom_error THEN -- Catch user-defined exception
99+
RAISE INFO 'Successfully caught custom_error';
100+
END test_raise_catch;
101+
END test_exc_raise;
102+
/
103+
-- Test execution
104+
BEGIN
105+
test_exc_raise.test_raise_catch();
106+
END;
107+
/
108+
INFO: Successfully caught custom_error
109+
DROP PACKAGE test_exc_raise;
110+
-- Cleanup
111+
DROP PACKAGE test_exc_pkg1;
112+
DROP PACKAGE test_exc_pkg2;
113+
DROP PACKAGE test_exc_pkg3;
114+
DROP PROCEDURE test_standalone_exc;

‎src/pl/plisql/src/pl_comp.c‎

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
#include "plisql.h"
3030
#include "pl_subproc_function.h"
3131
#include "pl_package.h"
32+
#include "pl_exception_type.h"
3233
#include "utils/builtins.h"
3334
#include "utils/fmgroids.h"
3435
#include "utils/guc.h"
@@ -2851,6 +2852,13 @@ build_row_from_vars(PLiSQL_variable * *vars, int numvars)
28512852
typcoll = InvalidOid; /* composite types have no collation */
28522853
break;
28532854

2855+
case PLISQL_DTYPE_EXCEPTION:
2856+
/* Exception variables have no PostgreSQL type */
2857+
typoid = VOIDOID;
2858+
typmod = -1;
2859+
typcoll = InvalidOid;
2860+
break;
2861+
28542862
default:
28552863
elog(ERROR, "unrecognized dtype: %d", var->dtype);
28562864
typoid = InvalidOid; /* keep compiler quiet */
@@ -3144,10 +3152,18 @@ plisql_parse_err_condition(char *condname)
31443152
PLiSQL_condition *new;
31453153
PLiSQL_condition *prev;
31463154

3147-
/*
3148-
* Eventually we will want to look for user-defined exception names here.
3149-
*/
3155+
/* First check for user-defined exceptions */
3156+
PLiSQL_exception_var *exc = plisql_lookup_exception(condname);
3157+
if (exc != NULL)
3158+
{
3159+
new = palloc(sizeof(PLiSQL_condition));
3160+
new->sqlerrstate = exc->sqlcode;
3161+
new->condname = condname;
3162+
new->next = NULL;
3163+
return new;
3164+
}
31503165

3166+
/* Check for OTHERS */
31513167
if (strcmp(condname, "others") == 0)
31523168
{
31533169
new = palloc(sizeof(PLiSQL_condition));

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

Lines changed: 163 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,163 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* pl_exception_type.c
4+
* Oracle-compatible EXCEPTION type implementation for PL/iSQL
5+
*
6+
* This module implements user-defined exception variables as found in
7+
* Oracle PL/SQL.
8+
*
9+
* Portions Copyright (c) 2025, IvorySQL Global Development Team
10+
*
11+
* IDENTIFICATION
12+
* src/pl/plisql/src/pl_exception_type.c
13+
*
14+
*-------------------------------------------------------------------------
15+
*/
16+
17+
#include "postgres.h"
18+
19+
#include "pl_exception_type.h"
20+
#include "plisql.h"
21+
22+
/*
23+
* plisql_build_exception
24+
* Build a user-defined exception variable
25+
*
26+
* This creates a new exception variable and optionally adds it to
27+
* the current namespace.
28+
*/
29+
PLiSQL_exception_var *
30+
plisql_build_exception(const char *refname, int lineno, bool add2namespace)
31+
{
32+
PLiSQL_exception_var *exc;
33+
34+
exc = palloc0(sizeof(PLiSQL_exception_var));
35+
exc->dtype = PLISQL_DTYPE_EXCEPTION;
36+
exc->refname = pstrdup(refname);
37+
exc->lineno = lineno;
38+
exc->sqlcode = ERRCODE_RAISE_EXCEPTION; /* Default P0001 for user-defined exceptions */
39+
exc->pkgoid = InvalidOid;
40+
41+
/* Assign datum number and add to datum array */
42+
exc->dno = plisql_nDatums;
43+
plisql_adddatum((PLiSQL_datum *) exc);
44+
45+
/* Add to namespace if requested */
46+
if (add2namespace)
47+
plisql_ns_additem(PLISQL_NSTYPE_VAR, exc->dno, refname);
48+
49+
return exc;
50+
}
51+
52+
/*
53+
* plisql_datum_is_exception
54+
* Check if a datum is an exception variable
55+
*/
56+
bool
57+
plisql_datum_is_exception(PLiSQL_datum *datum)
58+
{
59+
if (datum == NULL)
60+
return false;
61+
62+
return (datum->dtype == PLISQL_DTYPE_EXCEPTION);
63+
}
64+
65+
/*
66+
* plisql_datum_get_exception
67+
* Get exception variable from datum (with type checking)
68+
*/
69+
PLiSQL_exception_var *
70+
plisql_datum_get_exception(PLiSQL_datum *datum)
71+
{
72+
if (!plisql_datum_is_exception(datum))
73+
return NULL;
74+
75+
return (PLiSQL_exception_var *) datum;
76+
}
77+
78+
/*
79+
* plisql_lookup_exception
80+
* Lookup an exception by name in the current namespace
81+
*/
82+
PLiSQL_exception_var *
83+
plisql_lookup_exception(const char *name)
84+
{
85+
PLiSQL_nsitem *nse;
86+
87+
nse = plisql_ns_lookup(plisql_ns_top(), false, name, NULL, NULL, NULL);
88+
89+
if (nse == NULL)
90+
return NULL;
91+
92+
if (plisql_Datums[nse->itemno]->dtype != PLISQL_DTYPE_EXCEPTION)
93+
return NULL;
94+
95+
return (PLiSQL_exception_var *) plisql_Datums[nse->itemno];
96+
}
97+
98+
/*
99+
* plisql_exception_set_sqlcode
100+
* Set the SQLCODE for an exception variable
101+
*
102+
* This is used by PRAGMA EXCEPTION_INIT to associate a specific
103+
* Oracle error code with an exception.
104+
*/
105+
void
106+
plisql_exception_set_sqlcode(PLiSQL_exception_var *exc, int sqlcode)
107+
{
108+
if (exc == NULL)
109+
return;
110+
111+
exc->sqlcode = sqlcode;
112+
}
113+
114+
/*
115+
* plisql_process_pragma_exception_init
116+
* Process PRAGMA EXCEPTION_INIT directive at compile time
117+
*
118+
* This associates a user-defined exception with a specific error code.
119+
* Called during compilation when PRAGMA EXCEPTION_INIT is encountered.
120+
*
121+
* Syntax: PRAGMA EXCEPTION_INIT(exception_name, error_code);
122+
*/
123+
void
124+
plisql_process_pragma_exception_init(const char *exc_name, int sqlcode,
125+
int location, void *yyscanner)
126+
{
127+
PLiSQL_nsitem *nse;
128+
PLiSQL_exception_var *exc;
129+
130+
/* Lookup exception in current namespace */
131+
nse = plisql_ns_lookup(plisql_ns_top(), false, exc_name, NULL, NULL, NULL);
132+
133+
if (nse == NULL)
134+
ereport(ERROR,
135+
(errcode(ERRCODE_UNDEFINED_OBJECT),
136+
errmsg("exception \"%s\" does not exist", exc_name),
137+
plisql_scanner_errposition(location, yyscanner)));
138+
139+
/* Verify it's an exception variable */
140+
if (plisql_Datums[nse->itemno]->dtype != PLISQL_DTYPE_EXCEPTION)
141+
ereport(ERROR,
142+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
143+
errmsg("\"%s\" is not an exception", exc_name),
144+
plisql_scanner_errposition(location, yyscanner)));
145+
146+
/* Set the exception's sqlcode */
147+
exc = (PLiSQL_exception_var *) plisql_Datums[nse->itemno];
148+
plisql_exception_set_sqlcode(exc, sqlcode);
149+
}
150+
151+
/*
152+
* plisql_dump_exception
153+
* Dump exception info for debugging
154+
*/
155+
void
156+
plisql_dump_exception(PLiSQL_exception_var *exc)
157+
{
158+
if (exc == NULL)
159+
return;
160+
161+
printf(" EXCEPTION %s (dno %d, sqlcode %d, line %d)\n",
162+
exc->refname, exc->dno, exc->sqlcode, exc->lineno);
163+
}

0 commit comments

Comments
 (0)