CA ERwin Model Manager › Connect to the CA ERwin MM Mart › Custom Security Message at Connection › Example: Oracle Stored Procedure

Example: Oracle Stored Procedure

The following is an example of a stored procedure that can be used on an Oracle DBMS to display a custom message at connection:

CREATE OR REPLACE PROCEDURE m7x_Get_Privacy_Message (
   	p$string1	IN OUT varchar2,
	p$string2	IN OUT varchar2,
	p$string3	IN OUT varchar2,
	p$string4	IN OUT varchar2,
   p$gen_err_code IN OUT NUMBER
	)
AS
-- Declarations here
BEGIN
    -- Ensure the return parameter is set to Zero for success
	p$gen_err_code := 0;
	p$string1 := '';
	p$string2 := '';
	p$string3 := '';
	p$string4 := '';
    -- Add custom code here for extra validations
    -- Formatted message would go here. Ensure that the content of the message does not exceed 1000 chars
    -- Failure to do so will result in truncation */
	p$string1 := 'This stored procedure will be implemented by the customer based on ' ||
	'their current requirements. Depending on the DBMS additional validations can be ' ||
	'made by the end user to suit their privacy requirements.';
	p$string2 := Chr(13) || Chr(10) || 'Customer responsible for limiting each of the return strings to <= 250 chars, otherwise there could be unexpected errors returned by server.';
	p$string3 := ' Use native DB functions for special ASCII characters like CRFL, LF, TAB etc.,';
	p$string4 := Chr(13) || Chr(10) || 'Prior to exiting the proc make sure to limit the strings to 250 chars';
	-- Safety check to limit 250 chars
	p$string1 := SubStr(p$string1, 1, 250);
	p$string2 := SubStr(p$string2, 1, 250);
	p$string3 := SubStr(p$string3, 1, 250);
	p$string4 := SubStr(p$string4, 1, 250);
	RETURN;
END m7x_Get_Privacy_Message;
/
DROP PUBLIC SYNONYM m7x_Get_Privacy_Message
CREATE PUBLIC SYNONYM m7x_Get_Privacy_Message FOR m7x_Get_Privacy_Message
GRANT ALL ON m7x_Get_Privacy_Message TO PUBLIC
/