I’ve started keeping configuration information for Oracle’s Connection Manager in tables and then generating the cman.ora file needed for each listener. One of the quandaries I ran into was trying to enforce a rule where you could not define invited nodes for registration if you had registration checking disabled. It is technically legal to do so; but for our own internal rules it didn’t make sense to allow it.
Fortunately the 26ai comes with assertions. That is, it supports check constraints that can span multiple tables. Creating such an assertion solves the problem elegantly and efficiently.
First, a miniature mockup of my configuration tables.
CREATE TABLE listeners
(
listener_id INTEGER NOT NULL,
listener_name VARCHAR2(100 BYTE) NOT NULL,
host_name VARCHAR2(100 BYTE) NOT NULL,
port INTEGER NOT NULL,
CONSTRAINT pk_listeners PRIMARY KEY(listener_id),
CONSTRAINT uk_listeners UNIQUE(listener_name, host_name)
);
CREATE TABLE listener_parameters
(
listener_id INTEGER NOT NULL,
parameter_name VARCHAR2(100 BYTE) NOT NULL,
parameter_value VARCHAR2(4000 BYTE) NOT NULL,
CONSTRAINT pk_parameters PRIMARY KEY(listener_id, parameter_name)
);
CREATE TABLE invited_nodes
(
listener_id INTEGER NOT NULL,
node_name VARCHAR2(100 BYTE) NOT NULL,
CONSTRAINT pk_invited_nodes PRIMARY KEY(listener_id, node_name) ENABLE VALIDATE
);
Next I’ll define a 3 listeners. One with node checking explicitly on (1), one with node checking implicitly on as the default (2) so there is no parameter set for that, and one with node checking disabled (3).
INSERT INTO listeners(listener_id, listener_name, host_name, port)
VALUES (1, 'cman1', 'server_a', 1521);
INSERT INTO listeners(listener_id, listener_name, host_name, port)
VALUES (2, 'cman2', 'server_a', 1522);
INSERT INTO listeners(listener_id, listener_name, host_name, port)
VALUES (3, 'cman3', 'server_b', 1521);
INSERT INTO listener_parameters(listener_id, parameter_name, parameter_value)
VALUES (1, 'valid_node_checking_registration', 'on');
INSERT INTO listener_parameters(listener_id, parameter_name, parameter_value)
VALUES (3, 'valid_node_checking_registration', 'off');
COMMIT;
Now I’ll create an assertion that checks if a listener has entries defined in the invited_nodes table and but has the node checking disabled. Again, I’ll note, this is a legal combination by Oracle syntax, but it is a violation of my own rules. So, if you have a similar data model you might not want to create a similar assertion constraint.
CREATE ASSERTION check_registration_rules CHECK (
NOT EXISTS (
SELECT 'Invited nodes list defined with node checking disabled'
FROM invited_nodes n
WHERE EXISTS
(SELECT NULL
FROM listener_parameters p
WHERE p.listener_id = n.listener_id
AND p.parameter_name = 'valid_node_checking_registration'
AND lower(p.parameter_value) IN ('off', '0'))
)
);
And now add some nodes…
INSERT INTO invited_nodes(listener_id, node_name)
VALUES (1, 'registering_node_1');
INSERT INTO invited_nodes(listener_id, node_name)
VALUES (1, 'registering_node_2');
INSERT INTO invited_nodes(listener_id, node_name)
VALUES (2, 'registering_node_3');
SQL> SELECT * FROM invited_nodes;
LISTENER_ID NODE_NAME
______________ _____________________
1 registering_node_1
1 registering_node_2
2 registering_node_3
Those, as expected, worked. Both listeners allow registration checking and thus they are allowed to define registration nodes. Next though, I’ll try adding a node to the third listener which has checking disabled.
SQL> INSERT INTO invited_nodes(listener_id, node_name)
2* VALUES (3, 'registering_node_4');
Error starting at line : 1 in command -
INSERT INTO invited_nodes(listener_id, node_name)
VALUES (3, 'registering_node_4')
Error report -
ORA-08601: SQL assertion (CMAN.CHECK_REGISTRATION_RULES) violated.
If I re-enable checking by deleting that parameter, I will be able to insert the row.
SQL> DELETE listener_parameters
2 WHERE listener_id = 3
3* AND parameter_name = 'valid_node_checking_registration';
1 row deleted.
SQL> INSERT INTO invited_nodes(listener_id, node_name)
2* VALUES (3, 'registering_node_4');
1 row inserted.
SQL> SELECT * FROM invited_nodes;
LISTENER_ID NODE_NAME
______________ _____________________
1 registering_node_1
1 registering_node_2
2 registering_node_3
3 registering_node_4
But now that the node has been created, the assertion prevents disabling the checking. So, the assertion works in both directions, both the parameter and the invited nodes tables are constrained by the check.
SQL> INSERT INTO listener_parameters(listener_id, parameter_name, parameter_value)
2* VALUES (3, 'valid_node_checking_registration', 'off');
Error starting at line : 1 in command -
INSERT INTO listener_parameters(listener_id, parameter_name, parameter_value)
VALUES (3, 'valid_node_checking_registration', 'off')
Error report -
ORA-08601: SQL assertion (CMAN.CHECK_REGISTRATION_RULES) violated.
The assertion also works if I insert an enabled parameter, and then try to update it to disabled, which will be a violation of the rule. The insert works, but the update then fails.
SQL> INSERT INTO listener_parameters(listener_id, parameter_name, parameter_value) 2* VALUES (3, 'valid_node_checking_registration', 'on'); 1 row inserted. SQL> UPDATE listener_parameters 2 SET parameter_value = 'off' 3 WHERE listener_id = 3 4* AND parameter_name = 'valid_node_checking_registration'; Error starting at line : 1 in command - UPDATE listener_parameters SET parameter_value = 'off' WHERE listener_id = 3 AND parameter_name = 'valid_node_checking_registration' Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-08601: SQL assertion (CMAN.CHECK_REGISTRATION_RULES) violated.
Assertions have been requested for a long time and it’s exciting to see them implemented in 26ai. I’ll be reviewing many old data models looking for other places where inter-table rules are either not being enforced or are enforced by complex sets of code and/or jobs that might be simplified with an assertion.
I hope you found this example helpful. Questions and comments, as always, are welcome.