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
/
| Copyright © 2011 CA. All rights reserved. | Email CA Technologies about this topic |